Discounting Factor					0	1	2	3	4	5
A). Gross Revenue from Speaker					$0	$6,000,000.00	$6,540,000.00	$7,128,600.00	$7,770,174.00	$8,469,489.66
B). Returns & Allowance (2% of Gross)					$0	$120,000.00	$130,800.00	$142,572.00	$155,403.48	$169,389.79
Net Revenue (A-B)					$0	$5,880,000.00	$6,409,200.00	$6,986,028.00	$7,614,770.52	$8,300,099.87
Labor Cost (4% increasing trend)					$0	$915,100.00	$951,704.00	$989,772.16	$1,029,363.05	$1,070,537.57
Material Cost (6% increasing trend)					$0	$915,350.00	$970,271.00	$1,028,487.26	$1,090,196.50	$1,155,608.29
Overhead Cost (3% increasing trend)					$0	$1,536,120.00	$1,582,203.60	$1,629,669.71	$1,678,559.80	$1,728,916.59
Selling & Administration Costs (18% of Net Revenue					$0	$1,058,400.00	$1,153,656.00	$1,257,485.04	$1,370,658.69	$1,494,017.98
Profit Before Taxes					$0	$1,455,030.00	$1,751,365.40	$2,080,613.83	$2,445,992.49	$2,851,019.44
Less: Tax @30% (constant rate)					$0	$436,509.00	$525,409.62	$624,184.15	$733,797.75	$855,305.83
Profit after Taxes					$0	$1,018,521.00	$1,225,955.78	$1,456,429.68	$1,712,194.74	$1,995,713.61
Discount Factor @5%					0	0.9524	0.907	0.8638	0.8227
Net Present Value of Profits					$0	$970,020.00	$1,111,978.03	$1,258,118.72	$1,408,626.85
										$5,000,039.06
revenue growth	9%
labor growth	4%
materials growth	6%
overhead growth	3%
tax rate	30%
S&A rate	18%
R&N rate	2%
discount rate	13%

The Solid Sound Company (Revisited)

In Homework #1, you created a model for the Solid Sound Company to estimate NPV over a four-year period for a new line of speakers the company is considering. We are going to build on (extend) that previous model in this assignment. Please refer back to the Homework #1 file for more information if necessary.

Tasks:

Perform the tasks outlined in the exact order below. You should have one 'Model' worksheet and one 'Answers' worksheet in your Excel workbook (and only one workbook).

You can your model from the Homework #1 submission, but make sure to REMOVE all of the Goal Seek and Data Table information from this worksheet (to avoid confusion and potential conflicts with @Risk).

Include a worksheet labelled 'Answers' on which you will answer questions for Tasks 3 & 6.

For Tasks 1-3, you do not need @Risk. Thus, you can complete the creation of the corrected and updated spreadsheet model before accessing @Risk.

BeforeyouworkonTasks2-7,youmustfirst revise your Homework #1 submission as needed. Please look closely at the comments related to HW1's Task 1 (provided during grading of HW1) to help guide your revisions. A correct, functioning, deterministic model is necessary to the success of this simulation model.

Brianna is concerned about competition in this market, particularly from the current leader, AudioTronic. If they bring out a similar new speaker line, her first year estimate for revenue drops to $4 million, while her estimates for first year labor, materials, and overhead costs decrease to $859,170, $702,950, and $1,288,750 respectively. She believes there is a 50/50 chance this could occur.

The model should be able to handle this possibility (AudioTronic competes) using a single cell in which the user can enter the value of 1 to represent competition or the value of 0 to represent no competition from AudioTronic - and - using "IF" functions that will update first year costs as this trigger value changes between 0 and 1. (Note: we previously called this a binary variable, in probability terms this is called a

1

Bernoulli distribution, but this should remain user-defined at this time; the base-case value should be set at 0 for your HW submission).

This is still a deterministic model. It is not a simulation model. The only uncertainty should be the change with respect to competition entering the market (i.e. the use of the 0/1 binary variable in a cell, which the user can change to see the two possible outcomes). Update your spreadsheet model to incorporate these changes.

Answer the following the questions:

a) What is the NPV if there is no competition?

