Hi,

Can someone please solve this Financial Management questions? It is related to cash flow

You have been retained by a new 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 cash flow forecast for a bar (5 marks for second year cash flow). Listed below is the information the current operator gave you: 1) Beer sales are 50% of bar revenue 2) Wine sales are 25% of bar revenue 3) Liquor sales are 25% of bar revenue 4) Bar revenue is expected to be approximately 65% and food represents 35% of total revenue (this is an indication only and not to be taken into the calculations) 5) Cost of Sales: Wine is 35% Liquor is 20% 6) Beer is 25% Food is 30% - mostly bar snacks (pre-frozen and reheated except for wings Labour is 17% of revenue - includes wait staff and kitchen staff 7) Management Fees are $7,500 per month 8) 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. 9) Repairs and maintenance represents 3% of sales-30 day terms. 10) Cleaning and Sanitation represents 2% of sales - 30 day terms. 11) Smallwares and dish breakage represents 1% of sales- 30 day terms. 12) Insurance runs $300 per month -both business and liability insurance included in policy. 13) All food and beer are delivered, but the owner must shop for the wine and liquor - as a result he expenses his van to the bar at $500 per month including gas. 14) Depreciation and amortization are non-cash expenses and are not included in a cash flow. 15) The first year's profit is taxed and payable in the second year at a rate of 17% of net income. The company must make quarterly installments based on the previous three months income thereafter. 16) Credit card sales are 70% of the revenue (cost of credit card charges is 2% of sales) and the remainder is in cash. 17) The bar has no payment terms with any liquor or food supplier - it is all COD. 18) Telephone including cell phones and internet are $300 per month. 19) Utilities are $350 per month on average-including hydro, gas and water. . 20) The bar experiences a spike in revenue of 25% over the norm in December and a decline of 30% from the norm in January to March.) 21) The starting cash position is $5,000 and the business forecast begins in the month of October. . 22) Rent is $5,000 per month. 23) Other points to decide on through a cost/ benefit analysis (should the bar owner do either or both): 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. 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. 24) 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 loan of $60,000 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. 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? For questions 23 and 24 show a complete breakdown of your decision. Hints: 1 turn means a full seating in the restaurant - if it has 40 seats and all are occupied a least once during lunch then that is a full turn. You must calculate the revenue for food and bar separately in #8 first, then apply the percentages in #1,2,3 to divide up the bar revenue into liquor type and then finally apply the CGS percentages in #5 to calculate the actual cost of each product line. A monthly cash flow has to be done for two years to show the seasonality of the cash flows and in particular in the second year the impact of your decisions and the taxes (to be calculated by doing an annual income statement for the first year). The monthly cash flow (each column a month) should have revenue breakdown in the first rows and then all of the expenses in rows below that then calculate the net cash flow each month and the cumulative cash flow. The capital expenses and loan payments should also be included in the cash flow (but not in the income statement of course) -anything that affects the company bank account. As noted previously, you will have to pay taxes it the 2nd year as well