Finance-Fin 615 Cash Budget Graded Practice Problem

| January 30, 2017

Question
Fin 615
Cash Budget Graded Practice Problem – Chapter 3
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

2 bedroom (2 kings/room)
10 executive suites

Apr-May
Price/Occupancy
Rate

June-Aug
Price/Occupancy
Rate

Sept.-Nov.
Price/Occupancy Rate

10

$58 / 85%

$40 / 50%

$58 / 75%

$45 / 55%

60

$70 / 80%

$48 / 55%

$70 / 80%

$50 / 58%

20

$80 / 78%

$60 / 40%

$80 / 88%

$64 / 60%

10

$120 / 95%

$90 / 45%

$120 / 100%

$95 / 65%

yr

op

2 king size beds/room

Qty

C

1 king size bed/room

Jan-Mar & Dec.
Price/Occupancy
Rate

On your cash budget, be sure to show each room type’s cash flow individually (and total for all rooms by month)

ht
ig

ed

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

12
20

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.

1

Assume NO leap year

Get a 30 % discount on an order above $ 50
Use the following coupon code:
COCONUT
Order your essay today and save 30% with the discount code: COCONUTOrder Now
Positive SSL