BTA30 Exercise 12 Last year you began keeping track of your spending in an Excel spreadsheet. You now want to chart your progress to make it easier to see what's happening. Data table: 1. Pick up Household Expenses from the Pickup Folder. Rename it YOURLASTNAME Ex 12 Household Expenses. 2. Make the calculations. 1. Totals for each month (Row 19) 2. Average of all the expense items (B21) (do not use totals or % Total) 3. Maximum expense (B22) 4. Total Expenses (B23) (add all the totals from Row 19) 5. % Total for each expense item (for this, you will need to add all the expenses for each item across their row then divide by the Total Expenses in B23) 3. Format all the totals and the average and maximum values as currency. 4. Sort the table by % Total in Descending order. 5. Format the spreadsheet effectively using fonts and borders and shading. 6. Landscape the table. 7. Set the left and right margins to 0.2 inches. 8. Set the table to print with all columns on one page. 9. Add the row and column headings and gridlines, and put your name in a custom header on the left. Charts: 1. Rename Sheet1 Data. 2. Create a chart to show the percent of each expense in relation to the whole. What kind of chart do you think you should create? Add data labels to the inside end. Add a title to the chart and link it to main title in Data. Save the chart in a new worksheet. Name the sheet Percent. 3. Create another chart in a new worksheet to show the change in the cost of groceries over the year. Name this worksheet Groceries. Add a title to the chart and link it to the cell that has the word Groceries in it (probably A5). 4. Delete any worksheets that are blank. 5. Put your worksheets in this order: Data, Percent, Groceries. 6. Ensure all the sheets are properly centered. Include your name in a header on every worksheet. B D E F G H K L M N 07 Jan 1 Expenses 2 Your Name 3 4 5 Rent 6 Groceries 7 Vehicle, Gas 8 Vehicle, Maintenance 9 Cable 10 Internet 11 Cell Phone 12 Entertainment 13 Toiletries 14 Insurance 15 Dog Food 16 Clothing 17 Vet Costs 18 Savings 19 Total 20 21 Average 22 Maximum 23 Total Expenses 24 25 Feb 750 410 150 120 60 50 60 120 50 240 May Jun 750 750 420 400 180 180 90 80 45 45 Mar 750 380 50 0 6S 50 60 150 50 240 40 100 0 150 Apr 750 395 150 0 50 50 60 100 60 240 50 110 180 150 Aug 750 480 170 120 50 50 60 160 55 280 40 50 60 100 40 280 40 110 0 Jul 750 390 160 400 SO 50 60 100 45 280 50 400 0 50 60 110 50 280 50 200 120 150 Sep 750 320 150 100 50 50 60 120 60 280 40 100 40 150 Oct 800 410 180 80 SS 50 60 80 45 280 50 600 30 150 Nov Dec 800 800 420 420 180 170 0 40 45 45 50 50 60 60 80 90 40 50 280 280 50 40 100 100 0 0 150 % Total 800 500 160 800 65 50 60 120 50 280 40 400 0 150 40 100 100 0 150 0 150 150 150 150 Sheet1 Sheet2 Sheet3 BTA30 Exercise 12 Last year you began keeping track of your spending in an Excel spreadsheet. You now want to chart your progress to make it easier to see what's happening. Data table: 1. Pick up Household Expenses from the Pickup Folder. Rename it YOURLASTNAME Ex 12 Household Expenses. 2. Make the calculations. 1. Totals for each month (Row 19) 2. Average of all the expense items (B21) (do not use totals or % Total) 3. Maximum expense (B22) 4. Total Expenses (B23) (add all the totals from Row 19) 5. % Total for each expense item (for this, you will need to add all the expenses for each item across their row then divide by the Total Expenses in B23) 3. Format all the totals and the average and maximum values as currency. 4. Sort the table by % Total in Descending order. 5. Format the spreadsheet effectively using fonts and borders and shading. 6. Landscape the table. 7. Set the left and right margins to 0.2 inches. 8. Set the table to print with all columns on one page. 9. Add the row and column headings and gridlines, and put your name in a custom header on the left. Charts: 1. Rename Sheet1 Data. 2. Create a chart to show the percent of each expense in relation to the whole. What kind of chart do you think you should create? Add data labels to the inside end. Add a title to the chart and link it to main title in Data. Save the chart in a new worksheet. Name the sheet Percent. 3. Create another chart in a new worksheet to show the change in the cost of groceries over the year. Name this worksheet Groceries. Add a title to the chart and link it to the cell that has the word Groceries in it (probably A5). 4. Delete any worksheets that are blank. 5. Put your worksheets in this order: Data, Percent, Groceries. 6. Ensure all the sheets are properly centered. Include your name in a header on every worksheet. B D E F G H K L M N 07 Jan 1 Expenses 2 Your Name 3 4 5 Rent 6 Groceries 7 Vehicle, Gas 8 Vehicle, Maintenance 9 Cable 10 Internet 11 Cell Phone 12 Entertainment 13 Toiletries 14 Insurance 15 Dog Food 16 Clothing 17 Vet Costs 18 Savings 19 Total 20 21 Average 22 Maximum 23 Total Expenses 24 25 Feb 750 410 150 120 60 50 60 120 50 240 May Jun 750 750 420 400 180 180 90 80 45 45 Mar 750 380 50 0 6S 50 60 150 50 240 40 100 0 150 Apr 750 395 150 0 50 50 60 100 60 240 50 110 180 150 Aug 750 480 170 120 50 50 60 160 55 280 40 50 60 100 40 280 40 110 0 Jul 750 390 160 400 SO 50 60 100 45 280 50 400 0 50 60 110 50 280 50 200 120 150 Sep 750 320 150 100 50 50 60 120 60 280 40 100 40 150 Oct 800 410 180 80 SS 50 60 80 45 280 50 600 30 150 Nov Dec 800 800 420 420 180 170 0 40 45 45 50 50 60 60 80 90 40 50 280 280 50 40 100 100 0 0 150 % Total 800 500 160 800 65 50 60 120 50 280 40 400 0 150 40 100 100 0 150 0 150 150 150 150 Sheet1 Sheet2 Sheet3