Instruction:
You and your business partner have $50,000 to start a restaurant that is open for lunch only on Broad street in downtown Atlanta. However, your initial start-up cash is not enough and you need to get a loan from a bank. Banks require you to do a cash flow projection of how your business generates cash and how you can pay back the loan without delay or default. You need to decide (1) which bank you want to borrow from and (2) how much you want to borrow given that different banks have different loan structures (given your credit score) as following:
Bank A: Flat rate 6% per year (or 0.50% per month). The maximum loan is $300,000.
Bank B: 3% per year for the first 3 years (or 0.25% per month) and 9% flat rate afterwards (or 0.75% per month). The maximum loan is $300,000.
Bank A: Flat rate 4% per year (or 0.33% per month). The maximum loan is $150,000.
Assumptions:
You need to borrow from either bank A, B or C only.
Sales are subject to seasonal effect i.e. demand is high during fall and spring semesters and low during summer semester.
Tax rate is 20% on profit per year due every September. In the case of a loss, tax is 0% and your loss can be carried forward to deduct the tax amount up to 5 years.
Interest payment is tax deductible.
You can add as many assumptions as you need e.g. rent, wage, business hours etc.
Your responsibility:
Write a loan proposal report to the bank that you chose. It must include the following:
A detailed description of your cash flow/profit model that you build in Excel. You need to include a list of assumptions you made, a list of relevant variables, an influence diagram, a screenshot of your model in Excel, monthly cash flow and monthly profit forecast numbers, sensitivity analysis on your cash flows and profits, an equal monthly installment that you need to pay back to the bank including the interest payment, and tax calculation.
For inclusion of seasonal effect and time-series forecasting in your model (there's no need to do regression. Just write out equations).
For inclusion of relevant Monte Carlo simulation or probability distribution in your model.
For submitting your Excel spreadsheet.