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

At the end of 2018, 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 type (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 2019. Note that the current fixed cost estimate of $18,000 per cabin type in Table 2 is NOT related to 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.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.

2 Estimate the 2019 cost of utilities using a scattergraph chart.

2a. From the data given in the Business Issue, determine the predicted number of kilowatt hours needed in 2019 for the Basic model cabins. Estimate (eyeball) the utility costs expected for this usage level from your scattergraph. Draw a line from the top right of the chart, through the data points, until it crosses the Y axis (use Insert-Illustrations-Shapes-Line).

3 Estimate the 2019 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 2018.

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 2019 , estimate the utility costs for Basic model cabins.

4 Estimate the 2019 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 2019, estimate the utility costs for Basic model cabins.

4c. Use the FORECAST.LINEAR function and the total predicted number of kilowatt hours needed in 2019 to estimate the utility costs for Basic model cabins.

5 Update the estimated 2019 costs with the predicted utility costs.

5a. Copy Table 2 to the solution worksheet, and add the estimated 2019 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 2019 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).

5b. Convert the data updated with utility costs to a pivot table 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.

6 Prepare an projected income statement for 2019 (in good form) under the absorption costing approach.

6a.Open a new worksheet, call it "yourlastnameStmts".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 an projected income statement for 2019 (in good form) using the variable costing approach.

7a.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 $450,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.

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

Kilowatts Utility Cost

187,976 135,537

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 2019

Component Model Fixed Costs Variable Cost per unitUnits TotalCost

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