: A company is evaluating the production and sale of a new product. The projected wholesale selling price of the product is $225.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.00 Marketing fee = 3.0% of selling price
Production cost = $125.00 per case Shipping cost = $19.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:
Data Results
Selling price ($/case) $xx.xx Total Revenue $xxx
Annual Fixed Cost $xx,xxx.xx Total Fixed Costs $xxx
Production Cost ($/case) $xx.xx Returns above Fixed Costs $xxx
Marketing Fee (% of price) x.x% Production Costs $xxx
Marketing Fee ($/case) $xx.xx Marketing Costs $xxx
Shipping Cost ($/case) $xx.xx Shipping Costs $xxx
Sales Forecast (cases) xxx Total Variable Costs $xxx
Total Costs $xxx
Variable Cost per Unit ($/case) $xx.xx Net Profit (Loss) $xxx
Fixed Cost per Unit ($/case) $xx.xx Net Profit per Unit ($/case) $xx.xx
Total Cost per Unit ($/case) $xx.xx Break-Even Quantity:
BE to cover Fixed Costs xxx.x
Production Quantity (cases) xxx.x BE to cover Total Costs xxx.x
(1.) (a) What is the breakeven quantity necessary to cover fixed costs? __________ cases
(b) What is the breakeven quantity necessary to cover total costs? __________ cases