Three years ago Jane Porter purchased a coffee and cake shop in the Melbourne Botanical Gardens, and
now wants you to develop a spreadsheet-based decision model that can be used to explore decisions and
risks relating to taking a small business loan.
She has provided you with historical financial data for her shop, covering costs and sales revenue each
week over three years – see Excel spreadsheet MIS275_Assignment_2. This spreadsheet also includes a
template of the model for you to complete. The model should allow Jane to input the size of the loan, the
interest rate, the term of the loan, and the percentage of net profit that she will set aside each week for
meeting the loan repayments.
Regarding the loan, you are to assume the following:
? The interest rate is an annual rate which is fixed for the term of the loan.
? The term of the loan is limited to one, two, or three years.
? Equal loan repayments are made every four weeks, covering both principal and interest. The
repayments must be met immediately following the end of each four week period. Otherwise Jane
will be deemed in default of the loan agreement.
? There is no option for making extra repayments on the loan.
? Jane plans to set aside a fixed percentage of her profit in each four-week period, and use that
money to cover the loan repayment due at the end of each period. After making each repayment,
any money left over is deposited in a savings account and cannot be accessed to meet any future
shortfall in loan repayments.
Your model needs to take into account the costs of running the business and the sales revenue in order to
determine the profit generated in each four-week period. You will then use the model to explore the risks
associated with Jane taking the loan under various scenarios of your choosing.
The minimum requirements of the decision model are:
1. Ability to explore decision options relating to the size of the loan, the interest rate, the loan
term, and the percentage of profit to be set aside each week for repaying the loan.
2. Ability to calculate outputs such as whether Jane is in default of the loan agreement (i.e.
whether she have sufficient funds to cover a repayment) and the amount of repayment
outstanding (which will be zero if she can has sufficient funds to cover the repayment).
3. Stochastic treatment of random inputs to explore resulting simulated output and summarise