Question

In: Accounting

HAS TO BE SOLVED IN EXCEL Exercise 5A–1 High-Low Method LO5–11 NEEDS to be solved and...

HAS TO BE SOLVED IN EXCEL

Exercise 5A–1 High-Low Method LO5–11 NEEDS to be solved and shown in excel The Cheyenne Hotel in Big Sky, Montana, has accumulated records of the total electrical costs of the hotel and the number of occupancy-days over the last year. An occupancy-day represents a room rented for one day. The hotel’s business is highly seasonal, with peaks occurring during the ski season and in the summer. Month Occupancy-Days Electrical Costs January 1,736 $  4,127 February 1,904 $4,207 March 2,356 $5,083 April 960 $2,857 May 360 $   1,871 June 744 $2,696 July 2,108 $4,670 August 2,406 $ 5,148 September 840 $   2,691 October 124 $   1,588 November 720 $2,454 December 1,364 $3,529 Required: Using the high-low method, estimate the fixed cost of electricity per month and the variable cost of electricity per occupancy-day. Round off the fixed cost to the nearest whole dollar and the variable cost to the nearest whole cent. What other factors in addition to occupancy-days are likely to affect the variation in electrical costs from month to month

Solutions

Expert Solution

Electrical Costs
Occupancy Days Electrical Costs
High level of activity                                    2,406 $5,148
Low level of activity                                       124 $1,588
Change                                    2,282 $3,560
Variable cost per per occupancy days =$3,560 / 2,282 =$1.56 per Occupancy Days
Fixed cost element =$5,148 - (2406*$1.56) =$1,395

Related Solutions

Exercise 5A-1 High-Low Method [LO5-10] The Cheyenne Hotel in Big Sky, Montana, has accumulated records of...
Exercise 5A-1 High-Low Method [LO5-10] The Cheyenne Hotel in Big Sky, Montana, has accumulated records of the total electrical costs of the hotel and the number of occupancy-days over the last year. An occupancy-day represents a room rented for one day. The hotel’s business is highly seasonal, with peaks occurring during the ski season and in the summer. Month Occupancy-Days Electrical Costs January 3,560 $ 6,573 February 3,420 $ 6,209 March 4,120 $ 7,022       April 1,990 $ 5,174      ...
Exercise 5A-2 Least-Squares Regression [LO5-11]
Exercise 5A-2 Least-Squares Regression [LO5-11]   Bargain Rental Car offers rental cars in an off-airport location near a major tourist destination in California. Management would like to better understand the variable and fixed portions of it car washing costs. The company operates its own car wash facility in which each rental car that is returned is thoroughly cleaned before being released for rental to another customer. Management believes that the variable portion of its car washing costs relates to the...
Problem 5A-7 Cost Behavior; High-Low Method; Contribution Format Income Statement [LO5-10] Morrisey & Brown, Ltd., of...
Problem 5A-7 Cost Behavior; High-Low Method; Contribution Format Income Statement [LO5-10] Morrisey & Brown, Ltd., of Sydney is a merchandising company that is the sole distributor of a product that is increasing in popularity among Australian consumers. The company’s income statements for the three most recent months follow: Morrisey & Brown, Ltd. Income Statements For the Three Months Ended September 30 July August September Sales in units 5,500 6,000 6,500 Sales $ 577,500 $ 630,000 $ 682,500 Cost of goods...
Problem 5A-7 Cost Behavior; High-Low Method; Contribution Format Income Statement [LO5-10] Morrisey & Brown, Ltd., of...
Problem 5A-7 Cost Behavior; High-Low Method; Contribution Format Income Statement [LO5-10] Morrisey & Brown, Ltd., of Sydney is a merchandising company that is the sole distributor of a product that is increasing in popularity among Australian consumers. The company’s income statements for the three most recent months follow: Morrisey & Brown, Ltd. Income Statements For the Three Months Ended September 30 July August September Sales in units 6,000 6,500 7,000 Sales $ 570,000 $ 617,500 $ 665,000 Cost of goods...
Problem 5A-7 Cost Behavior; High-Low Method; Contribution Format Income Statement [LO5-10] Morrisey & Brown, Ltd., of...
Problem 5A-7 Cost Behavior; High-Low Method; Contribution Format Income Statement [LO5-10] Morrisey & Brown, Ltd., of Sydney is a merchandising company that is the sole distributor of a product that is increasing in popularity among Australian consumers. The company’s income statements for the three most recent months follow: Morrisey & Brown, Ltd. Income Statements For the Three Months Ended September 30 July August September Sales in units 8,500 9,000 9,500 Sales $ 850,000 $ 900,000 $ 950,000 Cost of goods...
Exercise 5-11 Comprehensive Exercise; Second Production Department-Weighted-Average Method [LO5-2, LO5-3, LO5-4, LO5-5] Scribners Corporation produces fine...
Exercise 5-11 Comprehensive Exercise; Second Production Department-Weighted-Average Method [LO5-2, LO5-3, LO5-4, LO5-5] Scribners Corporation produces fine papers in three production departments—Pulping, Drying, and Finishing. In the Pulping Department, raw materials such as wood fiber and rag cotton are mechanically and chemically treated to separate their fibers. The result is a thick slurry of fibers. In the Drying Department, the wet fibers transferred from the Pulping Department are laid down on porous webs, pressed to remove excess liquid, and dried in...
Exercise 5-11 Comprehensive Exercise; Second Production Department-Weighted-Average Method [LO5-2, LO5-3, LO5-4, LO5-5] Scribners Corporation produces fine...
Exercise 5-11 Comprehensive Exercise; Second Production Department-Weighted-Average Method [LO5-2, LO5-3, LO5-4, LO5-5] Scribners Corporation produces fine papers in three production departments—Pulping, Drying, and Finishing. In the Pulping Department, raw materials such as wood fiber and rag cotton are mechanically and chemically treated to separate their fibers. The result is a thick slurry of fibers. In the Drying Department, the wet fibers transferred from the Pulping Department are laid down on porous webs, pressed to remove excess liquid, and dried in...
Required information Exercise 5A-2 Least-Squares Regression [LO5-11] [The following information applies to the questions displayed below.]...
Required information Exercise 5A-2 Least-Squares Regression [LO5-11] [The following information applies to the questions displayed below.] Bargain Rental Car offers rental cars in an off-airport location near a major tourist destination in California. Management would like to better understand the variable and fixed portions of it car washing costs. The company operates its own car wash facility in which each rental car that is returned is thoroughly cleaned before being released for rental to another customer. Management believes that the...
Required information Exercise 5A-2 (Algo) Least-Squares Regression [LO5-11] [The following information applies to the questions displayed...
Required information Exercise 5A-2 (Algo) Least-Squares Regression [LO5-11] [The following information applies to the questions displayed below.] Bargain Rental Car offers rental cars in an off-airport location near a major tourist destination in California. Management would like to better understand the variable and fixed portions of its car washing costs. The company operates its own car wash facility in which each rental car that is returned is thoroughly cleaned before being released for rental to another customer. Management believes that...
1. High-Low Method for a Service Company Boston Railroad decided to use the high-low method and...
1. High-Low Method for a Service Company Boston Railroad decided to use the high-low method and operating data from the past six months to estimate the fixed and variable components of transportation costs. The activity base used by Boston Railroad is a measure of railroad operating activity, termed “gross-ton miles,” which is the total number of tons multiplied by the miles moved. Transportation Costs Gross-Ton Miles January $854,100 325,000 February 952,200 363,000 March 673,000 235,000 April 913,000 351,000 May 765,700...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT