The owner of Lazy Inn has been requested by First National Bank to submit a cash...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
The owner of Lazy Inn has been requested by First National Bank to submit a cash budget for the next calendar year. With that in mind, please prepare the following: a 12-month projected cash budget statement for 2012 (assume that your beginning cash (Jan 1) is $16,500). After preparing the above schedule, can you provide the owner with some guidance as to whether: • to raise Lazy Inn's summer (June-Aug) rates by 10% across the board. The owner's guess is that occupancy would go down by 5 percentage points if the rates were raised. What would the impact on cumulative borrowing be if these rates did change? (use scenario manager) Cash Inflows Apr-May Sept.-Nov. Jan-Mar & Dec. Price/Occupancy Price/Occupancy Price/Occupancy Price/Occupancy Rate Rate June-Aug Room Qty Rate Rate $58 / 85% $40 / 50% $48 / 55% $60 / 40% $90 / 45% $58 / 75% $70 / 80% $80 / 88% $120 / 100% 1 king size bed/room 10 $45 / 55% 2 king size beds/room 60 $70 / 80% $50 / 58% $80 / 78% $120 / 95% On your cash budget, be sure to show each room type's cash flow individually (and total for all rooms by month) 2 bedroom (2 kings/room) 20 $64 / 60% 10 executive suites 10 $95 / 65% Vending machines: $200/monthly -> Jan-Mar, Sept.- Dec.; $320/monthly (Apr - Aug) – Revenue is collected in the month of sale. Conference Room rental rate = $150 daily (Jan - May, Sept - Dec); $180 daily (June - Aug.) occupancy rate (25% of days' in Jan - May, Sept - Dec; 35% of the days in June - Aug) -- Revenue is collected in the month of the rental. For the room revenue, 30% are cash sales; 70% are credit sales. Of the credit sales, 40% of the credit sales are collected in the month of sale while 25% are collected one month later; the balance two months later. Total room revenue for November and December of 2011 was 115,000 and 185,000, respectively. Cash Outflows Mortgage ($12,000/monthly) Payroll expenses (Jan. - May, Sept - Dec. = $135,000/monthly; June - Aug. = $150,000/monthly) Insurance (quarterly payments of $9,300 on March 1, June 1, Sept. 1, and Dec. 1) Utilities (Jan - Mar, Nov. - Dec. = $3,900/month; Apr. - Oct = $2,900/month) Professional Services ($1,900/month) Bonus to Desk Manager ($400/monthly if monthly room revenues > $180,000 for Jan - May, Sept. - Dec.; > $195,000 for June - Aug.) You want to maintain at least $15,000 in your bank account. Any balance less than $15,000 you need to borrow from your line of credit. What is the highest cumulative amount that needs to be borrowed during this 12 month period? Use the roundup function to round this amount to the highest thousand dollar amount (e.g. 12,400 would become 13,000). As usual, use only functions and formulas along with proper formatting in preparing this cash budget. DO NOT have any numbers in formulas. Results should be to the nearest dollar. 202 The owner of Lazy Inn has been requested by First National Bank to submit a cash budget for the next calendar year. With that in mind, please prepare the following: a 12-month projected cash budget statement for 2012 (assume that your beginning cash (Jan 1) is $16,500). After preparing the above schedule, can you provide the owner with some guidance as to whether: • to raise Lazy Inn's summer (June-Aug) rates by 10% across the board. The owner's guess is that occupancy would go down by 5 percentage points if the rates were raised. What would the impact on cumulative borrowing be if these rates did change? (use scenario manager) Cash Inflows Apr-May Sept.-Nov. Jan-Mar & Dec. Price/Occupancy Price/Occupancy Price/Occupancy Price/Occupancy Rate Rate June-Aug Room Qty Rate Rate $58 / 85% $40 / 50% $48 / 55% $60 / 40% $90 / 45% $58 / 75% $70 / 80% $80 / 88% $120 / 100% 1 king size bed/room 10 $45 / 55% 2 king size beds/room 60 $70 / 80% $50 / 58% $80 / 78% $120 / 95% On your cash budget, be sure to show each room type's cash flow individually (and total for all rooms by month) 2 bedroom (2 kings/room) 20 $64 / 60% 10 executive suites 10 $95 / 65% Vending machines: $200/monthly -> Jan-Mar, Sept.- Dec.; $320/monthly (Apr - Aug) – Revenue is collected in the month of sale. Conference Room rental rate = $150 daily (Jan - May, Sept - Dec); $180 daily (June - Aug.) occupancy rate (25% of days' in Jan - May, Sept - Dec; 35% of the days in June - Aug) -- Revenue is collected in the month of the rental. For the room revenue, 30% are cash sales; 70% are credit sales. Of the credit sales, 40% of the credit sales are collected in the month of sale while 25% are collected one month later; the balance two months later. Total room revenue for November and December of 2011 was 115,000 and 185,000, respectively. Cash Outflows Mortgage ($12,000/monthly) Payroll expenses (Jan. - May, Sept - Dec. = $135,000/monthly; June - Aug. = $150,000/monthly) Insurance (quarterly payments of $9,300 on March 1, June 1, Sept. 1, and Dec. 1) Utilities (Jan - Mar, Nov. - Dec. = $3,900/month; Apr. - Oct = $2,900/month) Professional Services ($1,900/month) Bonus to Desk Manager ($400/monthly if monthly room revenues > $180,000 for Jan - May, Sept. - Dec.; > $195,000 for June - Aug.) You want to maintain at least $15,000 in your bank account. Any balance less than $15,000 you need to borrow from your line of credit. What is the highest cumulative amount that needs to be borrowed during this 12 month period? Use the roundup function to round this amount to the highest thousand dollar amount (e.g. 12,400 would become 13,000). As usual, use only functions and formulas along with proper formatting in preparing this cash budget. DO NOT have any numbers in formulas. Results should be to the nearest dollar. 202
Expert Answer:
Answer rating: 100% (QA)
Use spreadsheet to prepare the budget Enter values and formulas in the spreadsheet as shown in the i... View the full answer
Related Book For
Posted Date:
Students also viewed these accounting questions
-
a. Please make a cash budget for the months of January, February and March 1999 based on the data for: (5 Marks) View Receivable Trend: 30% of Sales are collected in the month of sale 30% of Sales...
-
You have been asked to prepare a cash budget for Whitborrow plc for the next three months, October, November and December. The managers are concerned that they may not have sufficient cash to pay for...
-
Great Lake Corporation wishes to prepare a cash budget for November 2014. Sales, purchases, and expenses for October (actual) and November and December (estimated) are as follows: Sales: All sales...
-
At the beginning of the year, COMET Company decided to raise additional capital by issuing 8,000 of 1,000 face amount 5-year bonds with interest rate of 12% payable semi-annually on June 30 and...
-
A Government of Canada bond will pay $50 at the end of every six months for the next 15 years, and an additional $1000 lump payment at the end of the 15 years. What is the appropriate price to pay if...
-
The table shows the mean heights and standard deviations for a population of men and a population of women. Compare the z-scores for a 6-foot-tall man and a 6-foot-tall woman. Assume the...
-
Marijuana Use The percent of college students marijuana use for a sample of 95,761 students is shown in the pie chart. Find the probability of each event. (a) Randomly selecting a student who never...
-
Emily Watkins, a recent college graduate, faces some tough choices. Emily must decide whether to accept an offer for a job that pays $35,000 or hold out for another job that pays $45,000 a year....
-
Match each definition with its related term by selecting the appropriate term in the dropdown provided. 1. Actual Accounting System Term 2. Direct Labor Efficiency Variance 3. Direct Labor Rate...
-
A centrifugal pump delivers water through a 40-cm-diameter, 1,000-m-long commercial steel pipe from reservoir A to B with EA = 920.5 m and 8 = 935.5 m. Friction losses vary according to the Moody...
-
Adrian, a fully qualified specialized registered nurse, is deaf. He relies upon an American Sign Language (ASL) interpreter to communicate with hearing individuals in the workplace. Adrian applied...
-
Your customer is hiring a vehicle as follows: Hyundai Elantra or similar - Automatic Pick up: 1900 10 April Drop off: 1900 15 April They would like to reduce their excess by paying the Collision...
-
20. What will be the output of the following code? int number = 11; if (number > 0) cout < < "C++" else cout < < "Soccer"; cout < < "is"; cout < < "fun" < < endl;
-
The following are the dividends and earnings of Killmarks Ltd over the last five (5) years. Year Dividends Earnings 000 000 2001 150,000 400,000 2002 192,000 510,000 2003 206,000 550,000 2004 245,000...
-
Your United States Senator has called you to ask for jurisprudential help. They are proposing a new bill, "The Good Acts Bill," which would require persons in the state to render aid to persons in...
-
a) We are looking at the same Buck Run region again i. i. II. (2pt) How far away are the two red dots separated in the real world? What is the average gradient (in feet per meter) of the river when...
-
How do emerging technologies, such as super-resolution microscopy, single-cell imaging, and organelle proteomics, contribute to our understanding of organelle structure, function, and dynamics at a...
-
For the next several days, take notes on your listening performance during at least a half-dozen situations in class, during social activities, and at work, if applicable. Referring to the traits of...
-
Extracted data from Penguin plc's last accounts are as follows: A major supplier to Penguin offers a discount of 2 per cent on all future supplies if payment is made on the seventh day following...
-
Hose plc is trying to make a decision on whether to make a commitment of 800,000 now to a project with a life of seven years. At present prices the project will return net cash flows of 150,000 per...
-
An investor has £100,000 to invest in shares of Trent or Severn the expected returns and standard deviations of which are as follows. The correlation coefficient between those two shares is...
-
Natural gas (methane) flows from a 3-in.-diameter gas main, through a 1-in.-diameter pipe, and into the burner of a furnace at a rate of \(100 \mathrm{ft}^{3} /\) hour. Determine the pressure in the...
-
SAE 30 oil at \(60^{\circ} \mathrm{F}\) flows through a 2-in.-diameter pipe with a mean velocity of \(5 \mathrm{ft} / \mathrm{s}\). Determine the value of the Reynolds number (see Example 1.4)....
-
Calculate the Reynolds numbers for the flow of water and for air through a 4-mm-diameter tube, if the mean velocity is \(3 \mathrm{~m} / \mathrm{s}\) and the temperature is \(30^{\circ} \mathrm{C}\)...
Study smarter with the SolutionInn App