MBA 6100 Case Study #2 Static and Flexible Budgets and Budget Variances Create an Excel file to show the budgets and calculations for the questions below. Assume no beginning or ending inventory. Ignore taxes. Analysis or explanations can be included in the Excel cells or in a textbox. Use contribution margin income statement formatting. Students are expected to work independently. Note: The 2nd question for this case study is on page 2, so keep scrolling down! HammerTime is preparing their 2019 budget. They want to look at static budgets and flexible budgets to determine which is best for them. They estimate sales/production will be between 2,000,000 and 4,000,000 boxes of nails per month. They want to be able to understand their budget variances as well. Monthly Budget Data: Selling price per unit: $5.00 per box Raw Materials Costs:$1.50 per box Packaging osts: $0.80 per box Salary and Wages Cost:$300,000 per month OT for production over 3,000,000 units: $0.70 per box Fringe benefits: 50% of wages and OT Electricity: $0.20 per box Waste and other costs: $0.10 per box Rent Costs: $500,000 per month Insurance Costs: $60,000 per month Depreciation Costs: $240,000 per month Question 1: Prepare some budgets in Excel for HammerTime. Show the static budget based on 3,000,000 units (boxes) produced. Show the flexible budget based on 2,000,000 units (boxes) produced. Show the flexible budget based on 4,000,000 units (boxes) produced. Show the flexible budget cost formula(s) for HammerTime. Explain the difference between static and flexible budgets and when each should be used. Data for question 2: January Actual Production : 3,250,500 boxes Sales: $16,250,000 Ingredents Costs: $4,795,000 Packaging Costs: $2,600,000 Salary and Wages costs: $280,000 Overtime(OT):$195,500 Fringe benefits: $237,750 Electricity: $650,000 Waste and Other Costs: $365,000 Rent Costs: $500,000 Insurance Costs: $65,000 Depreciation Costs: $240,000 Question 2: The month of January 2019 is complete, and HammerTime wants to compare their budget to their actual results. Actual results are shown in the table above. Compare Januarys actual results to the static budget you created in Question 1. Analyze the static budget variances. Be sure to break out price and volume variances and whether they are favorable or unfavorable for each line item. Provide possible explanations. Create the flexible budget based on actual units produced for January. Compare actual results to budgeted results for the flexible budget. Analyze the flexible budget variances. Be sure to include the variance amount and whether the variance is favorable or unfavorable for each line item. Provide possible explanations. HammerTime 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.