b) What is the NPV if there is competition?

c) What is the expected NPV?

oIf you don't know what expected means in this context, try searching online to help guide you in answering this question.

d) Would you recommend going forward with this new product line? Explain your answer.

e) Assume the board would like to see an NPV of $3.5 million to approve a new product line. Would the board approve this product line or not? Explain your answer.

In addition to the uncertainty from competition, six of the other parameters in this model are likely to change. The increase in gross revenue each year is normally distributed, with a mean of 9.9% and standard deviation of 1.4%. The labor cost growth rate is also normally distributed, with a mean of 3.45% and standard deviation of 1.0%. The overhead growth rate can take on any value in the range of 2% to 5% with equal likelihood. The S&A rate is most likely to be 18%, but it could be as low as 15% or as high as 20%. Similarly, while the tax rate is most likely to remain at 30%, it could drop as low as 28% or jump as high as 34%. Finally, materials cost growth is based on the following Discrete probabilities:

Materials Growth Rate

Probability

4% 0.10 5% 0.15 6% 0.15 7% 0.25 8% 0.25 9% 0.10

Incorporate all seven uncertainties into the model via @Risk. Set the RiskStatic values for each distribution to those used in HW1.

2

Note 1: for this part of the assignment, you should not add new parameters or rework your model. You are simply turning your deterministic model into a simulation model by converting the existing parameters into distributions.

Note 2: IF it is helpful, you can do a separate worksheet with all of the distribution parameters and use references to those when defining the parameters in the distribution windows. Or you can enter the parameters directly into the distribution window (you do not have to use cell references for the distribution parameters on this assignment).

Note 3: you do not need to assume independence for distributions in this model. You can assume values drawn from these distributions apply across all years. This makes the model much simpler.

Finish setting up your simulation model via @Risk by defining NPV as your output. Then run 5,000 iterations for this simulation model.

On your results graph, show the probability that the NPV meets or exceeds the $3.5 million mark (as requested by the Board).

oPaste the graph and grid (show the key statistics in the top section) onto a separate worksheet labeled 'Results'

Generate an Input Report for All Inputs, using the default options, and Send To the Excel Active Workbook.

oThis will make a new worksheet within your workbook for each input. Verify that you have the correct number of Inputs and the Distributions are correct before proceeding to the next step. If you spot an error in the distributions, fix them, and start Task 5 again.

Do a Sensitivities Report. Select Sensitivity w/ Graph, set the Sensitivity Calculation Method to Change in Output Statistic, and Send To the Excel Active Workbook.

Answer the following questions based on your simulation results:

a) Would you recommend going forward with this new product line? Explain.

b) Do you think the board is likely to approve this new product line? Explain.

c) Using the Sensitivities Report, which uncertain input that is internal to the

company would be most important to monitor if this product line were approved?

We want to investigate the company's selection of the discount rate on the acceptability of this project to the Board. To do

3

this, we will use the Goal Seek functionality of @Risk. We want to find what the discount rate would have to be in order to achieve a mean NPV of $3.5 million.

Open the Goal Seek window. oFor the Goal: make sure you use the correct Cell and Target Value

(units are important here!), and set the Statistic to the Mean. oFor the Changing Cell: make sure you use the correct Cell, and use

the default of -Infinity for the Minimum and +Infinity for the Maximum (note: I tested changing these values for the problem, and it caused an issue, leaving the defaults works better!)

oClick Analyze when finished. After Goal Seek finishes running, write / "type" (on your 'Answers' worksheet)

the discount rate required to achieve a mean NPV of $3.5 million. After recording your answers, then Reset your Discount Rate on the

spreadsheet to the base-case value (5%)!