Please fill in the excel cells that are greyed out using the information below.

Information needed for above.

Table 2: Operating Income Budgets and Variances 1 +Favorable, -Unfavorable Actual Results Flex Variance Flex Budget Sales Volume Variance Master Budget 2 3 4 5 Volume (customers) Revenue - Variable Cost - Contribution Margin Fixed Cost Operating Income 6 7 Master Variance 8 Level 1 Variance Flex Variance Sales Volume Variance 9 Level 2 Variances Table 3: Sales Budgets and Variances 6 Actual Results "Sales Mix Flex" "Market Share Flex" Budget Budget Corporate Sales "Market Size Flex" Budget Master Budget 3 4 5 6 7 8 9 Market Size x Market Share - Total Quantity x Segment Mix -Segment Units x Selling Price -Segment Revenue Consumer Sales 10 11 12 13 14 Market Size x Market Share - Total Quantity x Segment Mix -Segment Units x Selling Price -Segment Revenue 15 16 17 18 19 Total Sales Total Revenue Master Variance 20 Level 1 Variance Selling Price Variance Sales Volume Variance 21 Level 2 Variances Selling Price Variance Sales Mix Variance Sales Quantity Variance 22 Level 3 Variances Selling Price Variance Sales Mix Variance Market Share Variance Market Size Variance 23 Level 4 Variances Table 4: Direct Material Budgets and Variances 1 2 5 6 1 Actual Results 3 4 "Input Mix Flex Input Quantity Flex Budget Budget Presus Premium (PP) Tires Flex Budget Master Budget 2 3 4 5 6 Output Quantity x Usage Rate = Total Inputs x PP Input Mix = PP Input Units x PP Purchase Price = PP Input Cost 7 8 9 10 Diamond Discount (DD") Tires 11 12 13 14 Output Quantity x Usage Rate = Total Inputs x DD Input Mix = DD Input Units x DD Purchase Price I = DD Input Cost 15 16 17 18 Total Direct Materials 19 Total Input Cost Master Variance 20 Level 1 Variance Flex Variance Sales Volume Variance 21 Level 2 Variances Price Variance Efficiency Variance Sales Volume Variance 22 Level 3 Variances Price Variance Mix Variance Yield Variance Sales Volume Variance 23 23 Level 4 Variances Table 6: Variable Overhead Budgets and Variances 2 4 5 1 Actual Results (Variable Overhead Control) "Input Flex Budget Flex Budget (Variable Overhead Allocated) Master Budget 2 3 4 Output Quantity x Base Usage Rate - Allocation Base x Allocation Rate = Total Cost 5 6 Master Variance 7 Level 1 Variance Variable Overhead Variance Sales Volume Variance 8 Level 2 Variance Spending Variance Efficiency Variance Sales Volume Variance 9 Level 3 Variance Table 7: Fixed Overhead Budgets and Variances 2 4 1 Actual Results (Fixed Overhead Control) Master Budget Fixed Overhea d Allocate d 2 3 4 Output Quantity x Base Usage Rate = Allocation Base x Allocation Rate = Total Cost 5 6 Fixed Overhead Variance 7 Level 1 Variance Spending Variance Production Volume 8 Level 2 Variance Table 8: Summary of Operating Income Variances 2 3 4 1 OI Variances: +Favorable, -Unfavorable High-Level Middle-Level Low-Level 2 3 4 5 6 7 Sales Volume Variance Method 1: OI Volume Variance Production Volume Variance Method 2: Market Share Variance (Revenue) Market Size Variance (Revenue) DM Sales Volume Variance DL Sales Volume Variance V-OH Sales Volume Variance 8 9 10 11 12 13 14 15 Rev Flex Variance Selling Price Variance (Revenue) Sales Mix Variance (Revenue) 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 VC Flex Variance DM Flex Variance DM Price Variance DM Mix Variance DM Yield Variance DL Flex Variance DL Price Variance DL Mix Variance DL Yield Variance V-OH Variance V-OH Spending Variance V-OH Efficiency Variance FC Flex Variance TOTAL: Master Variance Table 1: Weekly Operating and Financial Results for the Month 2 Week 1 1,400 Panel 1 3 4 Week 2 Week 3 1,450 1,600 5 Week 4 1,510 6 Total 5,960 7 Budget 5,100 1 2 3 4 5 Sales Revenue Market Units Sales Units Corporate Customers Consumer Customers Total Sales Units Revenue Corporate Customers Consumer Customers Total Revenue 73 123 196 90 142 232 76 1401 216 79 118 196 317 523 840 485 485 969 6 Joala 7 8 9 10 $39,161 $67,297 $ 106,457 $47,954 $74,298 $122,252 $43,168 $75,816 $118,984 $44,364 $64,779 $109,143 $174,647 $282,190 $456,836 $242,250 $263,084 $505,334 Panel 2 4 2 Week 1 3 Week 2 5 Week 4 6 Total Week 3 7 Budget 1 2 3 4. 614 183 797 569 399 968) Direct Material Tire Kit Purchases (kits) Premium Discount Total Tire Kits Tire Kit Purchases Premium Discount Total Tire Kits 243 7091 952 237 629 866) 1,662 1,920 3,101 775 5 3,583 3,876 6 7 8 9 $33,167 $5,302 $38,469 $33,074 $11,571 $44,645 $15,672 $20,566 $36,238 $15,615 $18,251 $33,866 $97,529 $55,690 $153,219 $173,645 $22,481 $196,126 2 Week 1 3 Week 2 Panel 3 4 Week 3 5 Week 4 6 Total 7 Budget 1 2 3 208 756 4 5 1 Direct Labor Installer Hours Certified Installers Training Apprentices Total Installer Hours Installer Wages Certified Installers Training Apprentices Total Installer Wages 136 75 211 220 54 274 240) 192 57 249 54 263 591 378 969 996 6 7 8 9 $2,771 $1,150 $3,921 $4,619 $823 $5,441 $4,819 $931 $5,750 $4,221 $992 $5,213 $16,430 $3,896 $20,325 $11,822 $6,047 $17,868 7 Budget 1 2 3 4 1 Variable Overhead Utilities Supplies Support Wages Other Variable Overhead Total Variable Overhead 2 Week 1 $885 $2,847 $4,411 $2,055 $10,197 Panel 4 3 4 Week 2 Week 3 $1,116 $1,015 $3,125 $2,718 $4,418 $3,536 $2,089 $1,696 $10,749 $8,965 5 Week 4 $847 $2,743 $2,528 $1,350 $7,468 6 Total $3,862 $11,433 $14,894 $7,191 $37,380 $3,923 $11,768 $12,508 $4,521 $32,719 5 6 1 2 3 4 5 6 7 1 Fixed Overhead Rent Depreciation Support Salaries Performance Bonuses Other Fixed Overhead Total Fixed Overhead Week 1 $5,100 $7,200 $9,800 $2,400 $3,000 $27,500 Panel 5 3 4 Week 2 Week 3 $5,100 $5,100 $7,200 $7,200 $9,600 $7,600 $2,400 $2,400 $3,000 $3,000 $27,300 $25,300 5 Week 4 $5,100 $7,200 $7,100 $100 $1,000 $20,500 6 Total $20,400 $28,800 $34,100 $7,300 $10,000 $100,600 7 Budget $20,000 $28,000 $40,000 $13,900 $14,100 $116,000 7 Budget 1 2 3 4 5 Operating Income Volume (units) Revenue Variable Cost = Contribution Margin Fixed Cost = Operating Income 2 Week 1 196 $106,457 $52,588 $53,870 $27,500 $26,370 Panel 6 3 4 Week 2 Week 3 232 216 $122,252 $118,984 $60,836 $50,954 $61,417 $68,030 $27,300 $25,300 $34,117 $42,730 5 Week 4 196 $109,143 $46,547 $62,596 $20,500 $42,096 6 Total 840 $456,836 $210,924 $245,912 $100,600 $145,312 969 $505,334 $246,713 $258,621 $116,000 $142,621 6 7 5 2 Week 1 7 Budget Week 4 1 1 2 3 4 5 1 Operating Income per Unit Volume (units) Revenue Variable Cost = Contribution Margin Fixed Cost = Operating Income $543.15 $268.31 $274.84 $140.31 $134.54 Panel 7 3 4 Week 2 Week 3 1 $526.95 $550.85 $262.22 $235.90 $264.73 $314.95 $117.67 $117.13 $147.06 $197.82 $556.00 $237.12 $318.88 $104.43 $214.45 6 Total 1 $543.66 $251.01 $292.65 $119.72 $172.93 $521.50 $254.61 $266.89 $119.71 $147.18 6 7 Table 2: Operating Income Budgets and Variances 1 +Favorable, -Unfavorable Actual Results Flex Variance Flex Budget Sales Volume Variance Master Budget 2 3 4 5 Volume (customers) Revenue - Variable Cost - Contribution Margin Fixed Cost Operating Income 6 7 Master Variance 8 Level 1 Variance Flex Variance Sales Volume Variance 9 Level 2 Variances Table 3: Sales Budgets and Variances 6 Actual Results "Sales Mix Flex" "Market Share Flex" Budget Budget Corporate Sales "Market Size Flex" Budget Master Budget 3 4 5 6 7 8 9 Market Size x Market Share - Total Quantity x Segment Mix -Segment Units x Selling Price -Segment Revenue Consumer Sales 10 11 12 13 14 Market Size x Market Share - Total Quantity x Segment Mix -Segment Units x Selling Price -Segment Revenue 15 16 17 18 19 Total Sales Total Revenue Master Variance 20 Level 1 Variance Selling Price Variance Sales Volume Variance 21 Level 2 Variances Selling Price Variance Sales Mix Variance Sales Quantity Variance 22 Level 3 Variances Selling Price Variance Sales Mix Variance Market Share Variance Market Size Variance 23 Level 4 Variances Table 4: Direct Material Budgets and Variances 1 2 5 6 1 Actual Results 3 4 "Input Mix Flex Input Quantity Flex Budget Budget Presus Premium (PP) Tires Flex Budget Master Budget 2 3 4 5 6 Output Quantity x Usage Rate = Total Inputs x PP Input Mix = PP Input Units x PP Purchase Price = PP Input Cost 7 8 9 10 Diamond Discount (DD") Tires 11 12 13 14 Output Quantity x Usage Rate = Total Inputs x DD Input Mix = DD Input Units x DD Purchase Price I = DD Input Cost 15 16 17 18 Total Direct Materials 19 Total Input Cost Master Variance 20 Level 1 Variance Flex Variance Sales Volume Variance 21 Level 2 Variances Price Variance Efficiency Variance Sales Volume Variance 22 Level 3 Variances Price Variance Mix Variance Yield Variance Sales Volume Variance 23 23 Level 4 Variances Table 6: Variable Overhead Budgets and Variances 2 4 5 1 Actual Results (Variable Overhead Control) "Input Flex Budget Flex Budget (Variable Overhead Allocated) Master Budget 2 3 4 Output Quantity x Base Usage Rate - Allocation Base x Allocation Rate = Total Cost 5 6 Master Variance 7 Level 1 Variance Variable Overhead Variance Sales Volume Variance 8 Level 2 Variance Spending Variance Efficiency Variance Sales Volume Variance 9 Level 3 Variance Table 7: Fixed Overhead Budgets and Variances 2 4 1 Actual Results (Fixed Overhead Control) Master Budget Fixed Overhea d Allocate d 2 3 4 Output Quantity x Base Usage Rate = Allocation Base x Allocation Rate = Total Cost 5 6 Fixed Overhead Variance 7 Level 1 Variance Spending Variance Production Volume 8 Level 2 Variance Table 8: Summary of Operating Income Variances 2 3 4 1 OI Variances: +Favorable, -Unfavorable High-Level Middle-Level Low-Level 2 3 4 5 6 7 Sales Volume Variance Method 1: OI Volume Variance Production Volume Variance Method 2: Market Share Variance (Revenue) Market Size Variance (Revenue) DM Sales Volume Variance DL Sales Volume Variance V-OH Sales Volume Variance 8 9 10 11 12 13 14 15 Rev Flex Variance Selling Price Variance (Revenue) Sales Mix Variance (Revenue) 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 VC Flex Variance DM Flex Variance DM Price Variance DM Mix Variance DM Yield Variance DL Flex Variance DL Price Variance DL Mix Variance DL Yield Variance V-OH Variance V-OH Spending Variance V-OH Efficiency Variance FC Flex Variance TOTAL: Master Variance Table 1: Weekly Operating and Financial Results for the Month 2 Week 1 1,400 Panel 1 3 4 Week 2 Week 3 1,450 1,600 5 Week 4 1,510 6 Total 5,960 7 Budget 5,100 1 2 3 4 5 Sales Revenue Market Units Sales Units Corporate Customers Consumer Customers Total Sales Units Revenue Corporate Customers Consumer Customers Total Revenue 73 123 196 90 142 232 76 1401 216 79 118 196 317 523 840 485 485 969 6 Joala 7 8 9 10 $39,161 $67,297 $ 106,457 $47,954 $74,298 $122,252 $43,168 $75,816 $118,984 $44,364 $64,779 $109,143 $174,647 $282,190 $456,836 $242,250 $263,084 $505,334 Panel 2 4 2 Week 1 3 Week 2 5 Week 4 6 Total Week 3 7 Budget 1 2 3 4. 614 183 797 569 399 968) Direct Material Tire Kit Purchases (kits) Premium Discount Total Tire Kits Tire Kit Purchases Premium Discount Total Tire Kits 243 7091 952 237 629 866) 1,662 1,920 3,101 775 5 3,583 3,876 6 7 8 9 $33,167 $5,302 $38,469 $33,074 $11,571 $44,645 $15,672 $20,566 $36,238 $15,615 $18,251 $33,866 $97,529 $55,690 $153,219 $173,645 $22,481 $196,126 2 Week 1 3 Week 2 Panel 3 4 Week 3 5 Week 4 6 Total 7 Budget 1 2 3 208 756 4 5 1 Direct Labor Installer Hours Certified Installers Training Apprentices Total Installer Hours Installer Wages Certified Installers Training Apprentices Total Installer Wages 136 75 211 220 54 274 240) 192 57 249 54 263 591 378 969 996 6 7 8 9 $2,771 $1,150 $3,921 $4,619 $823 $5,441 $4,819 $931 $5,750 $4,221 $992 $5,213 $16,430 $3,896 $20,325 $11,822 $6,047 $17,868 7 Budget 1 2 3 4 1 Variable Overhead Utilities Supplies Support Wages Other Variable Overhead Total Variable Overhead 2 Week 1 $885 $2,847 $4,411 $2,055 $10,197 Panel 4 3 4 Week 2 Week 3 $1,116 $1,015 $3,125 $2,718 $4,418 $3,536 $2,089 $1,696 $10,749 $8,965 5 Week 4 $847 $2,743 $2,528 $1,350 $7,468 6 Total $3,862 $11,433 $14,894 $7,191 $37,380 $3,923 $11,768 $12,508 $4,521 $32,719 5 6 1 2 3 4 5 6 7 1 Fixed Overhead Rent Depreciation Support Salaries Performance Bonuses Other Fixed Overhead Total Fixed Overhead Week 1 $5,100 $7,200 $9,800 $2,400 $3,000 $27,500 Panel 5 3 4 Week 2 Week 3 $5,100 $5,100 $7,200 $7,200 $9,600 $7,600 $2,400 $2,400 $3,000 $3,000 $27,300 $25,300 5 Week 4 $5,100 $7,200 $7,100 $100 $1,000 $20,500 6 Total $20,400 $28,800 $34,100 $7,300 $10,000 $100,600 7 Budget $20,000 $28,000 $40,000 $13,900 $14,100 $116,000 7 Budget 1 2 3 4 5 Operating Income Volume (units) Revenue Variable Cost = Contribution Margin Fixed Cost = Operating Income 2 Week 1 196 $106,457 $52,588 $53,870 $27,500 $26,370 Panel 6 3 4 Week 2 Week 3 232 216 $122,252 $118,984 $60,836 $50,954 $61,417 $68,030 $27,300 $25,300 $34,117 $42,730 5 Week 4 196 $109,143 $46,547 $62,596 $20,500 $42,096 6 Total 840 $456,836 $210,924 $245,912 $100,600 $145,312 969 $505,334 $246,713 $258,621 $116,000 $142,621 6 7 5 2 Week 1 7 Budget Week 4 1 1 2 3 4 5 1 Operating Income per Unit Volume (units) Revenue Variable Cost = Contribution Margin Fixed Cost = Operating Income $543.15 $268.31 $274.84 $140.31 $134.54 Panel 7 3 4 Week 2 Week 3 1 $526.95 $550.85 $262.22 $235.90 $264.73 $314.95 $117.67 $117.13 $147.06 $197.82 $556.00 $237.12 $318.88 $104.43 $214.45 6 Total 1 $543.66 $251.01 $292.65 $119.72 $172.93 $521.50 $254.61 $266.89 $119.71 $147.18 6 7