Fin 615Cash Budget Graded Practice Problem – Chapter 3The owner of Lazy Inn has been requested by First National Bank to submit a cash budget for the next calendaryear. 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 occupancywould go down by 5 percentage points if the rates were raised. What would the impact on cumulativeborrowing be if these rates did change? (use scenario manager)Cash InflowsRoom2 bedroom (2 kings/room)10 executive suitesApr-MayPrice/OccupancyRateJune-AugPrice/OccupancyRateSept.-Nov.Price/Occupancy Rate10$58 / 85%$40 / 50%$58 / 75%$45 / 55`$70 / 80%$48 / 55%$70 / 80%$50 / 58 $80 / 78%$60 / 40%$80 / 88%$64 / 60 $120 / 95%$90 / 45%$120 / 100%$95 / 65%yrop2 king size beds/roomQtyC1 king size bed/roomJan-Mar & Dec.Price/OccupancyRateOn your cash budget be sure to show each room type’s cash flow individually (and total for all rooms by month)htigedVending machines: $200/monthly -> Jan-Mar Sept.- Dec.; $320/monthly (Apr – Aug) – Revenue is collected in themonth of sale.Conference Room rental rate = $150 daily (Jan – May Sept – Dec); $180 daily (June – Aug.) occupancy rate (25% of1days 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 arecollected in the month of sale while 25% are collected one month later; the balance two months later. Total roomrevenue for November and December of 2011 was 115 000 and 185 000 respectively.1220Cash OutflowsMortgage ($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 000for June – Aug.)You want to maintain at least $15 000 in your bank account. Any balance less than $15 000 you need to borrow fromyour line of credit.What is the highest cumulative amount that needs to be borrowed during this 12 month period? Use the roundup functionto 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 anynumbers in formulas. Results should be to the nearest dollar.1Assume NO leap year
