Question

Analysis and explanations can be included in the Excel cells or in a textbox. The submission should be one Excel file only. No additional files should be submitted. Use contribution margin income statement formatting. LiveColor is preparing their 2019 budget. They estimate sales/production will be between 700,000 and 900,000 boxes of markers per month. LiveColor wants to look at both static budgets and flexible budgets to determine which is best for them. They have struggled in the past with determining whether budget variances were related to volume being above or below budget vs whether they are spending too much or too little on expenses. They want to be able to understand their budget variances in order to make better decisions. Note: Treat Salary and Wages Costs as Fixed Expenses.

Question 1: Prepare some budgets in Excel for LiveColor. (20 points) a) Show the static budget based on 800,000 units (boxes) produced. b) Show what the flexible budget would be if 700,000 units (boxes) were produced. c) Show what the flexible budget would be if 900,000 units (boxes) were produced. d) Show the flexible budget cost formula(s) for LiveColor. e) Explain the difference between static and flexible budgets and when each should be used.

Question 2: The month of January 2019 is complete, and LiveColor wants to compare their budget to their actual results. Actual results are shown in the table above. (30 points) a) Compare January’s actual results to the static budget you created in Question 1. a). Analyze the static budget variances by comparing the static budget to the actual results. b) Break out price and volume variance amounts for each line item. c) For the static budget variances, indicate whether each line item is favorable or unfavorable. Provide possible explanations. d) Create the flexible budget based on the actual units produced for January. e) Compare the actual results to the flexible budget for January that you created in Question 2d. Analyze the flexible budget variances by comparing the flexible budget to the actual results. f) For the flexible budget variances, indicate whether each line item is favorable or unfavorable. Provide possible explanations. g) LiveColor wants to determine whether they should use a flexible budget or a static budget going forward. Write a memo to their CFO explaining some pros and cons of each option. Provide a recommendation including the reason(s) you recommend that approach.

Question 1 Chart Monthly Budget Selling

Price per unit-----5.00 per box Raw material cost-----1.50 per box Packaging Cost----.80 per box salary and wage cost----300,000 per month ot for product over 800,000 units---0.70 per box fringe benefits-----50% off wage and OT electricity---.20 per box waste and other cost---.10 per box rent cost---500,000 per month insurance cost----60,000 per month depreciation cost---240,000 per month

Question 2 Chart

Production---825,000 per box Sales---4,070,000 Ingredients cost---1,132,450 packaging cost---658,250 salary and wage cost---280,000 OT---48,750 Fringe benefits---164,375 electricity---158,780 waste and other cost--138,352 rent cost---500,000 insurance cost--65,000 depreciation cost---240,000

Answer 1

Per unit Standards Static Budget Cost Formula(s) Static Budget Number of units sold Sale Revenue Variable and Manufacturing Cost Material Cost Packing Cost OT for >800,000 Boxes OT electricity for > 800,000 Variable Overhead Cost Contribution Margin Fixed Cost Manufacturing (Rent) Manufacturing (General and Admin) Manufacturing Overhead (Depreciation) Salary and Wages Fringe Benefits 50% of wages Net Income Flexible Budget 9,00,000 $5 x Units Sold 7,00,000 35,00,000 8,00,000 $40,00,000 $5.00 $45,00,000 $1.50 0.80 $0.70 $0.20 $0.10 $12,00,000 $6,40,000 $0 $0 $80,000 $20,80,000 10,50,000 $5,60,000 $0 $0 $70,000 $18,20,000 $13,50,000 $1.50 X Units Sold $7,20,000 $0.80 X Units Sold 70,000 $0.70 per box for production over 800,000 units 20,000 $0.20 per box for production for over 800,000 units $90,000 $0.10 X Units Sold $22,50,000 $5,00,000 $60,000 $2,40,000 $3,00,000 $1,50,000 $5,70,000 $5,00,000 Fixed $5,00,000 $60,000 $2,40,000 $3,00,000 $1,50,000 8,30,000 $60,000 Fixed $2,40,000 Fixed $3,00,000 Fixed $1,50,000 50% on Fixed Salary and Wages $10,00,000

