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

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.