Please solve with Excel Solver and show step by step work with excel tables, Solver input and explanations.

Evaluating R&D Projects at WestCom Systems Products Company

WestCom Systems Products Company develops computer

systems and software products for commercial sale.

Each year it considers and evaluates a number of different

R&D projects to undertake. It develops a road map for

each project, in the form of a standardized decision tree that

identifies the different decision points in the R&D process

from the initial decision to invest in a project

$$

s development

through the actual commercialization of the final product.

The first decision point in the R&D process is whether

to fund a proposed project for $1$

$$year$.$ If the decision is no$,$

then there is no resulting cost; if the decision is yes, then

the project proceeds at an incremental cost to the company.

The company establishes specific short $-$ term, early technical milestones for its projects after $1$year$.$ If the early milestones

are achieved, the project proceeds to the next phase

of project development; if the milestones are not achieved,

the project is abandoned. In its planning process, the company

develops probability estimates of achieving and not

achieving the early milestones. If the early milestones are

achieved, the project is funded for further development

during an extended time frame specific to a project. At the

end of this time frame, a project is evaluated according

to a second set of $($later$)$technical milestones. Again, the

company attaches probability estimates for achieving and

not achieving these later milestones. If the later milestones

are not achieved, the project is abandoned.

If the later milestones are achieved, technical uncertainties

and problems have been overcome, and the company

next assesses the project$$s ability to meet its strategic

business objectives. At this stage, the company wants to

know if the eventual product coincides with the company$$s

competencies and whether there appears to be an eventual, clear market for the product. It invests in a product $$prelaunch$$

to ascertain the answers to these questions. The

outcomes of the prelaunch are that either there is a strategic

fit or there is not, and the company assigns probability

estimates to each of these two possible outcomes. If there

is not a strategic fit at this point, the project is abandoned

and the company loses its investment in the prelaunch process.

If it is determined that there is a strategic fit, then

three possible decisions result: $(1)$The company can invest in the product$$s launch, and a successful or unsuccessful outcome will result, each with an estimated probability of occurrence; $(2)$the company can delay the product$$s launch and at a later date decide whether to launch or abandon; and $(3)$if it launches later, the outcomes are success or failure, each with an estimated probability of

occurrence. Also, if the product launch is delayed, there is always a likelihood that the technology will become obsolete or dated in the near future, which tends to reduce the expected return. The following table provides the various costs, event

probabilities, and investment outcomes for five projects the

company is considering:

$$ Project $$ Decision Outcomes$/$Event $12345$ Fund$1$ year $ $200,000$ $ $350,000$ $ $170,000$ $ $230,000$ $ $400,000$ P$($Early milestones$$yes$)0\mathrm{.}700\mathrm{.}670\mathrm{.}820\mathrm{.}600\mathrm{.}75$ P$($Early milestones$$no$)0\mathrm{.}300\mathrm{.}330\mathrm{.}180\mathrm{.}400\mathrm{.}25$ Long$-$term funding $ $650,000780,000450,000300,000450,000$ P$($Late milestones$$yes$)0\mathrm{.}600\mathrm{.}560\mathrm{.}650\mathrm{.}700\mathrm{.}72$ P$($Late milestones$$no$)0\mathrm{.}400\mathrm{.}440\mathrm{.}350\mathrm{.}300\mathrm{.}28$ Prelaunch funding $ $300,000450,000400,000500,000270,000$ P$($Strategic fit$$yes$)0\mathrm{.}800\mathrm{.}750\mathrm{.}830\mathrm{.}670\mathrm{.}65$ P$($Strategic fit$$no$)0\mathrm{.}200\mathrm{.}250\mathrm{.}170\mathrm{.}330\mathrm{.}35$ P$($Invest$$success$)0\mathrm{.}600\mathrm{.}650\mathrm{.}700\mathrm{.}750\mathrm{.}80$ P$($Invest$$failure$)0\mathrm{.}400\mathrm{.}350\mathrm{.}300\mathrm{.}250\mathrm{.}20$ P$($Delay$$success$)0\mathrm{.}800\mathrm{.}700\mathrm{.}650\mathrm{.}800\mathrm{.}85$ P$($Delay$$failure$)0\mathrm{.}200\mathrm{.}300\mathrm{.}350\mathrm{.}200\mathrm{.}15$ Invest$$success $ $7,300,0008,000,0004,500,0005,200,0003,800,000$ Invest$$failure $2,000,0003,500,0001,500,0002,100,000900,000$ Delay$$success $4,500,0006,000,0003,300,0002,500,0002,700,000$ Delay$$failure $1,300,0004,000,000800,0001,100,000900,000$ Determine$$ the$$ expected$$ value$$ for$$ each$$ project$$ and then$$ rank$$ the$$ projects$$ accordingly$$ for$$ the$$ company$$ to consider. This case is based on R$.$ K$.$ Perdue, W$.$ J$.$ McAllister, P$.$ V$.$ King, and

B$.$ G$.$ Berkey, $$Valuation of R and D Projects Using Options Pricing and Decision Analysis Models,$$ Interfaces $29,$ no$.6($November$$December $1999)$: $5774.$