AutoSave of H b v elvo Lab 13 Budget Start.xls(1) - Protected View Search File Home Insert Draw Page Layout Formulas Data Review View Help (i) PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Pro A1 : X V fx Gas E F G H I D 14.25 20.21 16.29 50.32 18 14.6 41.78 15 23.9 10.25 666.98 5.62 35.19 A 1 Gas 2 Merchandise 3 Supermarkets 4 Services 5 Restaurants 6 Gas 7 Services 8 Gas 9 Supermarkets 10 Supermarkets 11 Merchandise 12 Services 13 Supermarkets 14 Merchandise 15 Merchandise 16 Gas 17 Gas 18 Services 19 Services 20 Services 21 Supermarkets 22 Merchandise 23 Gas 24 Supermarkets 25 Supermarkets 26 Merchandise 27 Restaurants 28 Merchandise 29 Gas 30 Merchandise 31 Supermarkets 32 Supermarkets 33 Supermarkets 34 Merchandise 35 Services 36 Services 37 Merchandise 38 Gas 39 Supermarkets 40 Merchandise 41 Merchandise 42 Gas 43 Services 44 Supermarkets 45 Gas 7/2/2003 FillerUp Gas Station 7/3/2003 Bulls Eye Dept Store 7/8/2003 Neighborhood Market 7/11/2003 Ameri-Cable 7/12/2003 Joe's Pizza 7/14/2003 City Service Sta 7/15/2003 Ameri-Cable Phone 7/18/2003 Gas and Go 7/18/2003 24/7 Market 7/19/2003 Super-Mart 7/20/2003 Digital Electronics, Inc. 7/24/2003 My Phone Co 7/24/2003 Super-Mart 7/27/2003 Booksellers Inc 7/27/2003 Country Stores 7/31/2003 US Gas 8/4/2003 Gas and Go 8/9/2003 My Phone Co 8/10/2003 Ameri-Cable 8/10/2003 Ameri-Cable Phone 8/10/2003 Super-Mart 8/14/2003 Clothes Line 8/14/2003 Sunny Oil Co 8/14/2003 Neighborhood Market 8/18/2003 24/7 Market 8/27/2003 Tina's Toys 8/29/2003 Mama's Pizza 8/30/2003 Bargain Outlets 8/30/2003 Sunny Oil Co 8/31/2003 Booksellers Inc 9/1/2003 Super-Mart 9/4/2003 Neighborhood Market 9/6/2003 24/7 Market 9/7/2003 Super Electronics 9/10/2003 Ameri-Cable 9/10/2003 Ameri-Cable Phone 9/12/2003 Bargain Outlets 9/12/2003 Filler Up Gas Station 9/17/2003 Super-Mart 9/19/2003 Bed, Bath & Beyond 9/20/2003 Bulls Eye Dept Store 9/21/2003 Sunny Oil Co 9/25/2003 My Phone Co 9/25/2003 Super-Mart 9/26/2003 Gas and Go 26.46 15.7 14.5 13.86 9.25 50.32 41.93 13.75 20.99 15.15 7.97 28.17 56.39 70 50.23 16.82 8.46 38.72 14.25 20.8 55.21 50.32 40.83 18.25 17.25 9.3 18.36 45.14 16.5 11.75 36.82 9.99 46 Add a documentation sheet in which you display a title for the project, the purpose of the workbook, the author (that's you), date created, source of data, and any revision history. Budget Sheet Create a budget sheet as shown below in which you enter the values for the monthly budget in each category. These cells are shown formatted with a yellow background. Merge-and-center the title "Monthly Budget" across cells Al and Bl, and format that in a larger font. Add a formula for cell A7 to be the sum of the values above it. o ON- Monthly Budget $100.00 Gas $120.00 Merchandise $50.00 Resturant $100.00 Services $130.00 Supermarket $500.00 Total Monthly Sheets Create three sheets: one for July one for August, and one for September. Place the name of the month in a cell that is merged and centered, spanning cells Al through Fl of each sheet. Format it is using a larger font size. Copy the Monthly Budget values from the Budget Sheet to each of the monthly sheets, being sure to link the cells back to the original budget sheet, so if you change the values there, they will be updated on the other sheets. Below the budget table, create column headings for Category, Date, Description, and Amount, in columns A through D. On the July sheet, copy and link the charges from July on the Data sheet. Sort them by category and add subtotals for each category. Add an IF formula and use conditional formatting in the column to the right of each subtotal that displays the words "Under Budget" in Green or "Over Budget" in Red if the value is under or over its corresponding budget value. To the right of that column, in each subtotal row, if the subtotal is over budget, display the amount by which the total is over budget in that category. Repeat these steps for the August and September sheets. Notice, there are no restaurant expenses for September, so you should add a Restaurant entry with an expense of 0 so that you will have a subtotal for this category. This way, all of your pages will have the same categories to subtotal. Once you get the July sheet done, you should be able to copy the formulas directly to the other sheets. Since you only need to copy the formulas to the visible cells, you can collapse all of the individual entries and only show the subtotals by clicking on the plus signs in the area at the left of your worksheet. Charts Add the following charts to your monthly sheets: July August September Add a vertical bar chart to show the expenses in each category. Add a pie chart showing the expenses in each category. Add a horizontal bar chart showing the expenses in each category. Experiment with the settings for each chart, including font size, text alignment of category names, position of the legend, colors, etc. so that the labels and values display correctly. Summary Sheet Create a summary sheet containing only the subtotals for each category. Cell Al should contain the words "Worksheet prepared: Cell A2 should contain a formula for today's date. Column headings should be Category, July, August, September, Total, and Average. Row headings should be the names of each category. Display a page title that says Summary formatted in bold and in a color other than black, centered and spanning across all of the columns containing data. To copy only the subtotal values from the earlier sheets, hide all of the data so that only the subtotals appear. Highlight the range of cells you want to copy. Select Edit ... Go To ... Special ... and click Visible Cells only then click OK. Now click Edit ... Copy (or the Copy icon) and move to the Summary sheet, and click Paste (linking these values back to the original month's sheet from which they came.) AutoSum Go To Special ? X Insert Delete Format Cells Sort & Find & 2 Clear Filter Select # Find... 4ac Replace... Go To... Go To Special... Select O Comments Constants Formulas Numbers Text Logicals Errors Blanks Current region Current array Objects Row differences O Column differences O Precedents O Dependents Direct only All levels Last cell O Visible cells only Conditional formats Data validation All Same Formulas Comments Conditional Formatting Constants Data Validation Select Objects > Selection Pane... OKJ Cancel On the summary page, add formulas to display the totals and averages in each category. Format the table with the header row in a background color, and the summary row text in the same color. Display a line chart showing the amount of money spent in each category in each month. The horizontal axis should have July, August and September. The vertical axis should be appropriate dollar amounts. Each line should represent the expense amount in each category for that month. Display the legend above the graph. Give the chart an appropriate title. Viewing Formulas View the formulas for each of your pages by pressing Ctrl and at the same time on a given sheet. Verify that they are correct. Changing Values Change a value in the Budget sheet and then notice how that change effects the corresponding month and summary sheets for example, if you change the monthly budgeted amount for Gas to $75, what changes occur elsewhere in the workbook? AutoSave of H b v elvo Lab 13 Budget Start.xls(1) - Protected View Search File Home Insert Draw Page Layout Formulas Data Review View Help (i) PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Pro A1 : X V fx Gas E F G H I D 14.25 20.21 16.29 50.32 18 14.6 41.78 15 23.9 10.25 666.98 5.62 35.19 A 1 Gas 2 Merchandise 3 Supermarkets 4 Services 5 Restaurants 6 Gas 7 Services 8 Gas 9 Supermarkets 10 Supermarkets 11 Merchandise 12 Services 13 Supermarkets 14 Merchandise 15 Merchandise 16 Gas 17 Gas 18 Services 19 Services 20 Services 21 Supermarkets 22 Merchandise 23 Gas 24 Supermarkets 25 Supermarkets 26 Merchandise 27 Restaurants 28 Merchandise 29 Gas 30 Merchandise 31 Supermarkets 32 Supermarkets 33 Supermarkets 34 Merchandise 35 Services 36 Services 37 Merchandise 38 Gas 39 Supermarkets 40 Merchandise 41 Merchandise 42 Gas 43 Services 44 Supermarkets 45 Gas 7/2/2003 FillerUp Gas Station 7/3/2003 Bulls Eye Dept Store 7/8/2003 Neighborhood Market 7/11/2003 Ameri-Cable 7/12/2003 Joe's Pizza 7/14/2003 City Service Sta 7/15/2003 Ameri-Cable Phone 7/18/2003 Gas and Go 7/18/2003 24/7 Market 7/19/2003 Super-Mart 7/20/2003 Digital Electronics, Inc. 7/24/2003 My Phone Co 7/24/2003 Super-Mart 7/27/2003 Booksellers Inc 7/27/2003 Country Stores 7/31/2003 US Gas 8/4/2003 Gas and Go 8/9/2003 My Phone Co 8/10/2003 Ameri-Cable 8/10/2003 Ameri-Cable Phone 8/10/2003 Super-Mart 8/14/2003 Clothes Line 8/14/2003 Sunny Oil Co 8/14/2003 Neighborhood Market 8/18/2003 24/7 Market 8/27/2003 Tina's Toys 8/29/2003 Mama's Pizza 8/30/2003 Bargain Outlets 8/30/2003 Sunny Oil Co 8/31/2003 Booksellers Inc 9/1/2003 Super-Mart 9/4/2003 Neighborhood Market 9/6/2003 24/7 Market 9/7/2003 Super Electronics 9/10/2003 Ameri-Cable 9/10/2003 Ameri-Cable Phone 9/12/2003 Bargain Outlets 9/12/2003 Filler Up Gas Station 9/17/2003 Super-Mart 9/19/2003 Bed, Bath & Beyond 9/20/2003 Bulls Eye Dept Store 9/21/2003 Sunny Oil Co 9/25/2003 My Phone Co 9/25/2003 Super-Mart 9/26/2003 Gas and Go 26.46 15.7 14.5 13.86 9.25 50.32 41.93 13.75 20.99 15.15 7.97 28.17 56.39 70 50.23 16.82 8.46 38.72 14.25 20.8 55.21 50.32 40.83 18.25 17.25 9.3 18.36 45.14 16.5 11.75 36.82 9.99 46 Add a documentation sheet in which you display a title for the project, the purpose of the workbook, the author (that's you), date created, source of data, and any revision history. Budget Sheet Create a budget sheet as shown below in which you enter the values for the monthly budget in each category. These cells are shown formatted with a yellow background. Merge-and-center the title "Monthly Budget" across cells Al and Bl, and format that in a larger font. Add a formula for cell A7 to be the sum of the values above it. o ON- Monthly Budget $100.00 Gas $120.00 Merchandise $50.00 Resturant $100.00 Services $130.00 Supermarket $500.00 Total Monthly Sheets Create three sheets: one for July one for August, and one for September. Place the name of the month in a cell that is merged and centered, spanning cells Al through Fl of each sheet. Format it is using a larger font size. Copy the Monthly Budget values from the Budget Sheet to each of the monthly sheets, being sure to link the cells back to the original budget sheet, so if you change the values there, they will be updated on the other sheets. Below the budget table, create column headings for Category, Date, Description, and Amount, in columns A through D. On the July sheet, copy and link the charges from July on the Data sheet. Sort them by category and add subtotals for each category. Add an IF formula and use conditional formatting in the column to the right of each subtotal that displays the words "Under Budget" in Green or "Over Budget" in Red if the value is under or over its corresponding budget value. To the right of that column, in each subtotal row, if the subtotal is over budget, display the amount by which the total is over budget in that category. Repeat these steps for the August and September sheets. Notice, there are no restaurant expenses for September, so you should add a Restaurant entry with an expense of 0 so that you will have a subtotal for this category. This way, all of your pages will have the same categories to subtotal. Once you get the July sheet done, you should be able to copy the formulas directly to the other sheets. Since you only need to copy the formulas to the visible cells, you can collapse all of the individual entries and only show the subtotals by clicking on the plus signs in the area at the left of your worksheet. Charts Add the following charts to your monthly sheets: July August September Add a vertical bar chart to show the expenses in each category. Add a pie chart showing the expenses in each category. Add a horizontal bar chart showing the expenses in each category. Experiment with the settings for each chart, including font size, text alignment of category names, position of the legend, colors, etc. so that the labels and values display correctly. Summary Sheet Create a summary sheet containing only the subtotals for each category. Cell Al should contain the words "Worksheet prepared: Cell A2 should contain a formula for today's date. Column headings should be Category, July, August, September, Total, and Average. Row headings should be the names of each category. Display a page title that says Summary formatted in bold and in a color other than black, centered and spanning across all of the columns containing data. To copy only the subtotal values from the earlier sheets, hide all of the data so that only the subtotals appear. Highlight the range of cells you want to copy. Select Edit ... Go To ... Special ... and click Visible Cells only then click OK. Now click Edit ... Copy (or the Copy icon) and move to the Summary sheet, and click Paste (linking these values back to the original month's sheet from which they came.) AutoSum Go To Special ? X Insert Delete Format Cells Sort & Find & 2 Clear Filter Select # Find... 4ac Replace... Go To... Go To Special... Select O Comments Constants Formulas Numbers Text Logicals Errors Blanks Current region Current array Objects Row differences O Column differences O Precedents O Dependents Direct only All levels Last cell O Visible cells only Conditional formats Data validation All Same Formulas Comments Conditional Formatting Constants Data Validation Select Objects > Selection Pane... OKJ Cancel On the summary page, add formulas to display the totals and averages in each category. Format the table with the header row in a background color, and the summary row text in the same color. Display a line chart showing the amount of money spent in each category in each month. The horizontal axis should have July, August and September. The vertical axis should be appropriate dollar amounts. Each line should represent the expense amount in each category for that month. Display the legend above the graph. Give the chart an appropriate title. Viewing Formulas View the formulas for each of your pages by pressing Ctrl and at the same time on a given sheet. Verify that they are correct. Changing Values Change a value in the Budget sheet and then notice how that change effects the corresponding month and summary sheets for example, if you change the monthly budgeted amount for Gas to $75, what changes occur elsewhere in the workbook