Panel 1 1 2 3 4 5 6 7 1 Sales Revenue Week 1 Week 2 Week 3 Week 4 Total Budget 2 Market Units 1,400 1,400 1,400 1,400 5,600 5,000 3 Sales Units 4 Corporate Customers 74 95 73 79 320 500 5 Consumer Customers 137 143 109 91 480 500 6 Total Sales Units 210 238 182 170 800 1,000 7 Revenue 8 Corporate Customers $40,425 $50,106 $38,220 $39,270 $168,021 $250,000 9 Consumer Customers $75,075 $78,540 $56,474 $45,730 $255,819 $275,000 10 Total Revenue $115,500 $128,646 $94,694 $85,000 $423,840 $525,000 Panel 2 1 2 3 4 5 6 7 1 Direct Material Week 1 Week 2 Week 3 Week 4 Total Budget 2 Tire Kit Purchases (kits) 3 Premium 675 602 219 202 1,698 3,200 4 Discount 176 388 586 551 1,702 800 5 Total Tire Kits 851 990 805 753 3,400 4,000 6 Tire Kit Purchases 7 Premium $37,126 $35,720 $14,260 $13,098 $100,204 $176,000 8 Discount $5,292 $11,640 $17,581 $16,539 $51,052 $24,000 9 Total Tire Kits $42,418 $47,360 $31,841 $29,637 $151,256 $200,000 Panel 3 1 2 3 4 5 6 7 1 Direct Labor Week 1 Week 2 Week 3 Week 4 Total Budget 2 Installer Hours 3 Certified Installers 134 209 181 166 690 750 4 Training Apprentices 79 55 49 46 230 250 5 Total Installer Hours 214 264 230 212 920 1,000 6 Installer Wages 7 Certified Installers $2,755 $4,451 $4,198 $3,783 $15,186 $14,250 8 Training Apprentices $1,191 $837 $835 $811 $3,674 $3,750 9 Total Installer Wages $3,946 $5,287 $5,033 $4,594 $18,860 $18,000 Panel 4 1 2 3 4 5 6 7 1 Variable Overhead Week 1 Week 2 Week 3 Week 4 Total Budget 2 Utilities $961 $1,138 $854 $776 $3,730 $4,000 3 Supplies $3,189 $3,413 $2,477 $2,643 $11,721 $12,000 4 Support Wages $4,464 $4,010 $3,136 $2,295 $13,905 $12,180 5 Other Variable Overhead $2,078 $1,985 $1,461 $1,209 $6,734 $4,004 6 Total Variable Overhead $10,693 $10,546 $7,928 $6,922 $36,089 $32,604 Panel 5 1 2 3 4 5 6 7 1 Fixed Overhead Week 1 Week 2 Week 3 Week 4 Total Budget 2 Rent $5,000 $5,00 $5,000 $5,000 $20,000 $20,000 3 Depreciation $7,000 $7,000 $7,000 $7,000 $28,000 $28,000 4 Support Salaries $10,000 $9,500 $7,500 $7,500 $34,500 $40,000 5 Performance Bonuses $2,500 $2,500 $2,500 $0 $7,500 $13,000 6 Other Fixed Overhead $3,000 $3,000 $3,000 $1,000 $10,000 $14,000 7 Total Fixed Overhead $27,500 $27,000 $25,000 $20,500 $100,000 $115,000 Panel 6 1 2 3 4 5 6 7 1 Operating Income Week 1 Week 2 Week 3 Week 4 Total Budget 2 Volume (units) 210 238 182 170 800 1,000 3 Revenue $115,500 $128,646 $94,694 $85,000 $423,840 $525,000 4 - Variable Cost $57,057 $63,193 $44,802 $41,153 $206,205 $250,604 5 = Contribution Margin $58,443 $65,453 $49,892 $43,847 $217,635 $274,396 6 - Fixed Cost $27,500 $27,000 $25,000 $20,500 $100,000 $115,000 7 = Operating Income $30,943 $38,453 $24,892 $23,347 $117,635 $159,396 Panel 7 1 2 3 4 5 6 7 1 Operating Income per Unit Week 1 Week 2 Week 3 Week 4 Total Budget 2 Volume (units) 1 1 1 1 1 1 3 Revenue $550.00 $540.53 $520.29 $500.00 $529.80 $525.00 4 - Variable Cost $271.70 $265.51 $246.16 $242.08 $257.76 $250.00 5 = Contribution Margin $278.30 $275.01 $274.13 $257.92 $272.04 $275.00 6 - Fixed Cost $130.95 $113.45 $137.36 $120.59 $125.00 $115.00 7 = Operating Income $147.35 $161.57 $136.77 $137.33 $147.04 $160.00 1. Determine the high level variances for Speedy Tire Co a. Using the information in Table 1 panel 6, create a high level flexible budget. b. Using Table 1 panel 6 and the flexible budget created in the above step, calculate the total master budget variance, flexible variance, and sales activity variance c. To check your answers, populate the appropriate yellow fields in the "High Level" column on the "Variance summary" tab with your variance result and see message in column I Note: Enter your variances as follows: +Favorable, -Unfavorable 2. Determine the mid level variances for Speedy Tire Co (The details are needed to calculate the lower level variances) a. Calculate the flexible budget amount and flexible variance for each cost component: direct material, direct labor, and variable overhead (must show calculations) b. To check your answers, compare your results to the appropriate cell in the "Middle-level" column 3. Determine the low level variances for all cost components for Speedy Tire Co a. Calculate the price and quantity variances for direct material for both product types for the month (calculate both products separately and add the results for the correct total variance) b. Calculate the price and efficiency variance for direct labor for both labor types for the month (calculate both labor types seperately and add the results for the correct total variance) c. Calculate the variable overhead spending and efficiency variance using the "Total variable overhead" amount for the month d. Calcualte the fixed overhead spending and volume variances using the "Total fixed overhead" amount for the month e. To check your answers, populate the appropriate yellow fields in the "Low Level" column on the "Variance summary" tab with your variance results and see message in column I