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.
a. Prepare a spreadsheet like the following one 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.
b. Try different order sizes (such as 2,000) or different prices to see the effect on contribution to profit.
1. The numbers in cells F7 to F9 should be formulas that refer to F5. This allows the number of cases to be changed in cell F5 with the other cells changing automatically.
2. The formula in cell F10 uses a function named ROUNDUP to calculate the even number of batches. The formula should be _ ROUNDUP(F5/1000,0) where the zero refers to rounding up to the nearest whole number.