p>AH is a Maryland based company that specializes in lawn services. It has 3 branches in Baltimore, Frederick, and Annapolis. The branches have submitted figures comparing this year’s budget with next year’s budget in 4 categories as shown in Table 1 below.
Table 1 AH’s Expenditures at Different Branches
Baltimore Frederick Annapolis
Next Year This Year Next Year This Year Next Year This Year
Equipment $112,000 $108,050 $52,550 $68,543 $125,100 $120,650
Maintenance $58,330 $52,550 $20,000 $18,000 $48,300 $53,500
Miscellaneous $38,100 $34,220 $25,000 $28,200 $40,500 $30,854
Salaries and Benefits $96,000 $92,400 $88,000 $100,000 $100,000 $112,050
Use the concepts and techniques presented in Excel Chapters 4-6, especially Chapter 6, to design an Excel 2010 workbook to reflect the data above:
1. Your finished workbook should contain 4 worksheets: one for the Baltimore branch, one for the Frederick branch, one for the Annapolis branch, and one Consolidated worksheet consolidating the 3 branches’ data for AH. Each worksheet should have its appropriate sheet tab name (at the bottom of the screen above the Windows status bar) and tab color.
2. Each worksheet should include this year’s budget, next year’s budget, and the difference for each of the 4 expenditure categories. The formula for difference is as follows: Difference for an expenditure category = next year’s budget – this year’s budget. Total budget should also be calculated for this year and next year, respectively, on each worksheet. All the formulas and numbers need to be correct on each worksheet. More specifically,
a. The worksheet for each branch must include the following data: This Year and Next Year budget amounts from Table 1 for the corresponding branch; the Difference between next year budget and this year budget calculated using the formula given above; and finally the Total Budget for next year and this year calculated using the SUM function. The Baltimore worksheet should have only the data for the Baltimore branch, the Frederick worksheet should have only the Frederick data, and the Annapolis worksheet should have only the Annapolis data.
b. Similarly, the Consolidated worksheet should have the exact same number of columns and rows as the other 3 branch worksheets; i.e., the consolidated worksheet for the entire AH company should include:
This Year, Next Year, and Difference amounts for each budget category; Total Budget for next year and Total Budget for this year. All the numbers on this worksheet should be calculated using formulas: Regarding This Year and Next Year amount for each expenditure category, you are not supposed to type in the numbers manually; instead, you should use 3-D cell references (e.g., similar to what you did for the 2011 column on the Consolidated worksheet in Excel Chapter 6, page EX 392 – EX394) to link to the data on the Baltimore, Frederick, and Annapolis worksheets. The Difference and Total Budget data should be calculated in the exact same way as the other 3 branch worksheets, and they do not require the use of 3-D cell references.
On each of the 4 worksheets, add a custom format to the Difference data so that it shows negative numbers in red color. An example of custom format can be found on the top of book page EX 378.
3. Each worksheet must have its own title, subtitle, and formatting as you see appropriate and professional. Your designed layouts and formatting need to be consistent, very clear and straightforward across all the four worksheets.
