January, 2017. Previously, you estimated a cost function for the property and completed a CVP analysis....
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
January, 2017. Previously, you estimated a cost function for the property and completed a CVP analysis. Your supervisor has asked you to prepare a 2018 operating budget. Analysis of historical data suggests the following monthly room demand: Months January and February March-May, September-October June-August November and December Revenues. In Sunrise (B), you forecast the average daily rate and sales volume for 2018. Assume the room rate is constant throughout the year. Costs. Assume now that you ran regressions for each of the separate expense categories in your data file. Increasing the variable cost estimates by 4% (an assumption given in the B case) results in the cost formulas provided in the table below.³ . Monthly Demand 5% of annual demand. 8% of annual demand. 12% of annual demand. 7% of annual demand. Item Front desk Housekeeping Supplies Energy . An alternative to regression is the industrial engineering method of cost estimation. Your regional manager provides you with the following insights: Cost Equation $7,800 $4.30/rental * Rentals $1.35/rental * Rentals $4,700 The front desk is staffed twenty-four hours per day. Average wages of front desk staff are $8.00/hour. Your annual salary, as general manager, is $27,000. • Housekeeping should clean a room in 20 minutes. However, preparing the laundry cart and taking linens to the laundry room mean that, on average, only two rooms per hour can be cleaned. The hourly wage rate for housekeepers is expected be $7.50. A laundry worker works 20 hours per week at $7.50/hour. Room Amenities. Rooms are stocked with two bars of soap and a bottle of shampoo. Soap costs $0.14 a bar and shampoo costs $0.25 per bottle. At the beginning of each month, you want 25% of next month's requirements on hand. Beginning inventory requirements are met on 1/1. Assume January 2018 room sales will be 900 rentals. Other Supplies. Paint, light bulbs, coffee, etc. average about $1,000 per month. Required 1. Prepare a monthly operating budget for 2018. Explain why you chose the regression or industrial engineering method to estimate costs. What are the limitations of each method? 2. Prepare a monthly purchases budget for room amenities (units and amount). 5 There are more than four accounts, but to make the exercise less tedious (and error prone), figures for the remaining accounts have been filled in on your spreadsheet. Further, two unusual observations are dropped. Year Month oms Availa Rooms Rented Rooms Revenue 1 3,162 2,958 2 3 3,162 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 4 3,060 5 3,162 3,060 3,162 3,162 NHÖ68SGAWNIÑOGAN-Ñ=0000 3,060 10 3,162 3,060 12 3,162 3,162 2 2,856 3,060 3,162 3,060 3,162 3,060 10 3,162 3,060 11 12 3,162 3,162 2,856 3,162 3,060 3,162 3,060 3,162 3,060 10 3,162 3,060 12 3,162 1,017 823 1,393 1,421 1,594 2,040 1,984 1,764 1,475 1,271 1,012 1,141 922 896 1,234 1,546 1,871 2,003 1,542 1,194 915 900 719 788 1,367 1,253 1,644 2,030 2,107 1,722 1,242 1,095 1,014 $39,920.00 $29,777.00 $52,099.00 $51,618.00 $64,288.00 $87,321.00 $101,024.00 $80,967.00 $56,488.00 $46,754.00 $37,747.00 $38,376.00 $34,426.00 $32,728.00 $40,373.00 $58,119.00 $74,540.00 $85,785.00 $64,104.00 $48,663.00 $36,997.00 $37,519.00 $27,762.00 $26,742.00 $43,897.00 $39,692.00 $54,763.00 $78,221.00 $86,730.00 $70,093.00 $48,832.00 $42,596.00 $39,089.00 Other Revenue $180.00 $365.00 $244.00 $450.00 $655.00 $869.00 $1,396.00 $705.00 $834.00 $643.00 $556.00 $271.00 $341.00 $864.00 $574.00 $820.00 $927.00 $1,200.00 $1,025.00 $370.00 $504.00 $505.00 $463.00 $389.00 $772.00 $534.00 $862.00 $962.00 $1,038.00 $710.00 $569.00 $884.00 $561.00 Total Revenue $40,100.00 $30,142.00 $52,343.00 $52,068.00 $64,943.00 $88,190.00 $102,420.00 $81,672.00 $57,322.00 $47,397.00 $38,303.00 $38,647.00 $34,767.00 $33,592.00 $40,947.00 $58,939.00 $75,467.00 $86,985.00 $65,129.00 $49,033.00 $37,501.00 $38,024.00 $28,225.00 $27,131.00 $44,669.00 $40,226.00 $55,625.00 $79,183.00 $87,768.00 $70,803.00 $49,401.00 $43,480.00 $39,650.00 Front Office HousekeepingOther Personnelotal Personne Supplies Other Expenses $4,597.00 $923.00 $3,119.00 $8,823.00 $3,539.00 $8,263.00 $2,979.00 $8,864.00 $4,781.00 $8,452.00 $4,461.00 $7,806.00 $5,294.00 $8,265.00 $7,222.00 $9,103.00 $6,772.00 $6,076.00 $10,321.00 $8,495.00 $9,282.00 $5,366.00 $9,845.00 $4,361.00 $6,573.00 $3,212.00 $7,325.00 $3,882.00 $9,201.00 $3,774.00 $6,058.00 $3,283.00 $4,117.00 $8,942.00 $5,622.00 $9,208.00 $6,982.00 $9,159.00 $8,095.00 $7,353.00 $5,259.00 $8,714.00 $4,624.00 $8,200.00 $3,461.00 $8,811.00 $3,009.00 $8,255.00 $3,083.00 $7,390.00 $2,710.00 $5,601.00 $7,880.00 $20,242.00 $268.00 $5,978.00 $17,220.00 $867.00 $7,976.00 $21,621.00 $1,561.00 $7,014.00 $19,927.00 $2,031.00 $8,357.00 $21,457.00 $1,486.00 $6,411.00 $21,898.00 $652.00 $6,995.00 $22,870.00 $2,327.00 $4,930.00 $21,327.00 $1,908.00 $6,803.00 $21,451.00 $620.00 $6,348.00 $20,554.00 $1,456.00 $6,632.00 $16,417.00 $972.00 $6,871.00 $18,078.00 $1,105.00 $6,536.00 $19,511.00 $1,089.00 $6,162.00 $15,503.00 $787.00 $4,150.00 $16,762.00 $1,108.00 $7,387.00 $21,951.00 $2,594.00 $8,249.00 $24,439.00 $4,525.00 $6,675.00 $23,929.00 $2,038.00 $6,878.00 $19,490.00 $1,342.00 $4,695.00 $18,033.00 $2,116.00 $7,697.00 $19,358.00 $436.00 $4,603.00 $16,423.00 $2,818.00 $6,827.00 $18,165.00 $421.00 $4,774.00 $14,874.00 $1,315.00 $5,316.00 $20,636.00 $641.00 $4,963.00 $17,177.00 $1,496.00 $6,281.00 $22,292.00 $4,095.00 $5,562.00 $22,252.00 $1,887.00 $7,998.00 $25,015.00 $2,718.00 $7,776.00 $22,792.00 $3,103.00 $6,945.00 $20,510.00 $1,860.00 $7,330.00 $21,042.00 $425.00 $6,410.00 $19,989.00 $1,252.00 $1,888.00 $2,549.00 $705.00 $2,866.00 $2,085.00 $2,140.00 $558.00 $2,711.00 $2,637.00 $4,052.00 $4,938.00 $3,592.00 $731.00 $1,919.00 $1,421.00 $2,305.00 $2,298.00 $2,730.00 $711.00 $1,296.00 $1,701.00 $2,649.00 $1,323.00 $2,647.00 $1,328.00 $2,158.00 $4,800.00 $6,311.00 $3,034.00 $2,833.00 $4,441.00 $2,231.00 $9,088.00 $1,222.00 $3,062.00 $350.00 $5,040.00 $1,425.00 $7,471.00 $2,352.00 $1,975.00 $1,010.00 $5,415.00 $1,399.00 $499.00 $5,523.00 -$3,224.00 $1,037.00 $5,662.00 $1,836.00 $2,145.00 $5,121.00 $4,563.00 $1,191.00 $7,513.00 $467.00 $1,836.00 $8,430.00 $1,800.00 $1,401.00 $1,641.00 $2,156.00 $2,710.00 $4,649.00 $1,822.00 $3,503.00 $3,307.00 $3,092.00 $3,271.00 $9,173.00 $1,698.00 $1,639.00 $3,750.00 $1,732.00 $2,513.00 $952.00 -$2,447.00 $4,228.00 $9,719.00 $7,916.00 $9,498.00 $4,298.00 $6,513.00 $8,792.00 $7,898.00 $7,830.00 $9,187.00 $6,382.00 $8,634.00 $8,153.00 $5,412.00 $9,269.00 $4,443.00 $9,066.00 $4,513.00 $4,094.00 Repairs Energy $5,269.00 $4,676.00 $6,848.00 $4,056.00 $5,934.00 $7,473.00 $6,389.00 $7,396.00 $3,312.00 $3,075.00 $6,315.00 $7,865.00 $4,019.00 $2,441.00 $6,408.00 $5,321.00 $3,182.00 $6,659.00 -$3,949.00 $3,523.00 $2,401.00 -$5,875.00 $2,241.00 $2,238.00 $2,313.00 $2,378.00 $2,420.00 Other Utilities $720.00 $2,581.00 $2,336.00 $3,294.00 $1,083.00 $5,854.00 $3,107.00 -$874.00 $2,464.00 $1,914.00 $2,480.00 $2,395.00 $2,935.00 $2,046.00 $2,431.00 $2,664.00 $2,931.00 $4,118.00 $2,295.00 $5,428.00 $2,786.00 $1,399.00 $1,994.00 January, 2017. Previously, you estimated a cost function for the property and completed a CVP analysis. Your supervisor has asked you to prepare a 2018 operating budget. Analysis of historical data suggests the following monthly room demand: Months January and February March-May, September-October June-August November and December Revenues. In Sunrise (B), you forecast the average daily rate and sales volume for 2018. Assume the room rate is constant throughout the year. Costs. Assume now that you ran regressions for each of the separate expense categories in your data file. Increasing the variable cost estimates by 4% (an assumption given in the B case) results in the cost formulas provided in the table below.³ . Monthly Demand 5% of annual demand. 8% of annual demand. 12% of annual demand. 7% of annual demand. Item Front desk Housekeeping Supplies Energy . An alternative to regression is the industrial engineering method of cost estimation. Your regional manager provides you with the following insights: Cost Equation $7,800 $4.30/rental * Rentals $1.35/rental * Rentals $4,700 The front desk is staffed twenty-four hours per day. Average wages of front desk staff are $8.00/hour. Your annual salary, as general manager, is $27,000. • Housekeeping should clean a room in 20 minutes. However, preparing the laundry cart and taking linens to the laundry room mean that, on average, only two rooms per hour can be cleaned. The hourly wage rate for housekeepers is expected be $7.50. A laundry worker works 20 hours per week at $7.50/hour. Room Amenities. Rooms are stocked with two bars of soap and a bottle of shampoo. Soap costs $0.14 a bar and shampoo costs $0.25 per bottle. At the beginning of each month, you want 25% of next month's requirements on hand. Beginning inventory requirements are met on 1/1. Assume January 2018 room sales will be 900 rentals. Other Supplies. Paint, light bulbs, coffee, etc. average about $1,000 per month. Required 1. Prepare a monthly operating budget for 2018. Explain why you chose the regression or industrial engineering method to estimate costs. What are the limitations of each method? 2. Prepare a monthly purchases budget for room amenities (units and amount). 5 There are more than four accounts, but to make the exercise less tedious (and error prone), figures for the remaining accounts have been filled in on your spreadsheet. Further, two unusual observations are dropped. Year Month oms Availa Rooms Rented Rooms Revenue 1 3,162 2,958 2 3 3,162 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 4 3,060 5 3,162 3,060 3,162 3,162 NHÖ68SGAWNIÑOGAN-Ñ=0000 3,060 10 3,162 3,060 12 3,162 3,162 2 2,856 3,060 3,162 3,060 3,162 3,060 10 3,162 3,060 11 12 3,162 3,162 2,856 3,162 3,060 3,162 3,060 3,162 3,060 10 3,162 3,060 12 3,162 1,017 823 1,393 1,421 1,594 2,040 1,984 1,764 1,475 1,271 1,012 1,141 922 896 1,234 1,546 1,871 2,003 1,542 1,194 915 900 719 788 1,367 1,253 1,644 2,030 2,107 1,722 1,242 1,095 1,014 $39,920.00 $29,777.00 $52,099.00 $51,618.00 $64,288.00 $87,321.00 $101,024.00 $80,967.00 $56,488.00 $46,754.00 $37,747.00 $38,376.00 $34,426.00 $32,728.00 $40,373.00 $58,119.00 $74,540.00 $85,785.00 $64,104.00 $48,663.00 $36,997.00 $37,519.00 $27,762.00 $26,742.00 $43,897.00 $39,692.00 $54,763.00 $78,221.00 $86,730.00 $70,093.00 $48,832.00 $42,596.00 $39,089.00 Other Revenue $180.00 $365.00 $244.00 $450.00 $655.00 $869.00 $1,396.00 $705.00 $834.00 $643.00 $556.00 $271.00 $341.00 $864.00 $574.00 $820.00 $927.00 $1,200.00 $1,025.00 $370.00 $504.00 $505.00 $463.00 $389.00 $772.00 $534.00 $862.00 $962.00 $1,038.00 $710.00 $569.00 $884.00 $561.00 Total Revenue $40,100.00 $30,142.00 $52,343.00 $52,068.00 $64,943.00 $88,190.00 $102,420.00 $81,672.00 $57,322.00 $47,397.00 $38,303.00 $38,647.00 $34,767.00 $33,592.00 $40,947.00 $58,939.00 $75,467.00 $86,985.00 $65,129.00 $49,033.00 $37,501.00 $38,024.00 $28,225.00 $27,131.00 $44,669.00 $40,226.00 $55,625.00 $79,183.00 $87,768.00 $70,803.00 $49,401.00 $43,480.00 $39,650.00 Front Office HousekeepingOther Personnelotal Personne Supplies Other Expenses $4,597.00 $923.00 $3,119.00 $8,823.00 $3,539.00 $8,263.00 $2,979.00 $8,864.00 $4,781.00 $8,452.00 $4,461.00 $7,806.00 $5,294.00 $8,265.00 $7,222.00 $9,103.00 $6,772.00 $6,076.00 $10,321.00 $8,495.00 $9,282.00 $5,366.00 $9,845.00 $4,361.00 $6,573.00 $3,212.00 $7,325.00 $3,882.00 $9,201.00 $3,774.00 $6,058.00 $3,283.00 $4,117.00 $8,942.00 $5,622.00 $9,208.00 $6,982.00 $9,159.00 $8,095.00 $7,353.00 $5,259.00 $8,714.00 $4,624.00 $8,200.00 $3,461.00 $8,811.00 $3,009.00 $8,255.00 $3,083.00 $7,390.00 $2,710.00 $5,601.00 $7,880.00 $20,242.00 $268.00 $5,978.00 $17,220.00 $867.00 $7,976.00 $21,621.00 $1,561.00 $7,014.00 $19,927.00 $2,031.00 $8,357.00 $21,457.00 $1,486.00 $6,411.00 $21,898.00 $652.00 $6,995.00 $22,870.00 $2,327.00 $4,930.00 $21,327.00 $1,908.00 $6,803.00 $21,451.00 $620.00 $6,348.00 $20,554.00 $1,456.00 $6,632.00 $16,417.00 $972.00 $6,871.00 $18,078.00 $1,105.00 $6,536.00 $19,511.00 $1,089.00 $6,162.00 $15,503.00 $787.00 $4,150.00 $16,762.00 $1,108.00 $7,387.00 $21,951.00 $2,594.00 $8,249.00 $24,439.00 $4,525.00 $6,675.00 $23,929.00 $2,038.00 $6,878.00 $19,490.00 $1,342.00 $4,695.00 $18,033.00 $2,116.00 $7,697.00 $19,358.00 $436.00 $4,603.00 $16,423.00 $2,818.00 $6,827.00 $18,165.00 $421.00 $4,774.00 $14,874.00 $1,315.00 $5,316.00 $20,636.00 $641.00 $4,963.00 $17,177.00 $1,496.00 $6,281.00 $22,292.00 $4,095.00 $5,562.00 $22,252.00 $1,887.00 $7,998.00 $25,015.00 $2,718.00 $7,776.00 $22,792.00 $3,103.00 $6,945.00 $20,510.00 $1,860.00 $7,330.00 $21,042.00 $425.00 $6,410.00 $19,989.00 $1,252.00 $1,888.00 $2,549.00 $705.00 $2,866.00 $2,085.00 $2,140.00 $558.00 $2,711.00 $2,637.00 $4,052.00 $4,938.00 $3,592.00 $731.00 $1,919.00 $1,421.00 $2,305.00 $2,298.00 $2,730.00 $711.00 $1,296.00 $1,701.00 $2,649.00 $1,323.00 $2,647.00 $1,328.00 $2,158.00 $4,800.00 $6,311.00 $3,034.00 $2,833.00 $4,441.00 $2,231.00 $9,088.00 $1,222.00 $3,062.00 $350.00 $5,040.00 $1,425.00 $7,471.00 $2,352.00 $1,975.00 $1,010.00 $5,415.00 $1,399.00 $499.00 $5,523.00 -$3,224.00 $1,037.00 $5,662.00 $1,836.00 $2,145.00 $5,121.00 $4,563.00 $1,191.00 $7,513.00 $467.00 $1,836.00 $8,430.00 $1,800.00 $1,401.00 $1,641.00 $2,156.00 $2,710.00 $4,649.00 $1,822.00 $3,503.00 $3,307.00 $3,092.00 $3,271.00 $9,173.00 $1,698.00 $1,639.00 $3,750.00 $1,732.00 $2,513.00 $952.00 -$2,447.00 $4,228.00 $9,719.00 $7,916.00 $9,498.00 $4,298.00 $6,513.00 $8,792.00 $7,898.00 $7,830.00 $9,187.00 $6,382.00 $8,634.00 $8,153.00 $5,412.00 $9,269.00 $4,443.00 $9,066.00 $4,513.00 $4,094.00 Repairs Energy $5,269.00 $4,676.00 $6,848.00 $4,056.00 $5,934.00 $7,473.00 $6,389.00 $7,396.00 $3,312.00 $3,075.00 $6,315.00 $7,865.00 $4,019.00 $2,441.00 $6,408.00 $5,321.00 $3,182.00 $6,659.00 -$3,949.00 $3,523.00 $2,401.00 -$5,875.00 $2,241.00 $2,238.00 $2,313.00 $2,378.00 $2,420.00 Other Utilities $720.00 $2,581.00 $2,336.00 $3,294.00 $1,083.00 $5,854.00 $3,107.00 -$874.00 $2,464.00 $1,914.00 $2,480.00 $2,395.00 $2,935.00 $2,046.00 $2,431.00 $2,664.00 $2,931.00 $4,118.00 $2,295.00 $5,428.00 $2,786.00 $1,399.00 $1,994.00
Expert Answer:
Answer rating: 100% (QA)
Monthly Operating Expense Budget Total demand 900005 18000 Costs Computation January February MarchMay JuneAugust SeptemberOctober November December Front Desk 7800 7800 7800 7800 7800 Energy 4700 470... View the full answer
Related Book For
College Accounting
ISBN: 978-1111528126
11th edition
Authors: Tracie Nobles, Cathy Scott, Douglas McQuaig, Patricia Bille
Posted Date:
Students also viewed these business communication questions
-
Your supervisor has asked you to audit some journal entries recorded by her clients bookkeeper on the following page. Review the following transactions. If there is an error, rejournalize the entry....
-
Your supervisor has asked you to prepare some calculations based on the companys data and then research publicly available similar competitor information to see how your company is performing within...
-
Your boss has asked you to prepare a feasibility report to determine whether the company should advertise its custom-crafted cabinetry in the weekly neighborhood newspaper. Based on your primary...
-
An article in Sociological Methods & Research (May 2001) analyzed the data presented in the accompanying table. A sample of 262 Kansas pig farmers was classified according to their education level...
-
Assume a sample size of 50 for all procedures, regardless of your answers in Part I. For other procedures, assume that an adequate sample size for the circumstance was selected. The only exceptions...
-
Samsungs statement of cash flows in Appendix A reports the change in cash and equivalents for the year ended December 31, 2017. Identify the cash generated (or used) by operating activities, by...
-
On July 1, Chi Kong creates a petty cash fund with a balance of $300. During July, Elise Sautter, the fund custodian, signs the following petty cash tickets: Petty Cash Ticket Number 101 102 103...
-
The Lyon Corporation is a merchandising company. Prepare a short-term cash forecast for July of Year 6 following the format of Exhibit 9A.4. Selected financial data from Lyon Corporation as of July 1...
-
A simple gas of mass m with a number density n and temperature T is enclosed in a container. The gas escape into a vacuum through a small circular hole of area A in the container. If the wall...
-
Professor Balkaran wants to sell his Bugatti Car and advertises it in the local FIU newspaper at $1,370,000, giving his telephone number, one of his students at FIU sees the advertisement and rings...
-
What factors would promote the successful adoption of Agile PM methodologies like Scrum? (You may select more than one answer. Single click the box with the question mark to produce a check mark for...
-
What is the continuation or terminal value of a project?
-
How do you choose between mutually exclusive capital budgeting decisions?
-
What does the existence of a positive-alpha trading strategy imply?
-
State the efficient market hypothesis.
-
How can the profitability index be used to identify attractive projects when there are resource constraints?
-
Walmart buys each bottle of Tylenol for $3.00. Walmart sells 624 bottles through a store every year and demand is relatively constant throughout the year. Walmart can place orders for more Tylenol...
-
By referring to Figure 13.18, determine the mass of each of the following salts required to form a saturated solution in 250 g of water at 30 oC: (a) KClO3, (b) Pb(NO3)2, (c) Ce2(SO4)3.
-
What is the normal balance for each of the following accounts? (a) Purchases? (b) Sales Discounts? (c) Purchases Returns and Allowances? (d) Sales? (e) Purchases Discounts? (f) Sales Returns and...
-
Prepare entries in general journal form to record the following: June 4 Received payment from Parker Company of interest on a 45-day, 5.5 percent note for $ 8,700, dated April 20, and renewal of the...
-
How are the three financial statements shown in this chapter connected?
-
Restore the file Drone City Ch 12 (Backup) that you downloaded from the student companion site and then do the following like you did in the chapter: Create a new client in the Fixed Asset Manager...
-
When is the Fixed Asset Manager synchronized with the fixed asset list?
-
What information is provided by the Fixed Asset Managers Depreciation Schedule by G/L Account Number report?
Study smarter with the SolutionInn App