This mini case aims to demonstrate your understanding of the interrelationship between cost estimation techniques and subsequent uses of cost information. You will use cost information estimated from a high-low analysis in a single and multiproduct CVP analysis. You will be working with Many Glacier Hotel.

Do the assignment in Excel. Set each of the 6 parts up in a separate tab (name your tabs). Use formulas, cell references, and good formatting. Properly label your inputs and variables. Format dollars with $, and format units using commas if the number is over 999. Set up the worksheet so that it is easy to follow and review. Clearly label and highlight the solution to each requirement.

Using the High-Low Method to Estimate Variable and Fixed Costs Located on Swiftcurrent Lake in Glacier National Park, Many Glacier Hotel was built in 1915 by the Great Northern Railway. To supplement its lodging revenue, the hotel decided to begin manufacturing and selling small wooden canoes decorated with symbols hand painted by Native Americans living near the park. The canoes were a great success, so a couple of years later the hotel began manufacturing and selling paddles. Many hotel guests purchase a canoe and paddles for use in self-guided tours of Swiftcurrent Lake. Because production of the two products began in different years, the canoes and paddles are produced in separate production facilities and employ different laborers. Each canoe sells for $540, and each paddle sells for $60. About 15 years ago, a fire destroyed the hotels accounting records. A new system put into place before the next season provides the following aggregated data for the hotels canoe and paddle manufacturing and marketing activities (Years 1 through 12 give the data for the years in which the new accounting system was active):

Required:

High-Low Cost Estimation Method

Use the high-low method to estimate the per-unit variable costs and total fixed costs for the canoe product line.

Use the high-low method to estimate the per-unit variable costs and total fixed costs for the paddle product line.

Cost-Volume-Profit Analysis, Single-Product Setting

Use CVP analysis to calculate the break-even point in units for

The canoe product line only (i.e., single-product setting)

The paddle product line only (i.e., single-product setting)

Cost-Volume-Profit Analysis, Multiple-Product Setting

The hotels accounting system data show an average sales mix of approximately 300 canoes and 1,200 paddles each season. Significantly more paddles are sold relative to canoes because some inexperienced canoe guests accidentally break one or more paddles, while other guests purchase additional paddles as presents for friends and relatives. In addition, for this multiple-product CVP analysis, assume there is an additional $28,000 of common fixed costs for a customer service hotline used for both canoe and paddle customers. Use CVP analysis to calculate the break-even point in units for both the canoe and paddle product lines combined (i.e., the multiple-product setting).

Cost Classification

Classify the manufacturing costs, marketing costs, and customer service hotline costs either as production costs or period costs.

For the period costs, further classify them into either selling expenses or general and administrative expenses.

Sensitivity Cost-Volume-Profit Analysis and Production Versus Period Costs, Multiple-Product Setting

If both the variable and fixed production costs (refer to your answer to Requirement 1) associated with the canoe product line increased by 5% (beyond the estimate from the high-low analysis), how many canoes and paddles would need to be sold in order to earn a target income of $96,000? Assume the same sales mix and additional fixed costs as in Requirement 3.

Margin of Safety

Calculate the hotels margin of safety (both in units and in sales dollars) for Many Glacier Hotel, assuming the same facts as in Requirement 3, and assuming that it sells 650 canoes and 2,400 paddles next year.

RUBRIC:

\begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|c|}{ Manufacturing Data } \\ \hline Year & \begin{tabular}{c} Number of \\ Canoes \\ Manufactured \end{tabular} & \begin{tabular}{c} Total Canoe \\ Manufacturing \\ Costs \end{tabular} & Year & \begin{tabular}{c} Number of \\ Paddles \\ Manufactured \end{tabular} & \begin{tabular}{l} Total Paddle \\ Manufacturin \\ Costs \end{tabular} \\ \hline 1 & 250 & $103,000 & 1 & 900 & $38,500 \\ \hline 2 & 240 & 115,000 & 2 & 1,200 & 49,000 \\ \hline 3 & 275 & 128,000 & 3 & 1,000 & 44,000 \\ \hline 4 & 310 & 114,000 & 4 & 1,100 & 45,500 \\ \hline 5 & 350 & 141,500 & 5 & 1,400 & 52,000 \\ \hline 6 & 380 & 132,000 & 6 & 1,700 & 66,500 \\ \hline 7 & 415 & 146,500 & 7 & 1,720 & 66,300 \\ \hline 8 & 430 & 132,000 & 8 & 1,850 & 71,750 \\ \hline 9 & 450 & 146,100 & 9 & 1,900 & 72,000 \\ \hline 10 & 470 & 155,000 & 10 & 2,020 & 78,900 \\ \hline 11 & 480 & 136,000 & 11 & 2,050 & 78,200 \\ \hline 12 & 500 & 167,000 & 12 & 2,200 & 84,000 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline Marketir & & & & & \\ \hline Year & \begin{tabular}{c} Number \\ of Canoes \\ Sold \end{tabular} & \begin{tabular}{c} Total \\ Canoe \\ Marketing \\ Costs \end{tabular} & Year & \begin{tabular}{c} Number \\ of Paddles \\ Sold \end{tabular} & \begin{tabular}{c} Total \\ Paddle \\ Marketing \\ Costs \end{tabular} \\ \hline 1 & 250 & $45,000 & 1 & 900 & $7,500 \\ \hline 2 & 240 & 47,000 & 2 & 1,200 & 9,000 \\ \hline 3 & 275 & 43,000 & 3 & 1,000 & 8,000 \\ \hline 4 & 310 & 51,000 & 4 & 1,100 & 8,500 \\ \hline 5 & 350 & 62,000 & 5 & 1,400 & 10,000 \\ \hline 6 & 380 & 53,000 & 6 & 1,700 & 11,500 \\ \hline 7 & 415 & 68,500 & 7 & 1,720 & 11,600 \\ \hline 8 & 430 & 63,000 & 8 & 1,850 & 12,250 \\ \hline 9 & 450 & 65,000 & 9 & 1,900 & 12,500 \\ \hline 10 & 470 & 67,000 & 10 & 2,020 & 13,100 \\ \hline 11 & 480 & 52,000 & 11 & 2,050 & 13,250 \\ \hline 12 & 500 & 73,000 & 12 & 2,200 & 14,000 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline & Requirement & PointsPossible \\ \hline 1 & \begin{tabular}{l} Canoe per unit variable costs and \\ total fixed costs--manufacturing \end{tabular} & 3 \\ \hline 1 & \begin{tabular}{l} Canoe per unit variable costs and \\ total fixed costs--marketing \end{tabular} & 3 \\ \hline 1 & \begin{tabular}{l} Total variable and fixed costs for \\ Canoe \end{tabular} & 1 \\ \hline 1 & \begin{tabular}{l} Paddle per unit variable costs and \\ total fixed costs--maufacturing \end{tabular} & 3 \\ \hline 1 & \begin{tabular}{l} Paddle per unit variable costs and \\ total fixed costs--marketing \end{tabular} & 3 \\ \hline 1 & \begin{tabular}{l} Total variable and fixed costs for \\ Paddle \end{tabular} & 1 \\ \hline 2 & Paddle break even & 5 \\ \hline 3 & \begin{tabular}{l} Set up table showing sales mix \\ and the package contribution \\ margin (use your unit variable \\ costs from number 1 ) \end{tabular} & 4 \\ \hline 3 & Break even units canoes & 2 \\ \hline 3 & Break even units paddles & 2 \\ \hline 4 & Cost classification & 3 \\ \hline 4 & Classify period costs & 3 \\ \hline 5 & Computation of variable costs & 2 \\ \hline 5 & \begin{tabular}{l} Updated sales mix table and \\ contribution margin \end{tabular} & 4 \\ \hline 5 & Canoe target units & 2 \\ \hline 5 & Paddle target units & 2 \\ \hline 6 & Margin of Safety Canoe units & 2 \\ \hline 6 & Margin of Safety Paddle Units & 2 \\ \hline 6 & Margin of Saftey Sales Dollars & 2 \\ \hline & Proper file name & 1 \\ \hline & \begin{tabular}{l} Formatting (use of $, commas in \\ numbers over 999, totals \\ underlined, etc.) \\ \end{tabular} & 5 \\ \hline & \begin{tabular}{l} Deductions for formulas or \\ turning in late (see yellow box \\ above) \end{tabular} & \\ \hline \end{tabular}