QUANTITATIVE METHODS FOR RISK ANALYSIS
This assignment comprises 20% of the assessment in ETX2011 and 20% in ETX9520.
Th. Submit it to your tutor in your tutorial (or to your tutor’s mailbox, 5th floor H Block).
* submit a printed “hard copy” of your assignment to your tutor.
* upload your assignment to Moodle (to prove that it was submitted, and it was on time).
Go to the “Assignments” tab. Click on the “Assignment 2” link to upload. When you’ve
done so, the following message will appear momentarily, “File uploaded successfully.”
[To later confirm your upload was successful, go to the “Assignments” tab and click
on the “Final Assignment” uploading link. The uploaded file’s name should appear.]
• Retain your marked assignment work until after the publication of final results for this unit.
This is an individual, NOT a syndicate, assignment. On the Assignment Cover Sheet, read the references to
plagiarism and collusion from Monash Statute 4.2 – Discipline.
• This assignment is based on the relevant unit material (lectures, tutorial exercises, prescribed reading from AWZ, prescribed Problems from AWZ, etc). We expect that students who have not revised such material before attempting the assignment will obtain VERY POOR marks (but, at least, they will know why they obtained poor marks).
• Check your work to ensure that
o you completed all tasks and answered the questions which were asked.
o you are succinct and to the point. Marks are awarded for correctness, not for the amount written.
o any software (eg @Risk, PrecisionTree) has carried out the tasks that you require.
o graphs, tables, trees, etc are appropriately and informatively labelled, with chart titles, axis titles, legends, etc – as appropriate. Print relevant graphs, tables, etc.
o you have followed any prescribed templates for reporting.
• Do not submit your assignment in a folder – stapled A4 pages are much easier for the marker.
• Save trees! Double-sided printing is encouraged.
• In business, a high standard of presentation is usually required. In this unit, your submitted work is expected to be legible, to be well laid out and to reveal use of a spelling checker. However, we are more interested in correct content than in your abilities in desk-top publishing.
For example,
o if you are having trouble embedding graphs or tables in your document, just print them separately and include them as appendices (appropriately labelled).
o if you lack the Excel skills to do a task, then do it “by hand”.
(We would rather see a hand-written alteration than an error.)
o if you want coloured output, but don’t have a colour printer, you may use coloured pencils/pens on your graph.
• Most calculation, tabulation and graphing tasks are done with little effort (for you) by the computer. Your role is to ensure that the output is presented appropriately and meaningfully to a reader.
• Keep a backup copy of all assignments until results are finalised at the end of semester.
________________________________________
QUESTION 1
RocketAuto Corporation (RAC) is considering marketing, at the start of next year, an anti-theft device for motor vehicles. Based on GPS technology, the device would be supplied free to customers. Customers would then have to pay an annual subscription for RocketAuto’s monitoring services. RocketAuto wants to estimate the accumulated profit at the end of the next 5 years.
Assume that
• there are 610,000 vehicles which could potentially use the device at the start of next year, and that this figure will grow each year.
• the annual percentage growth in the number of vehicles which could potentially use the device is Normally distributed with a mean of 5.4% and a standard deviation of 1.1%.
• the proportion (market share) of the 610,000 vehicles that RocketAuto will have as subscribers next year (ie, the first year) is modelled by the Triangular distribution, with a minimum of 25%, a most likely value of 40% and a maximum of 75%. If no competitors enter the market, RocketAuto expect to retain that market share.
• there are 3 potential competitors.
o At the start of each year (including the first year), there is a 40% chance that any competitor who has not yet entered the market will enter during that year.
o The year after it enters the market, a competitor will have impact on RocketAuto’s market share.
Any competitor entering the market will capture 20% of the market share that RocketAuto had prior to that competitor’s entry. eg, if RocketAuto had 60% market share, the entry of
* 1 competitor would reduce that 60% share to 60(1 – 0.2) = 48%;
* 2 competitors in the same year would reduce that 60% share to 60(1 – 2*0.2) = 36%.
o Once a competitor has entered the market, that competitor will remain there.
o If all 3 competitors have entered the market, no more competitors will enter.
o The competitors behave independently.
• an annual subscription yields $220 revenue to RocketAuto.
• a subscriber costs RocketAuto $40 per year to service (including provision of the device).
(a) (i) Use simulation, with 1,000 iterations, to estimate the accumulated profit after 5 years for
RocketAuto. (You may ignore the use of Net Present Value.)
(ii) Provide a histogram and summary statistics for each of your output variable(s).
WARNING
DO NOT print out the 1,000 iterations from the “Simulation Data” results.
IMPORTANT
For your simulation(s), your mark will depend on the formulas you have used.
Therefore, you MUST display your formulas (Excel and @Risk) AND you MUST also display the row and column headings (see the Excel Appendix below) so that your formulas can be interpreted. Marks WILL be deducted if you neglect to do this.
• Do not print the “Simulation Data” results from @Risk. It is unnecessary.
Do not submit any Excel files. Provide a printout of any tables or graphs in your Word
document.
• See the Excel Appendix and the @Risk Appendix at the end of this document for some operating hints and advice on presentation and display.
• In the @Risk Simulation Settings, you must select “Choose Randomly.” If you fail to do so, you run the risk of being accused of plagiarism because all simulation results based on “Fixed 1” will be identical. (“Latin Hypercube” and “Mersenne Twister” ARE the settings to use.)
DO NOT print out the 1,000 iterations from the “Simulation Data” results.
(b) From your simulation, report your findings on the estimated accumulated profit(loss) after 5 years for RocketAuto. Your responses do not need to be set out as a formal report, but should be succinct and directed to an imagined target audience of colleagues with a similar level of statistical knowledge to yours. Substantiate your findings with suitable figures/tables/graphs – as appropriate. Explain any additional assumptions that you have made.
QUESTION 2
The Government announced that it will award an $850,000 research grant for the best proposal for using wireless communication to enhance safety in rail transportation. Commex is considering applying for this grant. Commex estimates that it would cost $50,000 to prepare the grant proposal and that it has a 50-50 chance of being awarded the grant.
If awarded the grant, Commex must choose whether to use microwave, cellular or infrared technology. Depending on which technology is to be used, Commex would need to buy some new equipment. The estimated equipment costs for microwave, cellular and infrared technology are $40,000, $50,000 and $40,000, respectively.
In addition to equipment costs for the chosen technology, there will also be R&D (research and development) costs. However, Commex doesn’t know exactly what the R&D costs will be. So, for simplicity, Commex has estimated the best case and worst case R&D costs, with associated probabilities – as shown in Exhibit 2.1.
Exhibit 2.1 Best Case Worst Case
R&D Costs Cost Probability Cost Probability
Microwave $300,000 0.4 $600,000 0.6
Cellular $400,000 0.8 $700,000 0.2
Infrared $400,000 0.9 $800,000 0.1
(a) (i) In Excel, construct a table of input values for this problem. Provide a printout.
(ii) Construct a well-labelled decision tree for this problem. Provide a printout.
(Do not collapse any branches.)
(iii) On the basis of your decision tree analysis, what decision(s) should Commex make?
Give reason(s) for your answer.
(b) Perform sensitivity analysis on the optimal decision making by
(i) varying the probability of being awarded the research grant.
Report your findings and provide evidence for your statements.
(ii) varying the research grant amount.
Report your findings and provide evidence for your statements.
(iii) simultaneously varying
• the probability of receiving the grant and
• the research grant amount.
Report your findings and provide evidence for your statements.
QUESTION 3
A machine fills 40kg bags of cement. Every hour, a subsample of 4 bags is taken and these bags are weighed. The spreadsheet cement.xls shows the results of 25 consecutive subsamples.
(a) Construct well-labelled control chart(s) to monitor the performance of the filling machine.
Provide printout(s) of your chart(s).
(b) For your chart(s), list/describe all occurrences of the following. [If there are no occurrences,
then clearly state that fact.]
(i) Out of Control points. (ii) Up/Down Runs.
(iii) Above/Below Runs. (iv) Zone A Test violations.
(v) Zone B Test violations.
That is, make statements such as:
• “the chart has an Up/Down Run from subsample 1 to subsample 9”.
• “there are no Zone B violations”.
• etc.
(c) On the basis of the data, describe how the filling process for the cement bags has
performed. State relevant evidence for your conclusions.
Excel Appendix (Excel 2010)
Cell comments
A cell with a red triangle in the upper right-hand corner contains a comment. This comment may be read by placing the cursor on that cell.
To Copy a selected part of your spreadsheet and, eg, to Paste it into a Word document
• Select the required cells and click on the ‘Copy’ icon.
• Go to Word and Paste the selection by clicking on the downwards triangle of the Paste icon. Select Paste Special/ Picture (Enhanced Metafile).
To arrange graphs, pictures and text within a Word document – a hint
These can be more manageable if you insert a table into Word and place your graphs, pictures and text into cells of this table.
To alter the page margins, in order to optimise the use of a page before printing it
Select Office Button/Print/Print Preview/Show Margins and drag the “handles” at the edges of the page.
To print in “Landscape” view, rather than in “Portrait” view
Select Page Layout/Orientation/Landscape before printing.
To print the gridlines which border the spreadsheet cells
Select File/Page Setup/Sheet/Gridlines (Page Layout/Gridlines/Print in Excel 2007)
To print the Row (“1”, “2”, “3”, etc) and Column (“A”, “B”, “C”, etc) headings
Select File/Page Setup/Sheet/Row and Column Headings (Page Layout/Gridlines/Print Excel 2007)
To Copy a part of your spreadsheet, eg to Paste it into a Word document, first select the required cells.
• In Excel 2010, select the Copy command. Go to the Word document and select the triangle under the Paste command and select Paste Special/Picture(Enhanced Metafile).
An alternative to the procedures above is to press the “Print Scrn” key when your spreadsheet is displayed. This captures an image of your screen, which may then be Pasted into a Word document. Use Word’s “Crop” tool to reduce the Pasted image to your desired selection.
If you are unable to make Microsoft Office 2010 display the row and column headings clearly on your printout, then write them on by hand.
Word Tips
To capture a screen image & Paste it into Word
To capture the image (any image, eg, an Excel page, an internet page, a photo, etc) currently displayed on your screen,
• press the Print Scrn button on your keyboard.
• go to the required location in your Word document. Hold the Ctrl key and press the letter “V”.
• to trim (“Crop”) this image, select View/Toolbars/Picture and click on the “Crop” icon:
Then, apply the “Crop” icon to the small square “handles” of the image.
The “Snipping Tool” (available via START button/Programs/Accessories) is very useful too.
To arrange graphs and pictures within a “Word” document
Graphs and pictures can be more manageable if you insert a table into Word and place your graphs and pictures (and text) into cells of the table.
Place this order with us and get 18% discount now! to earn your discount enter this code: special18 If you need assistance chat with us now by clicking the live chat button.