Question I. Problem Statement: A company is evaluating the production and sale of a new

product. The projected wholesale selling price of the product is $$225\mathrm{.}00$ per case. Fixed costs

and variable costs $($production$,$ marketing, shipping$)$ anticipated to be incurred in the

production of this new product are estimated as follows:

Annual fixed cost $=$$$120,000\mathrm{.}00$

Production cost $=$$$125\mathrm{.}00$ per case

Marketing fee $=3\mathrm{.}0\%$ of selling price

Shipping cost $=,$$$19\mathrm{.}00$ per case

Develop a spreadsheet model in Excel to estimate breakeven quantities to cover fixed and

total costs as well as projected net returns for the sales forecast of $2,000$ cases in the following

format:

Question I. Problem Statement: A company is evaluating the production and sale of a new product. The projected wholesale selling price of the product is $$225\mathrm{.}00$ per case. Fixed costs and variable costs $($production$,$ marketing, shipping$)$ anticipated to be incurred in the production of this new product are estimated as follows:

Annual fixed cost $=$$$120,000\mathrm{.}00$ Marketing fee $=3\mathrm{.}0\%of$ selling price

Production cost $=$$$125\mathrm{.}00$ per case Shipping cost $=,$$$19\mathrm{.}00$ per case

Develop a spreadsheet model in Excel to estimate breakeven quantities to cover fixed and total costs as well as projected net returns for the sales forecast of $2,000$ cases in the following format:

$(1.)$

$($a$)$ What is the breakeven quantity necessary to cover fixed costs?

cases

$($b$)$ What is the breakeveh quantity necessary to cover total costs?

cases

$(2.)$

$($a$)$ What is the net profit per unit at $2,000$ cases of production per case

$($b$)$ What is the net profit per unit at $2,500$ cases of production case

$($c$)$ What per unit revenue or cost changed to result in this increased profit $q,$

$(3.)$ If the selling price decreases from $$225\mathrm{.}00$ to $$220\mathrm{.}00$ per case:

$($a$)$ What will happen to the breakeven quantity: increase or decrease

$($b$)$ What will be the new breakeven quantity required to cover total costs? $q,$

$(4.)$ If the production cost decreases from $$125\mathrm{.}00$ to $$115\mathrm{.}00$ per case:

$($a$)$ What will happen to the breakeven quantity: increase or decrease $q,$

$($b$)$ What will be the new breakeven quantity required to cover total costs? $q,$