How to Build a balance Sheet Using a Microsoft Excel Spreadsheet
|
|  Yüklə 172 Kb.
|
|
Microsoft Excel Tutorial (Revised – 7/22/99) How to Build A Balance Sheet Using a Microsoft Excel Spreadsheet To begin building a Balance Sheet using Microsoft Excel, click on the Excel icon on your desktop to start Excel. You should have already completed a tutorial called Creating a Spreadsheet. You can refer to that tutorial if you need a refresher on completing basic tasks in Microsoft Excel. At the back of this tutorial, there is a copy of what your completed balance sheet should look like. Feel free to use this copy to check your spreadsheet as you move through the tutorial. Before beginning this tutorial, be sure you have a formatted disk in the A drive. N   1 2 3 4 6    ow before we get started, let’s review some of the features of Microsoft Excel. 5 
Now, let’s get started building our Balance Sheet. The first step of building a Balance Sheet is to create a title page. In cell A10, type DAVIS SHOE COMPANY TUTORIAL In cell A13, type Created By: Your Name Notice that the headings do not fit in the cells. To make the cells accommodate the headings, we can widen the columns so the headings will fit. Widen Column A to 72 characters, and widen Column B to 50 characters. Widening Columns Point to the A above the A column and click the right mouse button. This will highlight the entire column, and the Active Block Menu will appear. 
Point to Column Width and click the left mouse button. Type in 72 and point to OK and click the left mouse button. Now widen Column B to a width of 50 characters. Now that the headings fit in the columns, wouldn’t it look nice if the headings were centered? Centering Text To center the title, point to cell A10 with the mouse. Hold down the left mouse button and drag the pointer down until cells A10 through A13 are highlighted. Release the left mouse button. Now, point to anywhere within the highlighted area and click the right mouse button. The Active Block Menu will appear again. This time point to Format Cells and click the left mouse button. T  he Format Cells Box will open, point to the tab titled Alignment and click the left mouse button. Notice the small box labeled Horizontal. Point to the small arrow on the right side of the small box and click the left mouse button. A menu will drop down, point to Center and click the left mouse button. Now Center is in the box. Do the same thing for Vertical. When you have finished click on OK and click the left mouse button again.
Everything that was highlighted is now centered! Shortcut for Centering Text: Notice that there are several tool bars at the top of your screen. These tool bars have buttons that represent the most commonly used tasks. If you move your mouse pointer across the different buttons on the tool bar, a box will pop up that explains what each button does. Find the button that looks like text that is centered on the page. To use this button, simply highlight the text as before and click on the center button. Remember, both methods produce the same results, so use the one you feel most comfortable with. Now that our title page is complete, save your work to the disk in the A drive. Be sure to choose a logical name for your file, and remember to save periodically while you work on your income statement. Next, we need to produce the input section of our balance sheet. The numbers in the input section will be used to build the balance sheet. This way if a number in our input section changes, the numbers in our balance sheet will change too! Save this spreadsheet as: BAL In cell B1, type INPUT SECTION. Now center the text in the cell using either method we used earlier. Now type the following text in the appropriate cells. CELL TEXT B4 INPUT SECTION: B6 Cash in Bank B7 Petty Cash B8 Marketable Securities B9 Accounts Receivable B11 Inventory B12 Office Supplies B13 Prepaid Insurance B14 Land B15 Buildings B16 Accumulated Depreciation—Buildings B17 Warehouse B18 Accumulated Depreciation—Warehouse B19 Trucks B20 Accumulated Depreciation—Trucks B21 Office Equipment B22 Accumulated Depreciation—Office Equipment B23 Accounts Payable B24 Notes Payable, Short-Term B25 Interest Payable B26 Income Taxes Payable B27 Liability for Payroll Taxes B28 Notes Payable, Long-Term B29 Davis, Capital Now, widen Column C to 23 characters wide the same way we did earlier. Next, input the following numerical values in the appropriate cells. CELL VALUE C6 7115.15 C7 400 C8 5000
C9 24100 C10 615
C11 65000 C12 1480
C13 5135 C14 10000 C15 65000 C16 9750
C17 30000 C18 5400
C19 16000 C20 9000
C21 8700 C22 3915
C23 14190 C24 25000 C25 215 C26 300
C27 1800 C28 70000 C29 97745.15
These are dollar values, so we must add dollar signs and decimal points. Formatting Numbers Point to cell C6. Hold down the left mouse button and drag the arrow down until you have highlighted cells C6 through C29. Release the left mouse button. Point anywhere in the highlighted area and click the right mouse button. The Active Block Menu will appear. Point to Format Cells and click the left mouse button. Point to the Number tab and click the left mouse button. Point to Currency and click the left mouse button. Indicate 2 decimal places for cents. Point to OK and click the left mouse button. Dollar signs and decimal points will be added to our numbers. Shortcut for Formatting Numbers: Once again we can use a button on the formatting tool bar to shorten our task. Highlight the area as indicated above. Now look for the button with a dollar sign on it. Click on the dollar sign with the left mouse button. This automatically adds dollar signs and two decimal places to the numbers. Finally, we are ready to begin actually building the balance sheet. In order for the account titles and the numerical values to fit, we need to widen the columns before we get started. Widen the following columns as you did before to the widths indicated below. COLUMN WIDTH D 44
E 15 Now, we can insert a three line heading for the balance sheet. Go to the following cells and type in the appropriate text.
D1 DAVIS SHOE COMPANY D2 BALANCE SHEET
D4 ASSETS D15 Total Current Assets D17 Noncurrent Assets D27 Total Noncurrent Assets D28 Total Assets Now, we will begin copying the account titles and numerical values from the input section into the actual balance sheet. Copying Text Point to cell B6 and click the left mouse button. Point to Edit on the top menu bar and click the left mouse button. Point to Copy and click the left mouse button. Notice that there is a dotted line moving around cell B6. Now, point to cell D7 and click the left mouse button. Now point to edit again and click the left mouse button. Point to Paste and click the left mouse button. The title in B6 is now copied to D7. Notice that because we copied the title to the new cell, the title remained in the old cell as well. If we had selected cut instead, the title would have been removed from the old cell and placed in the new cell. Shortcut for Copying Text: Like before, there is a button on the Formatting Tool Bar that will make copying text much quicker. Look for a button that looks like a pair of scissors. This is the Cut button. Next to it on the right is a button that looks like two small sheets of paper. This is the Copy button. To the right of that button is a button that looks like a clipboard and a small sheet of paper. This is the Paste button. Instead of going to the menu bar and clicking on Edit, Copy, Edit, Paste each time; you can select the cell you want to copy, then click the Copy button, then move to the cell you want to copy to and click the Paste button. This really speeds up copying! Now, copy the titles in cells B7 through B13 to cells D8 through D14. Point to cell B7, hold down the left mouse button until B7 through B13 are highlighted. Click on Edit-Copy or click on the Copy button. Now move to cell D8, hold down the left mouse button and drag until D8 through D14 are highlighted. Click on Edit-Paste or click on the Paste button. Next, copy B14 through B22 to D18 through D26. To improve the appearance of our balance sheet, we need to edit a few cells. Point to cell D7 and click the left mouse button, tap the F2 key to begin editing the cell. Use the arrow keys to move the cursor in front of the words Cash in Bank. Insert three spaces in front of the words. Repeat this for cell D8 through D14. Insert six spaces in front of Total Current Assets in cell D15. Edit cells D20, D22, D24, and D26 by placing the word Less: at the front of each cell. For example, cell D20 should read Less: Accumulated Depreciation—Buildings To edit: Left mouse click on the cell, tap F2. Insert three spaces in front of the words in cells D18 through D26. Insert six spaces in front of the words in cell D27. We have all of the titles in place for the Assets part of the balance sheet, now we must copy to the titles for the Liabilities and Owner’s Equity part. Go to the following cells and type in the appropriate text: CELL TEXT D31 LIABILITIES AND OWNER’S EQUITY D33 Current Liabilities D39 Total Current Liabilities D41 Noncurrent Liabilities D43 Total Noncurrent Liabilities D44 Total Liabilities D47 Owner’s Equity D49 Total Owner’s Equity D50 Total Liabilities and Owner’s Equity Next, copy B23 through B27 to D34 through D38. Insert three spaces in front of the words in cells D34 through D38 Insert six spaces in front of Total Current Liabilities in cell D39. Copy B28 to D42 and insert three spaces in front of the words in cell D42. Insert six spaces in front of the words in cells D43 and D44. Copy B29 to D48 and insert three spaces in front of the words in cell D48. Insert six spaces in front of the words in cell D49. Next we must copy the numerical values from the input section to the actual balance sheet. However, because we want the numbers in our balance sheet to change when we change the numbers in the input section, we cannot copy them the same way we copied text. Copying Numerical Values There are two ways to copy numerical values: 1. Plus-Point Method. To use this method, simply type a plus sign (+) in the new cell, then point to the cell where the number currently is and click the left mouse button. You will see the current cell address appear in the new cell. When you press enter, the numerical value will appear in the new cell. Notice that if you move back onto the new cell, the original cell address is still there. 2. Type-In Method To use this method, simply type a plus sign (+) in the new cell, then type in the original cell address. Once again, when you press enter, the numerical value will appear in the new cell, but when you move back onto the new cell, the original cell address remains. In the left column below, the destination cell addresses are listed. These are the cells where the specified task will be performed. In the right column below, the task is listed. For each row, this is the task we must perform on the destination cell. For tasks we have not yet discussed, instructions on performing the task will follow the task description. For example: In the first row, F7 is listed as the destination cell and the task is to Copy C6. This means that you must copy C6 to F7. Using the Plus-Point Method, you would go to cell F7, then type a plus sign and point to cell C6. Using the Type-In Method, you would go to cell F7, then type a plus sign and type ‘C6’. Destination Cell Task F7 Copy C6 F8 Copy C7 F9 Copy C8 E10 Copy C9 E11 Copy C10 Underline E11: Point to E11 and click the right mouse button. Select Format Cells using a left mouse click, then select the Border tab using a left mouse click. Point to the bottom of the cell within the box and left mouse click. A line should appear along the bottom of that cell. Click on OK with a left mouse click. There should be a line along the bottom of cell E11. F11 Using the Plus-Point Method, subtract E11 from E10. Type a +, point to E10, type a -, point to E11. Press enter. F12 Copy C11 F13 Copy C12 F14 Copy C13 Underline F14 F15 Using AutoSum, add F7 through F14. To use AutoSum: Point to F7, click and hold down the left mouse button and drag down until F7 through F15 are highlighted. Point to the (Sigma) sign in the Standard Tool Bar and click the left mouse button. The total of F7 through F14 will be placed in F15. F18 Copy C14 E19 Copy C15 E20 Copy C16 Underline E20 F20 Using the Plus-Point Method, subtract E20 from E19. E21 Copy C17 E22 Copy C18 Underline E22 F22 Using the Plus-Point Method, subtract E22 from E21. E23 Copy C19 E24 Copy C20 Underline E24 F24 Using the Plus-Point Method, subtract E24 from E23. E25 Copy C21 E26 Copy C22 Underline E26 F26 Using the Plus-Point Method, subtract E26 from E25. Underline F26 F27 Using AutoSum, add F18 through F26. Underline F27 F28 Using the Plus-Point Method, add F15 and F27. Double Underline F28. To Double Underline: Do this the same way you underline, except before clicking on the bottom of the cell to place the line there, click on the double underline from the choices to the right of the cell within the box. F34 Copy C23 F35 Copy C24 F36 Copy C25 F37 Copy C26 F38 Copy C27 Underline F38 F39 Using AutoSum, add F34 through F38. F42 Copy C28 Underline F42 F43 Copy F42 Underline F43 F44 Using the Plus-Point Method, add F39 and F43. Underline F44. F48 Copy C29 Underline F48 F49 Copy F48 Underline F49. F50 Using the Plus-Point Method, add F44 and F49. Double Underline F50 We have successfully transformed our input section into a balance sheet in proper format. Now, all we need to do is print our spreadsheet. Printing a SpreadsheetThe first thing we need to do is look at a preview of our spreadsheet to see if there are any adjustments that must be made to it. We will view and print the title page, the input section, and the balance sheet separately. To print the title page, highlight A1 through A40. Point to File and left mouse click. Find Print Area and point to it, notice that a small menu appears out to the side. Find Set Print Area and left mouse click. This sets the print area to only cells A1 through A40. This way we can just print the title page. Now, let’s preview what our printout would look like. To do this, point to File and left mouse click. Find Print Preview on the menu and left click on it. A print image of our document will appear. Notice that things are a bit off center. To center the title page, while you are in Print Preview, left mouse click on Setup, then click on the Margins tab. 
In this box, you can set the margins for the spreadsheet. However, you can also choose to center the text horizontally and/or vertically on the page. Notice these options are near the bottom of the box under the heading Center on Page. Point to the small box beside Horizontally and left mouse click. Notice that a check will appear in the box. Now, do the same thing for the box beside Vertically. Left mouse click on OK. This takes care of centering our title page. In the Print Preview box, left mouse click on Print to print the title page. When you are finished printing, left mouse click on Close to exit Print Preview. Now, we must print the input section. This time highlight B1 through C40 and set this as our print area. Repeat the above process for the input section. However, notice that you do not have to center the text on the page again. The center alignment we set for our title page will carry over each time we print. When you have printed the input section, redefine the print area to D1 through F50 and print the balance sheet. You should have a title page, an input section, and a balance sheet like the ones that follow this tutorial. Don’t forget to save your work to your disk. When you have completed the tutorial and saved your spreadsheet, exit Excel by clicking on File and selecting Exit. CONGRATULATIONS!!! You have successfully used Microsoft Excel to build a balance sheet. If you have any questions or comments please send them to: murray.t@lynchburg.edu |