Cash budget graded practice problemthe owner of lazy inn

Reference no: EM13381128

Cash Budget Graded Practice Problem

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

Room	Qty	Jan-Mar & Dec. Price/Occupancy Rate	Apr-May Price/Occupancy Rate	June-Aug Price/Occupancy Rate	Sept.-Nov. Price/Occupancy Rate
1 king size bed/room	10	$58 / 85%	$40 / 50%	$58 / 75%	$45 / 55%
2 king size beds/room	60	$70 / 80%	$48 / 55%	$70 / 80%	$50 / 58%
2 bedroom (2 kings/room)	20	$80 / 78%	$60 / 40%	$80 / 88%	$64 / 60%
10 executive suites	10	$120 / 95%	$90 / 45%	$120 / 100%	$95 / 65%
On your cash budget, be sure to show each room type's cash flow individually (and total for all rooms by month)

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 - 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.

Reference no: EM13381128

Questions Cloud

Mega company has just signed a contract to export a machine : mega company has just signed a contract to export a machine to bestway enterprises an american corporation. the machine

1 you hold 10000 eib7 14022015interest rates are flat at 6 : 1. you hold 10000 eib7 14.02.2015.interest rates are flat at 6. calculate the price of the bond.2. cupid plc has a

Jetblue airways ipo valuation questions1 what are the : jetblue airways ipo valuation questions1. what are the advantages and disadvantages of going public?2. what different

Using theoretical and empirical evidence and relevant : using theoretical and empirical evidence and relevant examples critically discuss how the following factors would

Cash budget graded practice problemthe owner of lazy inn : cash budget graded practice problemthe owner of lazy inn has been requested by first national bank to submit a cash

Part 1 health plans may pay a physician a per member per : part 1 health plans may pay a physician a per member per month pmpm capitation payment for each person he or she is

Identify a company it may be your own identify the : identify a company. it may be your own. identify the corporate culture. is there a compelling case for culture change?

Table gives abbreviated balance sheets and income : table gives abbreviated balance sheets and income statements for estee lauder companies.tableend of

Here is a forecast of sales by national bromide for the : here is a forecast of sales by national bromide for the first four months of 2010 figures in

User Account

All Pages