Using a Flexible Budget to Illustrate Best and Worst Case Scenarios

The following data is included in the file linked here.

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.

Cost Components for a Pack Regular Deluxe

Direct materials

Plastic sheets $3$ sheets x $$12=$ $$36\mathrm{.}005$ sheets x $$12=$ $$60\mathrm{.}00$

Assembly kit $=5\mathrm{.}00=5\mathrm{.}00$

Direct labor $0\mathrm{.}5$ hour x $$20=10\mathrm{.}000\mathrm{.}75$ hour x $$20=15\mathrm{.}00$

Variable overhead $0\mathrm{.}5$ labor hr$.$ x $$5=2\mathrm{.}500\mathrm{.}75$ hour x $$5=3\mathrm{.}75$

Total $$53\mathrm{.}50$ $$83\mathrm{.}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 which is $8,000$ batches. Management estimates that the best case scenario is that regular units increase by $10\%$ and deluxe units increase by $20\%.$ Management estimates that the worst case scenario 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.

Note: Do not use negative signs with your answers and round answers to the nearest whole dollar.

Budget Best Case Worst Case

Sales: Regular Answer

$8000$

Answer

$8,000$

Answer

DM: Plastic: Regular Answer

Answer

Answer

DM: Assembly Kits: Regular Answer

Answer

Answer

DL: Regular Answer

Answer

Answer

Overhead: Regular Answer

Answer

Answer

Contribution margin: Regular Answer

Answer

Answer

Budget Best Case Worst Case

Sales: Deluxe Answer

Answer

Answer

DM: Plastic: Deluxe Answer

Answer

Answer

DM: Assembly Kits: Deluxe Answer

Answer

Answer

DL: Deluxe Answer

Answer

Answer

Overhead: Deluxe Answer

Answer

Answer

Contribution margin: Deluxe Answer

Answer

Answer

b$.$ Create a stacked column 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?

$1.$ The top of the bar for the budget scenario falls between Answer

and Answer

$2.$ The top of the bar for the best case scenario falls between Answer

and Answer

$3.$ The top of the bar for the worst case scenario falls between Answer

and Answer

c$.$ How could calculating a best and worst case scenario assist management in planning?

Determine which answer is not correct.

Incorrect answer: Answer

$1.$ Assists management in planning for resources needed for the period.

$2.$ Helps management react more quickly to change.

$3.$ Puts management in a better position to adapt to a best or worst case scenario.

$4.$ Eliminates the risk of a worst case scenario and improves the chances of a best case scenario.