A spreadsheet-based decision model

Assignment Details:

Three years ago, Emma Thompson purchased a cafe in the Melbourne Botanical Gardens, and now wants you to develop a spreadsheet-based decision model that can be used to investigate and explore decisions and risks relating to taking a small business loan (e.g., for investment). The model needs to be generic enough to enable Emma to explore the size of the loan that is viable/manageable within different scenarios of income, cost, expenses, loan amount, deposit made on the loan, repayment amount, other financial commitments, the amount of interest paid etc. and understand the risks associated with meeting the loan commitments.

The decision model must be realistic and easy to use. The level of complexity modelled, for example the choice of deterministic vs. stochastic inputs, input distributions, etc., is left to your discretion. However, the model must enable the user to input the following business cost/expenses and loan details/options.

Deterministic/Fixed inputs:

Utilities (Electricity Gas Water)

• Telephone/mobile and Internet
• Insurance
• Maintenance
• Paid salaries
• Own wage
• Leasing
• Utilities (Electricity Gas Water)

No data is provided. You are required to create a fictitious business and demonstrate the utility of the decision model using real data where available (e.g., interest rates) and create data where it is not.

Your model needs to take into account the costs of running the business (e.g., coffee supplies) 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 Emma taking the loan under various scenarios of your choosing.

Note that Net Profit = Sales revenue Total variable costs Fixed costs Overheads.

You can also assume the following:

• weekly coffee supplies has a Normal distribution with a mean $864 and a standard deviation $29
• Sales revenue for any week = $3,624.3+8.76*Coffee supplies for that same week + Error term, where the Error term is Normally distributed with mean zero and standard deviation $253.5

For each of the remaining stochastic inputs of your choice, you will need to determine appropriate distributions with parameter values and support your answers with goodness-of-fit tests for all stochastic inputs.

The minimum requirements of the decision model are:

1. Ability to enter loan details, income, costs, and expenses to explore decision options relating to the business loan such as amount and repayments to calculate outputs for example total interest paid.
2. 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. Base the interest rate on the published RBA rates. 
3. Ability to calculate outputs such as whether Emma is in default of the loan agreement (i.e., whether she has sufficient funds to cover a repayment) and the amount of repayment outstanding (which will be zero if she can have sufficient funds to cover the repayment).
4. Stochastic treatment of some of the inputs, so that the resulting simulated output can be explored.5.It must also enable Emma to explore and understand the risks associated with the decision.