Business Modelling
Learning Objectives:
• To gain expertise in employing a modelling process whilst solving a business problem,
• To employ modelling tools whilst creating a model,
• To design, build, test and analyse a spreadsheet model, and
• Generate high level insights about the business problem and present those insights in a businesslike manner in a written report.
•
Retirement planning case
Bob Davidson is a 46 year old warehouse supervisor in Melbourne. He has a daughter, Sue, age 6, and a wife, Margaret, age 40. Margaret is a potter, a vocation from which she earns no appreciable income.
Bob’s plan is to work until he turns 65. He is planning to save $100,000 in today’s dollars for his daughter’s university studies, which he expects her tocommence at age 18. Upon retirement, he and his wife would like to be able to travel extensively (he estimates $30,000 annual travel expenses as a nice target), although he would be able to live quite modestly otherwise (his current family expenses,including mortgage repayments and insurance premiums, are about $4,500 per month). He does not foresee moving from where he now lives.
Bob’s grandfather died at age 42, and his father died at age 58. Both died from cancer, although unrelated instances of that disease. Bob’s health has been excellent; he is an active runner and water skier. There are no inherited diseases in the family, with the exception of glaucoma. Bob’s most recent serum cholesterol count was 190.
Bob’s total salary package from his current employer is $116,000 per year, including the compulsory Superannuation Guarantee levy of 10 percent. He expects his salary to grow between 0 to 3 percent per year. His current personal tax rate is 35 percent.
In addition to his Superannuation Guarantee payments, Bob has suggested a salary sacrificeof approximately $9,500 per year to his superannuation fund. The current maximum amount allowed for effective salary sacrifice is $12,000 per year and this is expected to increase to $15,000 per year in five years’ time. After that time, the government has stated that the threshold will increase at the same rate as inflation. Note that any additional monies contributed over and above this amount will be considered undeducted contributions and will be sourced from Bob’s after-tax salary. He does not expect to receive any benefits from Centrelink when he retires.
Bob’s superannuation currently amounts to $167,000 and is invested in a 50% mixture of stocks and bonds that earn a long-term average annual return of 7 percent overall. In addition to his superannuation assets, Bob’s net worth consists of $200,000 equity in his home, $50,000 in short-term money market mutual funds (long-term average annual return of 3 percent), and $24,000 in a growth and income fund (long-term average annual return 6 percent) for his daughter’s university studies. Bob expects the future performance of all his investments to be consistent with historical returns. He has a term life insurance policy with a value of $600,000; this policy has no asset value but pays its face value as long as Bob continues to pay the premiums. He has no outstanding debts other than his mortgage ($200,000 principal balance outstanding with a 7 percent interest rate).
Bob is concerned about the impact of inflation which he expects to run at the long-term average rate of about 3 percent. He wants to plan under the assumption that all his primary future expenses (college, travel, etc.) will grow at this rate.
Should Bob die while insured, the proceeds on his life insurance go to his wife tax-free. Similarly, if he dies before retirement, his superannuation will go to his wife tax-free. Upon retirement, withdrawals from the funds are taxed as ordinary income.
Bob would like to know whether his current level of retirement savings is adequate.

Tasks
• Read the case and bring the answers to the following questions to the tutorial in week 2
o What form could a helpful response to Bob’s request take?
o What assumptions does that involve?
o What are some of the key variables involved?

Class activities:
• Whole class discussion of how to limit the focus of our modelling for our first model (week 2)
• Small group discussion (groups of 4) to develop an initial problem frame.Check with the lecturer your group’s problem definition before continuing (due week 3)
• Small group discussion of the key variables (decision variables and parameters)
• Generate a list of elements that you could add for the next version of the model. Consider the constraints of the initial model and alternative outcome measures.
• Individually diagram the problem (due week 4)

Workshop activities:
• Design and build an initial model
• Use your initial model to analyse the case. Write out explanations for two insights (at a minimum)

Assignment Deliverables
1. Spreadsheet model emailed to lecturer. The model should be a working model developed using the techniques described in Modelling for Insight. It will include results capture and analysis for generating insights.
2. Report to the model sponsor appropriately presenting the model results and high level insights noting the assumptions and limitations inherent in the model.