Question

1. Develop a simulation model in SPSS for a three-year financial analysis of total profit based on the following data and information. Sales volume in the first year is 100,000 units and is projected to grow at a rate that is normally distributed with a mean of 7% per year and a standard deviation of 4%. The selling price is $10 and the price increase each year is normally distributed with a mean of $0.50 and a standard deviation of $0.05 each year. Per-unit variable costs are $3, and annual fixed costs are $200,000. Per-unit variable costs are expected to increase by an amount normally distributed with a mean of 5% per a year and a standard deviation of 2%. Fixed costs are expected to increase following a normal distribution with a mean of 10% per year and a standard deviation of 3%.

Report the descriptive statistics for profit each year and the cumulative profit. How confident are you that profits will increase each year? Use the percentiles report to answer this question and provide appropriate evidence. (NOTE: Sales, prices, and costs are NOT uncertain but rather the growth in each of these is uncertain. You should get a new growth value each year for each of the four variables for a total of eight uncertain items.)

Answer 1

Answer:

Financial Analysis Model is given as:

1 Financial Analysis Model 3 Year -D5*(1+E4) 5 Sales volume 100,000-C5 (1+D4) 0.50 $ 0.50 7 Selling price $10.00C7+D6 1096 -D9 (1+E8) 9 Fixed cost 200,000.00C9*(1+D8) 10 5% 5% 11 Per unit variable costs $ 3.00 C11 (1+D10) -D11 (1+E10) 12 $500,000.00 (D5"D7)-(D5*011)-D9-(Es* E7)-(E5E11)-E9 13 Profit 14 Cumulative Profit $500,000.00C14+D13 -D14+E13

1 Financial Analysis Model 2 3 Yea 2 4 5 Sales volume 114490 100,000 107000 0.50 $ 0.50 7 Selling price $10.00 $ 10.50 $ 11.00 10% 8 10% $200,000.00 220,000.00 242,000.00 9 Fixed cost 10 5% 5% 3.15 $ 11 Per unit variable costs$ 3.00 3.31 12 $500,000.00 566,450.00 638,714.33 13 Profit $500,000.00 $1,066,450.00 $1,705,164.33 14 Cumulative Profit

Now it is

1 Financial Analysis Model 2 3 Yea 4 5 Sales volume 114490 100,000 107000 7 Selling price $10.00 $ 10.50 $ 11.00 220,000.00 242,000.00 $200,000.00 9 Fixed cost 10 3.00 $ 3.15 $ 11 Per unit variablecosts$ 3.31 12 $500,000.00 566,450.00 638,714.33 13 Profit $500,000.00 $1,066,450.00 $1,705,164.33 14 Cumulative Profit

Now we can run simulation but before running a simulation, select the Run Preferences item from the Run group. The first dialog box has several tabs. We fill tabs Trials and Sampling as shown below

Run Preferences Tials Samping Speed Opions Statistics Tmais Samping Options Statistics Random number generation 5000 Number of trials to runc Use some sequence of random numbers Initial seed value: 図Stop on calculation errors Sampling method 図stop when precision control limits are reached Monte Carlo (more random) 95 Confidence level: Latin Hypercube (more even) Sample size 500 OK OK Cancel Defaults Cance Defaults. Help Help

We would leave the default values in the speed, options, and statistics tabs of the Run Preferences dialog. The last step is to run the simulation by clicking the Start button in the Run group. To create a spreadsheet summary of the simulation statistics for Profit, click on Extract data under Analyze option. Fill the dialog box as shown below.

Extract Data Preferences Data Options Select data to extract: RA Statistics Percentiles Chart bins. □ Sensitivity Data Trial values Forecasts All Choose None Assumptions Choose O None Defaults Help OK

Statistics Cumulative Profit Year 3 Percentiles Cumulative Profit Year 3 2 Trials 5000 $1,461,247.03 3 Mean 10% $1,705,024.24 $1,608,322.40 4 Median $1,704,259.85 1,639,312.49 $1,665,811.90 30% 5 Mode 1,685,086.56 $1,704,235.63 6 Standard Deviation $75,333.33 40% 7 Variance $5,675,110,470.25 50% 6% 8 Skewness $1,723,832.04 0.0609 $1,744,089.74 2.89 70% 9 Kurtosis 1,769,797.27 10 Coeff. of Variability Il 80% 0.0442 1,461,247.03 $1,981,808.17 90% 11 Minimum $1,802,921.24 100% 12 Maximum $1,981,808.17 $520,561.14 $1,065.37 13 Range Width 14 Mean Std. Error

From the above output, the average Cumulative Profit for year 3 is $1,705,024.24 with standard deviation of $75,333.33. From the percentiles, we see that the chance that the total profit will be less than $1,704,235.63 is 50%. To get trend chart, from Control Panel Analyze menu, select Trend Charts. We get following trend chart.

Trend Chart 2 Edit View Trend Preferences Help 5.000 Trials Centered on Median View Trend Chart 2 S720,000.00 $690,000.00 Certainty Bands $660,000.00 il 10% ■ 25% $630,000.00 60% $600,000.00 ■90% S570,000.00 S540,000.00 S510,000.00 3

Figure above shows a trend chart for three years proft. The trend chart displays certainty ranges in a series of patterned bands centered on the medians. The band representing 90% certainty range shows the range of values into which a forecast has a 90% chance of falling From the trend chart, we see that the median profit increases over time.