You have recently been appointed as a project manager at a well known medium$-$sized engineering company called QME Products $($Pty$)$ Ltd$.$ You have to manage the development of a new product. This product will be an in$-$house development that will be funded from company reserves. As a result, there is a great deal of pressure both on the cost and schedule of the project. Your first task is to develop a budget for the project. You have consulted with all the internal role players and collected relevant data. The estimated cost for each element of the WBS $($work breakdown structure$)$ for each phase of the project has been aggregated into the following proposed base cost budget for each phase of the development project:

As this is a typical product development project, there is a substantial degree ofBase cost budget

$\backslash$table$[[$A$.$ Build business case,,R$265000],[$B$.$ Development,R$10820000,],[$B$.1$ Concept design,R$5470000,],[$B$.2$ Prototype manufacture,R$1890000,],[$B$.3$ Testing of prototypes,R$2600000,],[$B$.4$ Design for manufacturing,R$860000,]]$

C$.$ Final testing and validation

R$1250000$

D$.$ Launch

TOTAL

R$12973000$ uncertainty regarding the cost estimates. You have reduced all the information and assessments about cost uncertainty to the following:

A$.$ Build the business case

This is a relatively low cost phase and will be assumed to have no uncertainty.

B$.$ Development

B$\mathrm{.}1$ Concept design

Concept design needs to deal with many unknowns, including two new technologies that have not completely been proven. The following discrete random variable reflects the uncertainty about the cost:

Prob Value $(\%$ of B$\mathrm{.}1$ base cost$)$

$0,1080\%$

$0,1090\%$

$0,30100\%$

$0,25120\%$

$0,25150\%$

B$\mathrm{.}2$ Prototype manufacture

The company has an excellent prototype workshop and there is actually a chance that this cost may be less than the estimated amount. A normal distribution with the mean equal to the base cost and a standard deviation of $0,2$ times base cost can be used.

B$\mathrm{.}3$ Testing of prototypes

There is a degree of correlation between the cost of concept design and the cost of testing of prototypes. You have decided that this relationship can best be captured as follows: If the cost of Phase B$\mathrm{.}1$ is $100\%$ or less than its base cost, then the cost of Phase B$\mathrm{.}3$ can be represented by this discrete variable

Prob Value $(\%$ of B$\mathrm{.}3$ base cost$)$

$0,290\%$

$0,5100\%$

$0,3120\%$

If the cost of Phase B$\mathrm{.}1$ is more than $100\%$ of its base cost, the cost of Phase B$\mathrm{.}3$ can be represented by this discrete variable

Prob Value $(\%$ of B$\mathrm{.}3$ base cost$)$

$0,3100\%$

$0,3120\%$

$0,4150\%$

B$\mathrm{.}4$ Design for manufacture

Design for manufacture is a well$-$established competence in the company and the base cost can be taken as certain.

C$.$ Final testing and validation

This can be taken as a fixed cost as well. However, there is a possibility that final testing and validation will not be required at all because prototype testing may already incorporate this. The chance that this will occur is estimated as $3$ out of $10.$

D$.$ Launch

The previous phases of the project will have no impact on this phase and the base cost can also be taken as certain.

$2\mathrm{.}1$ Develop a model in Excel to simulate the total development cost $($Phase B$)$ as well as the total project cost. Produce descriptive statistics $($including the $95\%$confidence level$$ for the mean$),$ a histogram and CDF for each case. Describe how you developed the model.

$2\mathrm{.}2$ How do you know that the random costs you generated for the uncertain budget elements did indeed correctly model the relevant distributions?

$2\mathrm{.}3$ What do you learn from the result for the $$confidence level$($as determined by Excel$)?$ Calculate the $95\%$ confidence interval for the mean by hand and compare it to the $$confidence level$.$ How does the Excel $$confidence level$$ relate to the confidence interval?

$2\mathrm{.}4$ Interpret and discuss the results. Compare the simulated mean to the total base cost. What are your observations and conclusions?

$2\mathrm{.}5$ What target budget would you recommend and why?

Submit your Excel worksheet$($s$)$ for this question and also paste all relevant results into your assignment.