Enter a formula in cell C$14$ on the Contribution worksheet that sums the two fixed costs in cells B$12$ and B$13.$

Enter a formula in cell C$15$ that calculates the Net Operating Income. The formula should subtract the total fixed costs in cell C$14$ from the contribution margin in cell C$10($C$10-$C$14).$

Enter a SUMIFS function into cell F$5$ on the Contribution worksheet that sums the Sales Units on the Sales and Cost Data worksheet. The setup of this function is identical to the SUMIFS function in step $3$ that was used to calculate the Sales Revenue. However, this function should find a match to the item in cell E$5($Sales Units$)$ in column C on the Sales and Cost Data worksheet. Add cell capacity to include row $100$ on the Sales and Cost Data worksheet.

Enter the value $60000$ into cell F$15$ on the Contribution worksheet. The first scenario will calculate sales targets in order for the company to achieve a net operating income of $$60,000.$

Enter a formula into cell F$11$ that calculates the contribution margin per unit. The formula should divide the contribution margin in cell C$10$ by the Sales Units in cell F$5.$

Enter a formula into cell F$9$ that calculates the variable cost ratio. This formula should divide the total variable cost in cell C$9$ by the sales revenue in cell C$5.$

Enter a formula into cell F$10$ that calculates the contribution margin ratio. This formula should subtract from the number $1$ the variable cost ratio in cell F$9.$ The contribution margin ratio and the variable cost ratios are percentages of the sales revenue. When added together, these ratios must equal $1$ or $100\%.$

Enter a formula in cell F$7$ that calculates the Sales Target in Units which is needed to achieve the net operating income of $$60,000.$ The formula should first add the target profit in cell F$15$ to the total fixed costs in cell C$14.$ This result should be divided by the contribution margin per unit in cell F$11.$

Enter a formula in cell F$6$ that calculates the Sales Target in Dollars which is needed to achieve the net operating income of $$60,000.$ The formula should first add the target profit in cell F$15$ to the total fixed costs in cell C$14.$ This result should be divided by the contribution margin ratio in cell F$10.$ The results show that based on the contribution format income statement for $2019,$ in order for the company to achieve $$60,000$ in net operating income, sales revenue must be $$436,721.$ Sales in units must be $6,612.$ These results will change from year to year as the cost and sales data change.

Change the year in cell B$1$ to $2020.$ Notice the company is showing a loss of $(12,106)$ with sales revenue at $$408,125.$

Change the value in cell F$15$ to $0.$ Notice the Sales Target in Dollars $$437,554.$ This is known as the break even point. In order for the company to break$-$even with a net operating income of $0,$ the sales revenue must be $$437,554.$ Note the sales revenue for $2020$ is lower than this value, which is why the net operating income is less than $0.$