You have been retained by a new restaurant/bar owner to assist him in making a few financial decisions. You explain to him the need to do a cash flow so that the decisions are well thought out and therefore you need to complete a 2-year monthly cash flow forecast for a bar (5 marks for second year cash flow). The cash flow must be structured to show Cash Receipts, Less Cash Expenses equals Net Cash Flow. The cumulative cash position must also be shown. Listed below is the information the current operator gave you:

Beer sales are 50% of bar revenue (2 marks)

Wine sales are 25% of bar revenue (2 marks)

Liquor sales are 25% of bar revenue (2 marks)

The starting cash position is $5,000 and the business forecast begins in the month of October with September 30 being the year end. (1 mark)

Cost of Sales: beer is 25% (2 marks)

Wine is 35% (2 marks)

Liquor is 20% (2 marks)

Food is 30% - mostly bar snacks (pre-frozen and reheated except for wings (2 marks)

Labour is 17% of revenue ? includes wait staff and kitchen staff (1 mark)

Management Fees are $7,500 per month. (1 mark)

Revenue is based on a 40-seat bar with 20 seats on the patio (only open from June to September each year inclusive). They expect 1 turn in seating at lunch and 1.5 turns in the afternoon (mainly after work crowd) and then a half turning the evening. The average patron spends $8.00 on the bar and $10.00 on food at lunch and the bar tab doubles with no food sold to the after work crowd and then food sales double in the evening and the bar tab stays at $13.00 on average. The bar is open 7 days a week including all holidays and expect the above numbers to be averages over the week. Be sure to note in your calculations the number of days in each month. ((13 marks)

Repairs and maintenance represents 3% of sales-Owner will pay in 30 days meeting terms. (2 marks)

Advertising and promotion can increase bar revenue (not including food) by 10% with bar promotions ? some are funded by the breweries, but the bar owner estimates the out-of-pocket expenses at $300 on average. (4 marks)

The bar owner has an option to buy some outdoor heaters for $8,000, which will give you and extra month on the patio ? the company selling them to you will give him three months to pay for them and they will be expensed in the current year. (4 marks)

23) The bar owner has been offered an opportunity by the landlord to expand into the space next door at a rent of $2,500 per month, which could provide additional seating, expanding the bar by 40%. He will need to take out a $60,000 loan from the bank at 8% per annum to pay for the expansion. The loan requires a principle payment of $5,000 each month and interest is payable in monthly installments on the outstanding balance each month to be paid out in the first year. Be sure to include the incremental revenue and expenses with the loan payments in making your decision. Should you go ahead with the expansion? Is the bar going to realize sufficient incremental profit over the cost of the loan to proceed with the expansion? (9 marks)

Do all of the assignment in Excel ? put one year on each spreadsheet. For questions 22 and 23 show a complete breakdown of your decision.