ATC $6-6($Static$)$ Spreadsheet Assignment Using Excel

Dorina Company makes cases of canned dog food in batches of $1,000$ cases and sells each case for $$15.$ The plant capacity is $50,000$ cases; the company currently makes $40,000$ cases. Doggie$-$Mart has offered to buy $1,500$ cases for $$12$ per case. Because product$-$level and facility$-$level costs are unaffected by a special order, they are omitted.

Required

Complete the spreadsheet to calculate the contribution to income if the special order is accepted. Construct formulas so that the number of cases or the price could be changed and the new contribution would be automatically calculated.

Spreadsheet Tips

The numbers in cells B $18$ to B $20$ should be formulas that refer to B $15.$ This allows the number of cases to be changed in cell B$15$ with the other cells changing automatically.

The formula in cell B$21$ uses a function named ROUNDUP to calculate the even number of batches. The formula should be $=$ROUNDUP$($B$15/1000,0)$ where the zero refers to rounding up to the nearest whole number.

$($Ungraded$)$ Explore the power of formulas by copying cells A$14$ to G$23$ into the scratchpad area below. Try different order sizes $($such as $2,000)$ or different prices to see the effect on contribution to profit.

Navigation:

Use the Open Excel in New Tab button to launch this question.

When finished in Excel, use the Save and Return to Assignment button in the lower right to return to Connect.