Overhead Cost and Break Even

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 "yourgroupmemberslastnamesSolution".
	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 "absorptionandvariablecosting". 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.