e) Difference between Static and Flexible Budget Static Budget Static budget is meant for only planned volume of activities. If there is no change in the planned volume of activities then static budget is considered as best. If the volume of planned activity changed is more, than the static budget and actual budget cannot be compared. It will be like comparing apples with oranges. Flexible Budget Flexible Budget is also known as master budget or planning budget. It is mainly prepared when the planned level of activites tthat are tend to fluctuate. Master budget with flexible nature helps is analysing the deviation and variance between what was planned (expected) and what is done (Actual)

Per unit Per unit Actual Variance variance Amount Analysis Standards lexible Budget Number of units sold Sale Revenue Variable and Manufacturing Cost Material Cost Packing Cost OT for>800,000 Boxes OT electricity for > 800,000 Variable Overhead Cost Contribution Margin Fixed Cost Manufacturing (Rent) Manufacturing (General and Admin) Manufacturing Overhead (Depreciation) Salary and Wages Fringe Benefits 50% of wages Net Income Volume Variance Actual Sales was 82500x4.93 $40,70,000 unfavorable sales Variance -$55000 due to lesser selling price. Cost Variance as the Actual Direct Material Cost per unit is Lesser Cost Variance as the Actual Packing Cost per unit is less Cost Variance as the Actual OT for the extra 25000 production is more Cost Variance as the Actual OT for the extra 25000 production is more Cost Variance as the Actual per unit is more 8,25,000 540,70,000 55,000 93 $12,37,500 $6,60,000 $17,500 $5,000 $82,500 $21,22,500 $1.37 $0.80 $1.95 $6.35 $0.17 $11,32,450 $6,58,250 $48,7S0 $1,58,780 $1,38,352 $19,33,418 1,05,050 1,750 31,250 1,53,780 55,852 1,89,082 $0.70 Variable Overhead cost $0.10 $5,00,000 $60,000 $2,40,000 3,00,000 $5,00,000 $65,000 $2,40,000 $2,80,000 5,000 $0.08 Cost Variance as the Actual Fixed Insurance cost is more $0.34 $0.20 20,000 14,375 $1,45,957 Cost Variance as the Actual Fixed Salary and wages is less Cost Variance as the Actual Fixed Fringe benfits is more $6,84,043

Per unit Per unit Actual Variance variance Amount Analysis Standards lexible Budget Number of units sold Sale Revenue Variable and Manufacturing Cost Material Cost Packing Cost OT for>800,000 Boxes OT electricity for > 800,000 Variable Overhead Cost Contribution Margin Fixed Cost Manufacturing (Rent) Manufacturing (General and Admin) Manufacturing Overhead (Depreciation) Salary and Wages Fringe Benefits 50% of wages Net Income Volume Variance Actual Sales was 82500x4.93 $40,70,000 unfavorable sales Variance -$55000 due to lesser selling price. Cost Variance as the Actual Direct Material Cost per unit is Lesser Cost Variance as the Actual Packing Cost per unit is less Cost Variance as the Actual OT for the extra 25000 production is more Cost Variance as the Actual OT for the extra 25000 production is more Cost Variance as the Actual per unit is more 8,25,000 540,70,000 55,000 93 $12,37,500 $6,60,000 $17,500 $5,000 $82,500 $21,22,500 $1.37 $0.80 $1.95 $6.35 $0.17 $11,32,450 $6,58,250 $48,7S0 $1,58,780 $1,38,352 $19,33,418 1,05,050 1,750 31,250 1,53,780 55,852 1,89,082 $0.70 Variable Overhead cost $0.10 $5,00,000 $60,000 $2,40,000 3,00,000 $5,00,000 $65,000 $2,40,000 $2,80,000 5,000 $0.08 Cost Variance as the Actual Fixed Insurance cost is more $0.34 $0.20 20,000 14,375 $1,45,957 Cost Variance as the Actual Fixed Salary and wages is less Cost Variance as the Actual Fixed Fringe benfits is more $6,84,043

g) Flexibile Budget is preferred because Volume of activities are considerably changed Static Budget Static budget is meant for only planned volume of activities. If there is no change in the planned volume of activities then static budget is considered as best. If the volume of planned activity changed is more, than the static budget and actual budget cannot be compared. It will be like comparing apples with oranges. Flexible Budget Flexible Budget is also known as master budget or planning budget. It is mainly prepared when the planned level of activites ttha ae tend to fluctuate. Master budget with flexible nature helps is analysing the deviation and variance between what was planned (expected) and what is done (Actual
Please find the updated solution. If you have any questions, please let me know in the comments section. Will be happy to address it and help you.