As the management accountant for Superior Log Cabins, Inc. you have been asked to attend a planning meeting for the 2012 season. The owner specifically wants to know how many log cabins must be sold to earn a profit of $300,000 in 2012. The company makes three models of cabins: deluxe, standard, and basic. At the end of 2011, the local utility company began charging Superior as a mixed cost: an annual fee plus a variable cost for each kilowatt of power the company uses (rather than solely at a fixed rate). Each cabin (all three models) requires approximately 8,000 kilowatt hours of electricity. The company needs to add that new utility cost to the projected income statement for 2012. Cabins are constructed at three separate locations (one for each model), so the utility company will estimate costs separately for each model. The owner estimates that sales in 2012 will be 25 basic cabins, 35 deluxe cabins, and 55 standard cabins, and they will produce exactly that quantity. The current selling price per unit is $79,000 for basic, $112,000 for deluxe, and $91,000 for standard cabins and will not increase next year. Assume that there are $150,000 of fixed administrative expenses per model and $5,500 of variable selling expenses for each camper sold, no matter what model it is. The company uses job order costing. See 'Business Issue Data Formatted' sheet for these data assumptions in table, rather than text, form. ADDITIONAL DATA:
Table 1: 2011 Utility Usage and Costs (based on similar firms' experience) Table 2: Estimated Costs by Model for 2012
Kilowatts Utility Cost Component Model Fixed Costs Variable Cost per unit Units Total Cost
187976 135500 DM Used Basic 38000 25 950000
219764 170000 DL Basic 28000 25 700000
196644 160000 Mfg O/H Basic 18000 25 18000
143987 114000 DM Used Deluxe 52000 35 1820000
156983 128000 DL Deluxe 37000 35 1295000
115300 89000 Mfg O/H Deluxe 18000 35 18000
156392 108000 DM Used Standard 43000 55 2365000
125982 98000 DL Standard 32000 55 1760000
151999 88000 Mfg O/H Standard 18000 55 18000
170637 140000
294000 165000
184763 144000
REQUIREMENTS (# in parentheses refers to the Excel Function Directory):
1 Format your Solution Worksheet:
1a. Open a new worksheet and name it "".
1c. Insert a header and put your name on the left-hand side and your student # on the right-hand side.
2 Estimate the 2012 cost of utilities using the high-low method
2a. Use these two data points and the high-low method to find a cost equation for utilities.
2b. Using your cost equation and the predicted number of kilowatt hours needed in 2012 , estimate the utility costs for Basic model cabins.
3 Estimate the 2012 cost of utilities using the least-squares regression method.
3a. Estimate the total predicted number of kilowatt hours needed in 2012 to estimate the utility costs for Basic model cabins.
4 Update the estimated 2012 costs with the predicted utility costs.
4a. Copy Table 2 to your solution worksheet, and add the estimated 2012 utility costs for each type of camper to the prediction of other manufacturing overhead costs in the data above. Indicate which of the above methods (Requirement 2-4 above) you are relying on for the estimate of 2012 costs. Note that the three models will be billed separately, so the fixed costs from your cost equation will apply to each model separately (not shared between the three models; the fixed costs will be the same for each model).
4b. Convert the data updated with utility costs to a pivot table (#3) displaying total costs (values) by model (columns) and components (rows). Note: If you make any changes to the underlying data (from Requirement #5a), you must refresh the pivot table. Click on the pivot table, then Pivot Table Tools-Options-Refresh.
5 Prepare a projected income statement for 2012 (in good form) under the absorption costing approach.
5a. Open a new worksheet, call it "". Your income statement should show revenues, costs, and net operating income for each of the three product lines. Use the component product costs for each model directly from the pivot table (use the GetPivotData function) in the cost of goods sold calculations. Check to see that the total product costs for the absorption costing method agree with the grand totals from the pivot table. Assume production stated in the Business Issue occurred and is equal to sales.
6. Prepare a projected income statement for 2012 (in good form) using the variable costing approach.
6a. Place this income statement right below the absorption costing statement. Also show revenues, costs, and net operating income for each of the three product lines. Using cost-volume-profit analysis, determine what total number of units must be sold in order to earn a company-wide profit of $300,000. Add a comment by your calculation to draw attention to this figure. When you have finished these two income statements, remove the gridlines so they are easier to read.