Write a Summary on How You would Improve the Model Review and Audit Process in Your Organisation

$100.00

How are they informally and formally reviewed?

 

SKU: Fin1002939

Learning outcomes (LO) mapping

1. Explain the concept and principles of financial modelling and the skill set required to become a financial modeller.

2. Use Excel to effectively model business problems and extract essential data from spreadsheets.

3. Illustrate best practice modelling techniques by applying strategies for user-friendly design, consistency, error reduction and data security.

4.Effectively summarise and communicate the assumptions, implications and results of spreadsheet models.

5. Interpret and audit model results in the context of the business situation and address the inherent assumptions through stress testing.

 

Question 1 

Consider the models used in your organisation. How are they stress tested? How are they informally and formally reviewed? Imagine you are communicating to the risk management team. Write a summary on how you would improve the model review and audit process in your organisation.

 

Question 2 

In Assignment 1, you set up a spreadsheet for Ray who was taking out a loan to purchase a car on his twenty-first birthday on 1 September 2014. He has found one that he likes for $27,500. Ray will have a $6000 deposit by his birthday, leaving a loan principal of $21,500. He hopes to negotiate the loan based on an annual interest of 7.4%, with monthly payments over eight years, however, there is a chance that he may be able to afford to pay off the loan in five years. The bank charges $5.00 per month service fee.

Show Ray how his monthly loan payments will vary under different scenarios: loan term of 5, 6, 7 and 8 years and interest rates of 7%, 7.4% and 7.8%.

Use your existing spreadsheet solution from Assignment 1 or the Excel template provided (FIN280_AS2_v1_template.xls — released in Week 8) to conduct further analysis to support Ray’s financing decision. Please note that the template provided is simply a base from which to work and you will be required to build on this, or your Assignment 1 spreadsheet, substantially.

Calculations

• Overall accuracy of the calculations

Spreadsheet structure (see Criteria-based Marking Guide for more details on spreadsheet criteria)

• formulas

• titles, labels, headings

• use of Excel tools

• professional presentation of spreadsheet, including appropriate formatting

• spreadsheet content.

 

Question 3 

In Assignment 1, you set up a spreadsheet for Ausco, a company considering an investment in their retail business which will improve their annual cash flow stream by $19,600 in Year 1, $15,400 in Year 2, $14,000 in Year 3, $10,800 in Year 4, $7800 in Year 5, $4800 in Year 6 and $2300 in Year 7. The outlay for this investment is $51,342. The analysis to date also shows that a further $5000 will be required to be paid in Year 3 of this project for maintenance expenditure. The appropriate cost of capital is 7.6%.

(a) To which variable is the project most sensitive? Use a graph to communicate your answer.

 

(b) Conduct a scenario analysis where the existing analysis forms the base (and most likely) case. In the best-case scenario, cash flows increase by 10%, the cost of the project falls by $2000, the maintenance expenditure falls by $400 and the cost of capital falls to 7%. In the worst-case scenario, cash flows decrease by 10%, the cost of the project increases by $3000, the maintenance expenditure increases by $600 and the cost of capital rises to 8%.

 

Use your existing spreadsheet solution from Assignment 1 or the Excel template provided (FIN280_AS2_v1_template.xls — released in Week 8) to conduct this further analysis. Please note that the template provided is simply a base from which to work and you will be required to build on this, or your Assignment 1 spreadsheet, substantially.

(a) Calculations and Charting

• Overall conclusions of sensitivity analysis and techniques utilised (e.g. graph)

(b) Calculations

• Overall accuracy of the calculations

Spreadsheet structure (see Criteria-based Marking Guide for more details on spreadsheet criteria)

• formulas

• titles, labels, headings

• use of Excel tools

• professional presentation of spreadsheet, including appropriate formatting

• spreadsheet content.

 

Question 4 

Your friend Sue has decided to start a new home business making baskets. Her estimated fixed costs are $40,000; raw materials per basket of $15, labour per basket of $20 and overheads of $2 per basket. Sue is hoping to sell the baskets for $65 each. Prepare a break-even analysis for Sue, including a graph indicating the break-even point.

 

Calculations

• Overall accuracy of the calculations

Spreadsheet structure (see Criteria-based Marking Guide for more details on spreadsheet criteria)

• formulas

• charting

• titles, labels, headings

• use of Excel tools

• professional presentation of spreadsheet, including appropriate formatting

• spreadsheet content.

 

Question 5 

Choose data of your choice from your own organisation, hobby or home to demonstrate your charting skills. You must use a minimum of five (5) data categories on the x-axis.

(a) Create a chart with two different axes and chart types.

(b) Prepare an explanatory paragraph for your chart as if you were going to be presenting the results to an audience.

 

Download (XLSX, 34KB)

 

Download (XLSX, 29KB)

Reviews

There are no reviews yet.

Be the first to review “Write a Summary on How You would Improve the Model Review and Audit Process in Your Organisation”

Your email address will not be published. Required fields are marked *

Sorry no more offers available