Using a Flexible Budget to Illustrate Best and Worst Case Scenarios Case Products manufactures two models of cell phone cases: regular and deluxe. Presented is standard cost information for each model, sold to retailers in packages of 6 units. During July, the company produced 8,000 regular and 3,500 deluxe packs of cell phone cases. a and b. Data Set(s) Budget Best Case Worst Case Cost Components for a Pack Direct materials Plastic sheets.. Assembly kit Direct labor.. Regular Deluxe 3 sheels x $12 $36.00 5 sheets x $12- $60.00 5.00 5.00 0.5 hour x $20 10.00 Variable overhead. 0.5 labor hr. x $5 2.50 0.75 hours x $20 0.75 labor hrs. x $5 = 15.00 3.75 Total $53.50 $83.75 Required a. Assume that budgeted selling prices for regular and deluxe packs are $60 and $90 respectively. The company expects to sell all of the products produced for the period. Management estimates that the best case scenario for next month is that regular units increase by 10% and deluxe units increase by 20%. Management estimates that the worst case scenario for next month is that regular units decrease by 10% and deluxe unit decrease by 15%. Create a schedule in Excel showing the contribution margin at budget, best, and worst case scenarios for regular and deluxe product. b. Create a stacked bar chart showing contribution margin of regular and deluxe products at budget, best case, and worst case. In what $10,000 range does the top of each bar fall under? c. How could calculating a best and worst case scenario assist management in planning? Solution Summary a. See report to the right. b. See chart to the right. 1. The top of the bar for the budget scenario falls between $70,000 and $80,000. 2. The top of the bar for the best case scenario falls between $80,000 and $90,000. 3. The top of the bar for the worst case scenario falls between $60,000 and $70,000. C. Having a best and worst case scenario helps management in planning for resources needed for the period Volume: Regular Deluxe Selling Price: Regular Deluxe Sales: Regular DM: Plastic: Regular DM: Assembly Kits: Regular DL: Regular Overhead: Regular Contribution margin: Regular Sales: Deluxe DM: Plastic: Deluxe DM: Assembly Kits: Deluxe Budget Best Case Worst Case Budget Best Case Worst Case DL: Deluxe Overhead: Deluxe Contribution margin: Deluxe Summary Budget Best Case Worst Case Contribution margin: Regular Contribution margin: Deluxe