Download Creating a Personal Budget Excel Spreadsheet: Instructions and Formatting and more Exercises Financial Accounting in PDF only on Docsity!
Creating a Budget Exercise
Follow the directions below to create a budget using Excel. Read through each individual direction before performing it, like you are following recipe instructions.
Remember that text is entered (typed in) or pasted wherever the black box is positioned. Always make sure the black box is on the cell you want text to be entered. To move the location of the black box , move the mouse pointer and click. When using Excel, to move between different cells, you can use the arrow keys (up, down, left, right) or you can use the Tab key to move across and the Enter key to move down.
Remember, too, that there is usually more than one way to do something. If a different way to do it occurs to you, go ahead and try it! If it doesn’t work, you can always click on the Undo button on the Quick Access Toolbar. If you can’t remember what a command does, move the mouse pointer over it and pause. A ToolTip appears describing the button’s function.
In the instructions, the following terms will be used:
Click - a single left mouse click. Command - a button displayed on a tab on the Ribbon. Key – on the keyboard. Drag Cursor – a black line with black arrows on both sides Select Cursor – a black arrow pointing down or across
Part One: Enter Information
- Type Personal Budget 2013 in A1. Press ‘Enter’ twice.
- Type Income in A3 , press tab once
- Type Jan in B3. Use the copy cursor to drag across and fill in the months all the way to Dec. Click in the Dec cell and press tab once. Type the word Year.
- Type Salary in A4. Press Enter once
- Type Income Totals in A5. Press Enter twice
- Type Expenses in A7. Press Enter Once
- Type Mortgage/Rent in A8. Press Enter Once
- Type Utilities in A9. Press Enter Once
- Type Cell Phone in A10. Press Enter Once
10.Type Groceries in A11. Press Enter Once
11.Type Gas in A12. Press Enter Once
12.Type Entertainment in A13. Press Enter Once
13.Type Savings in A14. Press Enter Once
14.Type Expense Totals in A15. Press Enter Once
15.Type Overall Total in A16.
16.Click in cell B4 and type 3000. Use the copy cursor to drag across and copy that number all the way through Dec
17.Click in cell B8 and type 600. Use the copy cursor to drag across and copy that number all the way through Dec
18.Click in cell B9 and type 200. Use the copy cursor to drag across and copy that number all the way through Dec
19.Click in cell B10 and type 60. Use the copy cursor to drag across and copy that number all the way through Dec
20.Click in cell B11 and type 150. Use the copy cursor to drag across and copy that number all the way through Dec
21.Click in cell B12 and type 100. Use the copy cursor to drag across and copy that number all the way through Dec
22.Click in cell B13 and type 60. Use the copy cursor to drag across and copy that number all the way through Dec
Part Three: Inserting Formulas
There are two ways to insert automatic calculation formulas. You can use the Autosum button in the Editing Group or you can type it in yourself. Let’s try both.
- Click in cell B5 and drag across to highlight that entire row. In the Editing group, click on the arrow next to the Autosum command and choose Sum.
- Now click in cell N4. Type =sum(B4:M4) and press Enter. Your totals are calculated for income.
- Click in cell B15 and drag across to highlight that entire row. In the Editing group, click on the arrow next to the Autosum command and choose Sum.
- Click on cell B8 and drag across to highlight that entire row all the way through Year. Click the Autosum command.
- Click on cell B9 and drag across to highlight that entire row all the way through Year. Click the Autosum command.
- Click on cell B10 and drag across to highlight that entire row all the way through Year. Click the Autosum command.
- Click on cell B11 and drag across to highlight that entire row all the way through Year. Click the Autosum command.
- Click on cell B12 and drag across to highlight that entire row all the way through Year. Click the Autosum command.
- Click on cell B13 and drag across to highlight that entire row all the way through Year. Click the Autosum command. 10.Click on cell B14 and drag across to highlight that entire row all the way through Year. Click the Autosum command. 11.You now have the totals for your expenses. 12.To calculate the difference between your total income and total expenses, click on cell N16 and type =N5-N15 then press Enter
13.To add lines to your budget, click and drag over the whole budget to highlight it, then click the arrow next to the Borders command in the Font group and choose All Borders
Part Four: Printing the List
Sometimes Excel sheets can be tricky when printing in terms of fitting everything on one page. If you have more than 5 columns (which we do in this exercise) it’s likely that when printing, the last 1 or more will be pushed onto a second page. There are two ways of adjusting this.
- To see how your page is going to print click on the page layout view button in the status bar at the bottom of your worksheet. This will split the page and show that part of your budget is going to be on a second page.
- Click on the Page Layout tab at the top of the page and click on Orientation in the Page Setup group. Change it to Landscape.
- Now adjust the width by changing it in the Scale to Fit group. Right now Width and Height are set to automatic. Click on Width and change it to 1 page.
- Note: When you are in the page layout view you can title your worksheet by typing in a name such as Address List or Expense Report, etc. where it says “Click to Add Header”.
- Change the view back to Normal by clicking on the normal view button in the status bar at the bottom of your worksheet.
- Now you can input numbers and the budget will automatically calculate the correct sums. Try this by changing some of the numbers in the expenses group.
Part Five: Saving the List
- Click on the File Tab and click on Save As from the menu.
- When the Save As dialog box opens, check under Computer in the Folders pane to verify that your table is saving to your thumb drive.
