In the file, the shaded boxes under the 2018 Budget column must be filled in per the instructions to the right of the worksheet. Please read the instructions carefully. You will need a couple formulas from the reading to complete the assignment. The document provides the historical actual amounts from 2015 and 2016. It also provides the 2017 Budget and the 2017 Actual (assume this is a 12 month projection or annualized amount). You are also given the 2017 variances.
o View + 100% Zoom fxv Formula EE Table l Chart T Text Shape Media Comment Format Sort & Filter Assignment HTM Budget Document 2015 Actual 2016 Actual Budget 2017 Actual variance $ Budget 2018 Variance % Instructions Compensation Expenses 6.5% 1) For Technicans with a 2017 base salary budget variance of less than +/- 10%, assume the 2018 Budget will be their 2017 actual plus 4% to account for merit increases. Base Salaries Technician A Technician B Technician C Technician D Technician E Technician F Technician G Technician H Technician Technician J (NEW) Total Base Salaries $ $ $ $ $ $ $ $ $ 52,123 43,949 70,248 50,232 53,939 39,245 63,039 60,393 51,020 $ $ $ $ $ $ S $ $ 54,208 45,707 73,058 52,241 56,097 40,815 65,561 62,809 53,061 $ $ $ $ $ $ $ $ $ 56,376 $ 47,535 $ 75,980 $ 54,331 $ 58,340 $ 42,447 $ 68,183 $ 65,321 $ 55,183 $ 52,929 45,003 65,030 54,939 46,373 42,929 67,292 62,929 55,200 $ $ $ $ $ $ $ $ $ 3,447 2,532 10,950 (608) 11,967 (482) 891 2,392 (17) 5.6% 16.8% -1.1% 25.8% -1.1% 2) For Technicians with a 2017 base salary budget variance of greater than +/- 10% assume their 2018 Budget will be equal to the 2017 Budget amount plus 4% to account for merit increase 3) You have approval to add an additional technician in 2018. Their base rate will be $27/ hr. Estimate the annual amount sing the formular in the reading. Enter that 2018 budget amount for the new Technician J. 3.8% -0.0% $ 484,188 $ 503,556 $ 523,698 $ 492,624 $ 31,074 6.3% Overtime Benefits $ $ $ 40,393 20,264 145,256 $ $ S 40,284 20,528 151,067 $ $ $ 26,185 21,600 157,109 $ $ $ 39,299 20,292 172,282 $ $ $ (13,114) 1,308 (15,173) -33.4% 6.4% -8.8% 4) For overtime, choose the more appropriate vlaue: $25,000 or $40,000. 5) For PTO, calculate the average of 2016 Actual and 2017 Actual to use for 2018. 6) Calculate benefits using the 2018 Total Base Saleries and the formula in the reading Total Compensation $ 690,101 $ 715,435 $ 728,592 $ 724,497 $ 4,095 0.6% $ Department Expenses Tools and Test Equipment Mileage Expenses Office Supplies Training and Education Communication Expense Supplies $ $ $ $ S $ 15,494 119 5,033 50,292 2,828 5,030 $ $ $ $ S $ 12,929 592 4,018 80,292 4,040 5,819 $ $ $ $ $ $ 20,292 719 2,019 50,292 1,919 5,202 $ $ $ $ $ S 20,191 $ 1,262 S 4,039 S 65,003 $ 4,202 S 5,303 S 101 (543) (2,020) (14,711) (2,283) (101) 0.5% -43.0% -50.0% -22.6% -54.3% -1.9% 7) Your organization announced that your 2018 Department Expenses Budget cannot excced your current 2017 Actual Department Expense. Create a logical 2018 budget for these expenses whos sum is equal to the 2017 Actual. Total Departement Expenses $ 78,796 $ 107,690 $ 80,443 $ 100,000 $ (19,557) -19.6% Service Expenses Vendor Contracts Vendor Labor Repair Parts $ 5,494,292 $ 1,404,939 $ 4,920,292 $ 4,992,929 $ 1,499,393 $ 7,430,530 $ 4,802,929 $ 1,949,392 $ 7,952,020 $ 4,829,299 $ 1,829,299 $ 5,339,393 $ (26,370) $ 120,093 $ 2,612,627 -0.5% 6.6% 48.9% 8) You plan to reduce contract by 10% from the 2017 Actual. 9) After reducing contracts you anticipate a 2% increase in Vendor Labor for 2017 Actual. 10) Due to variantion an unpredictablity you calculate 2018 Parts budge by taking the average of years 2015, 2016 and 2017 Actuals. Then you add 5% more as a result of contract reductions and less parts being covered. Total Service Expenses $11,819,523 $13,922,852 $14,704,341 $11,997,991 $ 2,706,350 22.6% Total HTM Budget $12,588,420 $14,745,977 $15,513,376 $12,822,488 $ 2,690,888 21.0% o View + 100% Zoom fxv Formula EE Table l Chart T Text Shape Media Comment Format Sort & Filter Assignment HTM Budget Document 2015 Actual 2016 Actual Budget 2017 Actual variance $ Budget 2018 Variance % Instructions Compensation Expenses 6.5% 1) For Technicans with a 2017 base salary budget variance of less than +/- 10%, assume the 2018 Budget will be their 2017 actual plus 4% to account for merit increases. Base Salaries Technician A Technician B Technician C Technician D Technician E Technician F Technician G Technician H Technician Technician J (NEW) Total Base Salaries $ $ $ $ $ $ $ $ $ 52,123 43,949 70,248 50,232 53,939 39,245 63,039 60,393 51,020 $ $ $ $ $ $ S $ $ 54,208 45,707 73,058 52,241 56,097 40,815 65,561 62,809 53,061 $ $ $ $ $ $ $ $ $ 56,376 $ 47,535 $ 75,980 $ 54,331 $ 58,340 $ 42,447 $ 68,183 $ 65,321 $ 55,183 $ 52,929 45,003 65,030 54,939 46,373 42,929 67,292 62,929 55,200 $ $ $ $ $ $ $ $ $ 3,447 2,532 10,950 (608) 11,967 (482) 891 2,392 (17) 5.6% 16.8% -1.1% 25.8% -1.1% 2) For Technicians with a 2017 base salary budget variance of greater than +/- 10% assume their 2018 Budget will be equal to the 2017 Budget amount plus 4% to account for merit increase 3) You have approval to add an additional technician in 2018. Their base rate will be $27/ hr. Estimate the annual amount sing the formular in the reading. Enter that 2018 budget amount for the new Technician J. 3.8% -0.0% $ 484,188 $ 503,556 $ 523,698 $ 492,624 $ 31,074 6.3% Overtime Benefits $ $ $ 40,393 20,264 145,256 $ $ S 40,284 20,528 151,067 $ $ $ 26,185 21,600 157,109 $ $ $ 39,299 20,292 172,282 $ $ $ (13,114) 1,308 (15,173) -33.4% 6.4% -8.8% 4) For overtime, choose the more appropriate vlaue: $25,000 or $40,000. 5) For PTO, calculate the average of 2016 Actual and 2017 Actual to use for 2018. 6) Calculate benefits using the 2018 Total Base Saleries and the formula in the reading Total Compensation $ 690,101 $ 715,435 $ 728,592 $ 724,497 $ 4,095 0.6% $ Department Expenses Tools and Test Equipment Mileage Expenses Office Supplies Training and Education Communication Expense Supplies $ $ $ $ S $ 15,494 119 5,033 50,292 2,828 5,030 $ $ $ $ S $ 12,929 592 4,018 80,292 4,040 5,819 $ $ $ $ $ $ 20,292 719 2,019 50,292 1,919 5,202 $ $ $ $ $ S 20,191 $ 1,262 S 4,039 S 65,003 $ 4,202 S 5,303 S 101 (543) (2,020) (14,711) (2,283) (101) 0.5% -43.0% -50.0% -22.6% -54.3% -1.9% 7) Your organization announced that your 2018 Department Expenses Budget cannot excced your current 2017 Actual Department Expense. Create a logical 2018 budget for these expenses whos sum is equal to the 2017 Actual. Total Departement Expenses $ 78,796 $ 107,690 $ 80,443 $ 100,000 $ (19,557) -19.6% Service Expenses Vendor Contracts Vendor Labor Repair Parts $ 5,494,292 $ 1,404,939 $ 4,920,292 $ 4,992,929 $ 1,499,393 $ 7,430,530 $ 4,802,929 $ 1,949,392 $ 7,952,020 $ 4,829,299 $ 1,829,299 $ 5,339,393 $ (26,370) $ 120,093 $ 2,612,627 -0.5% 6.6% 48.9% 8) You plan to reduce contract by 10% from the 2017 Actual. 9) After reducing contracts you anticipate a 2% increase in Vendor Labor for 2017 Actual. 10) Due to variantion an unpredictablity you calculate 2018 Parts budge by taking the average of years 2015, 2016 and 2017 Actuals. Then you add 5% more as a result of contract reductions and less parts being covered. Total Service Expenses $11,819,523 $13,922,852 $14,704,341 $11,997,991 $ 2,706,350 22.6% Total HTM Budget $12,588,420 $14,745,977 $15,513,376 $12,822,488 $ 2,690,888 21.0%
