As the management accountant for Superior Log Cabins, Inc. you have been asked to attend a planning meeting for the 2020 season. The owner specifically wants to know how many log cabins must be sold to earn a profit of $450,000 in 2020. The company makes three models of cabins: deluxe, standard, and basic.

At the end of 2019, 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, regardless of model type, requires approximately 8,000 kilowatt hours of electricity. The company needs to add that new utility cost to the projected income statement for 2020. Note that the current fixed cost estimate of $18,000 per cabin model in Table 2 is NOT related to the new utility cost, but is a fixed manufacturing overhead cost that will continue to exist along with the fixed portion of the new utility cost.

Cabins are constructed at three separate locations (one for each model), so the utility company will estimate costs separately for each model type. Thus, the total fixed cost and the variable cost per unit will respectively be the same at each of the three locations. The owner estimates that sales in 2019 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 cabin sold, no matter what model it is. The company uses job order costing.

Table 1: 2019 Utility Usage and Costs (based on similar firms' experience)

Kilowatts	Utility Cost
187,976	135,627
219,764	170,000
196,644	160,000
143,987	114,000
156,983	128,000
115,300	89,000
156,392	108,000
125,982	98,000
151,999	88,000
170,637	140,000
294,000	165,000
184,763	144,000

Table 2: Estimated Costs by Model for 2020
Component	Model	Fixed Costs	Variable Cost per Cabin	Cabins	Total Cost
DM Used	Basic		38,000	25	950,000
DL	Basic		28,000	25	700,000
Mfg O/H	Basic	18,000		25	18,000
DM Used	Deluxe		52,000	35	1,820,000
DL	Deluxe		37,000	35	1,295,000
Mfg O/H	Deluxe	18,000		35	18,000
DM Used	Standard		43,000	55	2,365,000
DL	Standard		32,000	55	1,760,000
Mfg O/H	Standard	18,000		55	18,000

3 Estimate the 2020 cost of utilities using the high-low method 3a. Use the Min and Max functions in Excel to find the highest and lowest numbers of kilowatts used in 2019. 3b. Use the VLOOKUP function to return the utility cost associated with the max/min number of kilowatts used determined above. 3c. Use these two data points and the high-low method to find a cost equation for utilities. 3d. Using your cost equation and the predicted number of kilowatt hours needed in 2020, clearly provide an estimate of the utility costs for Basic model cabins.

4 Estimate the 2020 cost of utilities using the least-squares regression method. 4a. Use the SLOPE function and the INTERCEPT function to determine a cost equation for utilities. 4b. Using your cost equation and the total predicted number of kilowatt hours needed in 2020, clearly provide an estimate of the utility costs for Basic model cabins. 4c. Use the FORECAST.LINEAR function and the total predicted number of kilowatt hours needed in 2020 to estimate the utility costs for Basic model cabins. 5 Update the estimated 2020 costs with the predicted utility costs. 5a. Copy Table 2 to the solution worksheet, and add the estimated 2020 utility costs for each type of cabin 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 2020 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).

6 Prepare a projected income statement for 2020 (in good form) under the absorption costing approach. 6a. Open a new worksheet. 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 (Requirement #5) 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.

7 Prepare a projected income statement for 2020 (in good form) using the variable costing approach. 7a. 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 $450,000. Use excel to 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.