Submit an Excel $(.$xlsx$)$ workbook with ONLY ONE SHEET containing the solution to the following question. Please DO NOT hard$-$code data in $($calculation formulae must be visible by clicking cells in Excel$).$ Exact duplicates of worksheets or worksheets with only formatting differences will be considered copies of each other and hence disqualified from grading. This is expected to be an individual assignment.

Exercise Problem

Key Storage Inc. produces USB drives. Sales have been very erratic, with some months showing a profit and some months showing a loss. The company$$s contribution format income statement for the most recent month is given below:

Sales $(20,000$ units$)$ $$500,000$

Less: Variable expenses $$320,000$

Contribution Margin $$180,000$

Less: Fixed expenses $$200,000$

Net operating loss $$(20,000)$

Required:

$1.$ Compute the company$$s CM ratio and its break$-$even point in both units and dollars. $($Do not round intermediate calculations. Round your "Break$-$even point in units" answer up to nearest whole number.$)$

$2.$ The sales manager feels that an $$10,000$ increase in the monthly advertising budget, combined with an intensified effort by the sales staff, will result in a $$100,000$ increase in monthly sales. If the sales manager is right, what will be the effect on the company$$s monthly net operating income or loss? $($Use the incremental approach in preparing your answer.$)$

$3.$ Refer to the original data. The president is convinced that a $10\%$ reduction in the selling price, combined with an increase of $$35,000$ in the monthly advertising budget, will double unit sales. Should the company make these changes?

$4.$ Refer to the original data. The company$$s advertising agency thinks that a new package would help sales. The new package being proposed would increase packaging costs by $$1$ per unit. Assuming no other changes, how many units would have to be sold each month to earn a profit of $$4,500?($Do not round intermediate calculations. Round your answer up to nearest whole number.$)$

$5.$ Refer to the original data. By automating, the company could slash its variable expenses in half. However, fixed costs would increase by $$118,000$ per month.

a$.$ Compute the new CM ratio and the new break$-$even point in both units and dollars. $($Do not round intermediate calculations. Round your "Break$-$even point" answers up to nearest whole number.$)$

b$.$ Assume that the company expects to sell $20,000$ units next month. Prepare two contribution format income statements: one assuming that operations are not automated, and one assuming that they are. $($Do not round intermediate calculations. Round "Per Unit" answers to $2$ decimal places.$)$

c$.$ What is the point of indifference between the two options? $($Do not round intermediate calculations. Round your answer up to nearest whole number.$)$

$6.$ Plot a graph in Excel to clearly show the break$-$even point for both cases $($automation and no automation$).$