Hernandez Family Juan and Olivia Hernandez are a recently married couple in Fort Wayne, Indiana. Juan is currently in graduate school and Olivia is the manager at a local bakery. They want to use Excel to help manage their family budget, but they need help setting up the formulas and functions to project their monthly expenses and help them meet their financial goals. Complete the following:
1. Open the Hernandez workbook located in the Excel3 Case1 folder included with your Data Files, and then save the workbook as Hernandez Budget in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. Go to the Budget worksheet. In cell B7, calculate the couple’s total monthly income. 4. In row 23, use AutoFill to replace the numbers 1 through 12 with the month abbreviations Jan through Dec. 5. In rows 24 and 25, enter the couple’s monthly income by referencing the monthly income estimates in cells B5 and B6. Use an absolute cell reference. 6. In row 26, calculate the couple’s monthly income. 7. In row 37, enter formulas to calculate the total estimated expenses for each month. 8. In row 38, calculate each month’s net cash flow, which is equal to the total income minus the total expenses. 9. In row 39, calculate the running total of the net cash flow so that Olivia and Juan can see how their net cash flow changes as the year progresses. 10. In the range B10:B19, calculate the average monthly expenses by category based on the values previously entered in rows 27 through 36. 11. In cell B20, calculate the total average monthly expenses. 12. The couple currently has $7,350 in their savings account. Each month the couple will either take money out of their savings account or deposit money. In row 41, calculate the end-of-month balance in their savings account by adding the value in cell E5 to the running total values of the net cash flow in row 39. Use an absolute cell reference for cell E5. 13. In cell E6, enter a formula to display the value of the savings balance at the end of December. 14. Juan and Olivia would like to have $15,000 in their savings account by the end of the year. Olivia is planning to ask for a raise at her job. Use Goal Seek to determine the value of cell B6 that will achieve a final savings balance of $15,000. 15. Save and close the workbook.
