| Assignment 3 Financial Analysis Graphs Excel Template |
| | | Month 1 Budget | | | | | Month 2 Budget | | | | | Month 3 Budget | | | | | Financial Goal Savings Progress |
| | | Dollars | Percent | | | | Dollars | Percent | | | | Dollars | Percent | | | | Savings | Overall Savings | Amount Remaining to Save |
| Income | | | - | | Income | | $ - 0 | - | | Income | | $ - 0 | - | | | Month 1 | | 0 |
| Expenditures | | | | | Expenditures | | | | | Expenditures | | | | | | Month 2 | | 0 |
| | Housing | | | | | Housing | | | | | Housing | | | | | Month 3 | | 0 |
| | Food | | | | | Food | | | | | Food |
| | Transportation | | | | | Transportation | | | | | Transportation |
| | Education | | | | | Education | | | | | Education |
| | Utilities | | | | | Utilities | | | | | Utilities |
| | Taxes | | | | | Taxes | | | | | Taxes |
| | Health Care | | | | | Health Care | $ 400 | | | | Health Care |
| | Family Care | | | | | Family Care | | | | | Family Care |
| | Miscellaneous | | | | | Miscellaneous | | | | | Miscellaneous | $ 100 |
| | Savings | | | | | Savings | | | | | Savings |
| | Total | | | | | Total | | | | | Total |
| Assignment 3 Excel Instructions:
In this assignment, you will make three monthly budgets. Your income increases each month using embedded formulas, as shown in the tables above. Additionally, in Months 2 and 3, some cells have been filled in with a formula to represent an unexpected expense in that expenditure category for the month. You will need to reallocate your budget around these expenses.
1. Fill in the Month 1 Budget based on your annual budget from Assignment 2. Remember that Assignment 2 was looking at your annual budget. So, to get the number for your monthly budget, you will need to divide by 12.
2. Notice that your income for Month 2 and Month 3 have been auto-calculated. Use these income numbers to plan your budgets in these months. Also, as noted in the instructions, notice that your “Health Care” costs for Month 2 and your “Miscellaneous” costs for Month 3 have auto-calculated. Do not change these numbers. You will need to plan around them.
3. For Month 2 and Month 3, fill in the cells for each category for how you are choosing to allocate your income in each of those months.
4. Use formulas to calculate the sum for your total in the “Dollars” columns, and fill in the “Percent” columns for each monthly budget.
5. Now produce a graphic for each of these three budgets to show the spending allocation. You could use a pie chart, bar chart, or other graphic from Excel. You will end up with three graphics, one for each month. Each graphic should show how you have allocated your income among the various categories.
6. Complete the Financial Goal Savings Progress table by entering in the “Savings” amount from each of your three monthly budgets. Use a formula to calculate how much you have left to save using the dollar amount of your chosen savings goal from Assignment 2.
7. Create a graphic that shows your progress toward your savings goal based on the information you input into the Financial Goal Savings Progress table. Select the type of graphic that you think would best illustrate your progress.
8. Put the graphics in the space below on this spreadsheet.
|
| In this assignment, you will make three monthly budgets. Your income increases each month using embedded formulas, as shown in the tables above. Additionally, in Months 2 and 3, some cells have been filled in with a formula to represent an unexpected expense in that expenditure category for the month. You will need to reallocate your budget around these expenses. |
| 1. Fill in the Month 1 Budget based on your annual budget from Assignment 2. Remember that Assignment 2 was looking at your annual budget. So, to get the number for your monthly budget, you will need to divide by 12. |
| 2. Notice that your income for Month 2 and Month 3 have been auto-calculated. Use these income numbers to plan your budgets in these months. Also, as noted in the instructions, notice that your “Health Care” costs for Month 2 and your “Miscellaneous” costs for Month 3 have auto-calculated. Do not change these numbers. You will need to plan around them. |
| 3. For Month 2 and Month 3, fill in the cells for each category for how you are choosing to allocate your income in each of those months. |
| 4. Use formulas to calculate the sum for your total in the “Dollars” columns, and fill in the “Percent” columns for each monthly budget. |
| 5. Now produce a graphic for each of these three budgets to show the spending allocation. You could use a pie chart, bar chart, or other graphic from Excel. You will end up with three graphics, one for each month. Each graphic should show how you have allocated your income among the various categories. |
| 6. Complete the Financial Goal Savings Progress table by entering in the “Savings” amount from each of your three monthly budgets. Use a formula to calculate how much you have left to save using the dollar amount of your chosen savings goal from Assignment 2. |
| 7. Create a graphic that shows your progress toward your savings goal based on the information you input into the Financial Goal Savings Progress table. Select the type of graphic that you think would best illustrate your progress. |
| 8. Put the graphics in the space below on this spreadsheet. |
shekeykey33
excelwk10assignment.xlsx
