The owner of Lazy Inn has been requested by First National Bank to submit a cash

Question

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) 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 (25% of days1 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. 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.

Explanation / Answer

Note : - rooms cannot be partially occupied. Eg : - 10 rooms with 85% occupancy = 8.5. In scuh cases it has been rounded up , ie for case in exapmple 8.5 has been taken as 9)   Cash inflow calculation Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Cash Inflows 1 king size bed room 16182 146160 161820 6000 6200 13920 14384 14384 8370 8370 8100 16182 2 king size bed rooms 104160 94080 104160 47520 49104 100800 104160 104160 52500 54250 52500 104160 2 Bed rooms 39680 35840 39680 14400 14880 43200 44640 44640 23040 23808 23040 39680 10 executives suites 37200 33600 37200 13500 13950 36000 37200 37200 19950 20615 19950 37200 Total 197222 309680 342860 81420 84134 193920 200384 200384 103860 107043 103590 197222 Vending Machine 200 200 200 320 320 320 320 320 200 200 200 200 Conference Room 1200 1200 1200 1200 1200 1980 1980 1980 1200 1200 1200 1200 Total rooms revenue 115000 185000 198422 310880 344060 82620 85334 195900 202364 202364 105060 108243 104790 198422 Cash sales = 30% 34500 55500 59526.6 93264 103218 24786 25600.2 58770 60709.2 60709.2 31518 32472.9 31437 59526.6 Credit sales 80500 129500 138895.4 217616 240842 57834 59733.8 137130 141655 141655 73542 75770.1 73353 138895.4 cash collection from credit sales 40% in month of sales 32200 51800 55558.16 87046.4 96336.8 23133.6 23893.5 54852 56661.9 56661.9 29416.8 30308.04 29341.2 55558.16 25% one month later 20125 32375 34723.9 54404 60210.5 14458.5 14933.5 34282.5 35413.7 35413.7 18385.5 18942.525 18338.25 34723.85 0 35% two months later 28175 45325 48613.4 76165.6 84294.7 20241.9 20906.8 47995.5 49579.2 49579.18 25739.7 26519.54 25673.55 48613.39 cash collection from credit sales 116108.16 167095 199354 159510 122647 90027.4 111851 140071 114410 98272.72 74023.425 100415.9 60397.4 48613.39 Total collection from sales ( cash and credit sales as per condition provided 175634.76 260359 302572 184296 148247 148797 172560 200780 145928 130745.62 105460.43 159942.5 60397.4 48613.39 Cash Outflow calculation Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mortagage 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 Payroll expense 135000 135000 135000 135000 135000 150000 150000 150000 135000 135000 135000 135000 135000 135000 Insurance 9300 9300 9300 9300 Utilities 3900 3900 3900 2900 2900 2900 2900 2900 2900 2900 3900 3900 Professional service 1900 1900 1900 1900 1900 1900 1900 1900 1900 1900 1900 1900 Bonum to desk manager 400 400 400 400 Minimum cash balance Total cash outflow 152800 153200 162500 152200 151800 176100 166800 167200 161100 151800 152800 162100 147000 147000 Cash Budget statement - projected of Lazy Inc Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mar-13 Opening cash balance 16500 25000 118000 244000 262000 244000 203000 195000 214000 185000 150000 88000 Cash inflow ( Total sales ( cash and credit)+ vending machine 175834.76 260559 302772 184616 148567 149117 172880 201100 146128 130945.62 105660.43 160142.5 60397.4 48613.39 0 Cash outflow 152800 153200 162500 152200 151800 176100 166800 167200 161100 151800 152800 162100 Minimum cash balance 15000 15000 15000 15000 15000 15000 15000 15000 15000 15000 15000 15000 Closing cash 25000 118000 244000 262000 244000 203000 195000 214000 185000 150000 88000 72000 ( opening cash + revenue - outflowp- minimum cash balance) Closing cash of month becomes opeing cash of next month Note - If the figure of closing cash is negative it is a situation of external borrowing No condition of external borrowing Situation when June to aug rates are increased by 10% Occupancy reduced by 5 % Cash inflow calculation Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Cash Inflows 1 king size bed room 16182 146160 161820 6000 6200 13920 13844.6 13844.6 8370 8370 8100 16182 2 king size bed rooms 104160 94080 104160 47520 49104 100800 107415 107415 52500 54250 52500 104160 2 Bed rooms 39680 35840 39680 14400 14880 43200 46376 46376 23040 23808 23040 39680 10 executives suites 37200 33600 37200 13500 13950 36000 40920 40920 19950 20615 19950 37200 Total 197222 309680 342860 81420 84134 193920 208556 208556 103860 107043 103590 197222 Vending Machine 200 200 200 320 320 320 320 320 200 200 200 200 Conference Room 1200 1200 1200 1200 1200 1980 1980 1980 1200 1200 1200 1200 Total rooms revenue 115000 185000 198422 310880 344060 82620 85334 195900 210536 210536 105060 108243 104790 198422 Cash sales = 30% 34500 55500 59526.6 93264 103218 24786 25600.2 58770 63160.7 63160.7 31518 32472.9 31437 59526.6 Credit sales 80500 129500 138895.4 217616 240842 57834 59733.8 137130 147375 147375 73542 75770.1 73353 138895.4 cash collection from credit sales 40% in month of sales 32200 51800 55558.16 87046.4 96336.8 23133.6 23893.5 54852 58950 58950 29416.8 30308.04 29341.2 55558.16 25% one month later 20125 32375 34723.9 54404 60210.5 14458.5 14933.5 34282.5 36843.7 36843.7 18385.5 18942.525 18338.25 34723.85 0 35% two months later 28175 45325 48613.4 76165.6 84294.7 20241.9 20906.8 47995.5 51581.2 51581.222 25739.7 26519.54 25673.55 48613.39 cash collection from credit sales 116108.16 167095 199354 159510 122647 90027.4 114139 143789 117842 100274.76 74023.425 100415.9 60397.4 48613.39 Total collection from sales ( cash and credit sales as per condition provided 175634.76 260359 302572 184296 148247 148797 177300 206950 149360 132747.66 105460.43 159942.5 60397.4 48613.39 Cash Outflow calculation Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mortagage 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 12000 Payroll expense 135000 135000 135000 135000 135000 150000 150000 150000 135000 135000 135000 135000 135000 135000 Insurance 9300 9300 9300 9300 Utilities 3900 3900 3900 2900 2900 2900 2900 2900 2900 2900 3900 3900 Professional service 1900 1900 1900 1900 1900 1900 1900 1900 1900 1900 1900 1900 Bonum to desk manager 400 400 400 400 Minimum cash balance Total cash outflow 152800 153200 162500 152200 151800 176100 166800 167200 161100 151800 152800 162100 147000 147000 Cash Budget statement - projected of Lazy Inc Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mar-13 Opening cash balance 16500 25000 118000 244000 262000 244000 203000 199000 225000 199000 166000 104000 Cash inflow ( Total sales ( cash and credit)+ vending machine 175834.76 260559 302772 184616 148567 149117 177620 207270 149560 132947.66 105660.43 160142.5 60397.4 48613.39 0 Cash outflow 152800 153200 162500 152200 151800 176100 166800 167200 161100 151800 152800 162100 Minimum cash balance 15000 15000 15000 15000 15000 15000 15000 15000 15000 15000 15000 15000 Closing cash 25000 118000 244000 262000 244000 203000 199000 225000 199000 166000 104000 88000 ( opening cash + revenue - outflowp- minimum cash balance) Closing cash of month becomes opeing cash of next month Note - If the figure of closing cash is negative it is a situation of external borrowing No condition of external borrowing

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.