ABI-WE2 - ABI-WE 2: Huron Mobile Camping, Inc. Business...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
ABI-WE 2: Huron Mobile Camping, Inc. Business Issue TOPIC: Overhead Costs and Break-Even (CH 4-5-6) ADDITIONAL DATA: Table 2: Estimated Costs by Model for 2012 Kilowatts Component Model Units Total Cost 187976 135650 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 refer to the Excel Function Directory): 1 Format your Solution Worksheet: a. Open a new worksheet and name it "yourlastnameSolution". c. Insert a header (#8) and put your name on the left-hand side and your student # on the right-hand side. 2 a. Create a Scattergraph chart (#33) of the Kilowatt hours and utility costs in Table 1. b. Adjust the Scattergraph (#34) to have an appropriate title (e.g., Kilowatt hours, KWH) on the horizontal axis. 3 Example: =VLOOKUP(156983, B9:C21, 2, 0) would return the value 128000 from Table 1 above. c. Use these two data points and the high-low method to find a cost equation for utilities. 4 a. Use the SLOPE function (#25) and the INTERCEPT function (#26) to determine a cost equation for utilities. 5 Update the estimated 2012 costs with the predicted utility costs. 6 7 8 9 3 = Pivot Table (Insert-Tables-Pivot Table) 16= Remove gridlines (View-Show/Hide-Gridlines uncheck) 17 = Add a comment to a cel (Review-Comments-New Comment-Show Al Comments) 25 = Slope (Formulas-Function Library-More Functions-Statistical-Slope) 26 = Intercept (Formulas-Function Library-More Functions-Statistical-Intercept) 27 = Forecast (Formulas-Function Library-More Functions-Statistical-Forecast) 28 = Minimum (Formulas-AutoSum-Min) 28 = Maximum (Formulas-AutoSum-Max) 33 = Scattergraph chart (Insert-Charts-Scatter), then Insert a line (Insert-Il ustrations-Shapes-Lines) Continue to use these functions from prior ABI-WE assignments: #1, #8-12, #14, #21, #37-38. GRADING RUBRIC: 5 5 5 5 Total: 20 As the management accountant for Huron Mobile Camping, you've been asked to attend a planning meeting for the 2012 season. The owner specifical y wants to know how many camper trailers must be sold to earn a profit of $300,000 in 2012. The company makes three models of camper trailers: deluxe, standard, and basic. At the end of 2011, the local utility company began charging Huron as a mixed cost: an annual fee plus a variable cost for each kilowatt of power the company uses (rather than solely at a a fixed rate). Each camper (al 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. Campers are constructed at three separate locations (one for each model), so the utility company wil
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 4

ABI-WE2 - ABI-WE 2: Huron Mobile Camping, Inc. Business...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online