Using Excel to Build a Budget

1. Using Excel to Build a Budget BTA – 2012 Accompanies NEFE page 23

2. Building a budget • Determine the specific period of time the budget will cover – weekly, monthly, annually. This will impact the income or expenses calculations. • Title your budget: In cell A1 type the label Jessica’s Monthly Budget.

3. Building a budget • In cell A3, type the label Estimated Income. • Look at Jessica’s income, she has a weekly paycheck and earns interest on savings. • In cell A4, type the label Paycheck (after taxes). • In cell B4, we need a formula that will calculate the amount of her MONTHLY net pay: =((8*25)*.70)*4

4. Building a budget • In cell A5, type the label Interest Earned • In cell B5, type the value 15 • In cell A6, type the label Total Estimated Income • We now need a formula to in order to add up the total estimated income, so in cell B6 : =B4+B5

5. Building a budget • Let’s do a little cleanup: • Resize column A to the exact width needed • Format the following cells • B4 – accounting • B5 – number • B6 – accounting • Select the range A6:B6 and place a top and bottom border on this range • Bold cells A1, A3, A6, and B6

6. Building a budget • In cell A8, enter the label Fixed Expenses • Bold cell A8 • In cell A9, click the increase indent button and enter the label Savings (PYF) • In cell A10, click the increase indent button and enter the label Car Payment • In cell B10, enter the value 235

7. Building a budget • In cell A11, click increase indent and enter the label Car Insurance • In cell B11, enter the value 50 • In cell A13, enter the label Estimated Variable Expenses • Bold cell A13 • Resize column A

8. Building a budget • In cell A14, click increase indent and enter the label Cell Phone • In cell B14, enter the value of Jessica’s estimated cell phone bill: 40 • In cell A15, click increase indent and enter the label Gas • In cell B15, enter the value of the estimated cost Jessica will spend on gas: 50

9. Building a budget • In cell A16, click the increase indent button and enter the label Miscellaneous • In cell A18, enter the label Total Expenses • In cell B18, enter a formula that will add up all of Jessica’s expenses: =B9+B10+B11+B14+B15+B16 OR… use auto sum and click and drag to get the formula: =SUM(B9:B17)

10. Building a budget • Let’s clean up again: • Select the range A18:B18, bold the range and place a top border and bottom border on this range • Format cell B9 and B18 as accounting • Format the range B10:B17 as numbers

11. Building a budget • In cell A20, enter the label Total Income – Total Expenses • In cell B20, enter the formula to calculate total income – total expenses: =B6-B18 • Select the range A20:B20, bold and add a top and bottom border • Format cell B20 as accounting

12. Building a budget • Look at cell B20, Jessica has $200 left over after paying all her bills. We need to reallocate this money so that she is saving some and she has some for miscellaneous expenses. • In cell B9 and cell B16 enter the value you feel Jessica needs for saving and for miscellaneous expenses. • Look at cell B20. The value should now be 0 or “-”.

13. Building a budget • Add cell styles and themes using colors, fonts, etc. of your choice. • RESIZE columns if necessary. • Save this workbook to your P: drive as Jessica’s budget • Print to NORWOOD 3600 and turn into your class folder