# 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