Group case project question # 4&5 needed

Table of Contents Q1 – Raw Data Worksheet 3 Q2 – Simulation Worksheet 12 Question 1: 15 Question 2: 15 Questions 3-7: 17 Question 8: 17 Question 10: 18 Question 9: 19 Q3 – Simulation Worksheet 19 Question 1: 22 Question 2: 22 Questions 3-7: 22 Question 8: 23 Question 10: 23 Question 9: 24 Question 11: 24 Question 12: 25 Question 13: 25 Question 14: 28 Question 15: 28 Question 16: 28 Question 17: 28 Q4 – Simulation Worksheet 29 Question 1: 32 Question 2: 32 Questions 3-7: 32 Question 8: 33 Question 10: 34 Question 9: 34 Question 11: 34 Question 12: 35 Question 13: 36 Question 14: 38 Question 15: 38 Question 16: 38 Question 17: 38 Q5 – Simulation Worksheet 39 Question 1: 41 Question 2: 41 Questions 3-7: 41 Question 8: 42 Question 10: 42 Question 9: 43 Question 11: 43 Question 12: 44 Question 13: 45 Question 14: 47 Question 15: 47 Question 16: 47 Question 17: 47

Step 1: I first put in the following formulas to get the IAT sample statistics.

To begin I ran the FIT test with the IAT values ($A$2:$A$148) and the FIT options in the image below. Choosing the Discrete type because the values are all integers. Even if selecting the various Fitting to have selected either Allow Shifted Distributions, Run Sample Independence Test, or both in addition to AIC/BIC statistics or Chi-Square statistics the results are all almost identical.

From the FIT results we see that the best fitting distribution ranked according to the Chi-Square test is Geometric, then NegativeBinomial, then IntegerUniform, and then Poisson.

Discrete

If I were to have chosen to go with the Continuous type we would have received very similar results that can be seen in the chart below. You can view that in the shape. However, notice that the best fitting distribution is no longer Geometric but Logical.

Step 2: When closing the chart, we are prompted if we want to accept the fitted distribution.

After selected yes, then we are asked to select a cell to place the fitted distribution, which I then selected cell G17. We are then also auto populated the distribution chart in red along with probability parameters. You can easily see that there is a 90% chance likely hood a drive up inter-arrival time will fall somewhere between 0 and 23 minutes.

By looking at the formula bar you can now see the formula in the call as “=PsiGeometric(0.117788461538462)” along with the uncertain variable 25.

Now that we have the uncertain variable cell being fitted to the historical data this is now converted to a cell that we can run simulations and trials from. Using the following formulas as can be seen in the below image we tie each formula to G17.

Prior to running the simulation, the cells appear as errors. Once we run the simulation the results will appear.

If we repeat the same steps for the service time, we would get IntegerUniform as the best fitting distribution.

If we decided to go with continuous, we would receive the below chart with Lognormal as the best fitting distribution. A key difference however is the probability parameters are showing an exponential distribution between 5.98 and 11.86.

After selecting K17 as the destination for the IntergerUniform distribution under certain variable cell, we get the below chart. The probability and parameters chart shows a “uniform” with each option just as likely being selected.

By looking at the K17 formula you can also see that excel has automatically defined the lower limit and upper limit and set them to 6 and 8. This is now what our spread sheet looked like.

If we were to run this simulation once with 10000 trials, we would get the following results and charts.

There are a couple of things to notice. For the drive-up interval times, you can see that there is a leftward leaning skew showing greater probability with drive up times being around 0 to 5 minutes. For the service times, you can see that all three options, the economy at 6 minutes, custom at 7 minutes, and deluxe at 8 minutes are all evenly distributed at 3,333 selections each.

To set the initial seed value we click on the options button.

After doing so we enter the initial seed value into the Sim. Random Seed: Field and click OK.

We ensure that the number of iterations/trials run is set to 10,000 and then we run the simulation.

After running the simulation, we get these results and observations:

We then can click on the statistics button on the top right and select percentiles:

Here we can see that the 95% of cars washed is 1-.95 which equals .05 which is 66.

Then we have to go to the percentiles list to find the percentages. Here we can see that for 10 minutes or less this is 28%.

In order to update these values, you first had to update the minutes in cells P19-P21 from 6-8 to 5-7.

Before

After

By doing this, we started to get errors in some of the cells showing #N/A. To fix this you have to update cells G7 – G105 from =PsiIntUniform(6,8) to =PsiIntUniform(5,7).

Now we can run the simulation without any errors and we get the following chart and values.

Next, we follow the same steps that we did previous to start answering the questions in table 1.

Here we can see that for 10 minutes or less this is 81%.

We can then use the same function but instead use the annual rate of 6.5% and number of periods as 3 and we get an annual loan payment of $4,342.12.

Now that we have the annual loan payment we can figure out the simple payback of the loan assuming if we earn an annual profit of $4,855 how many years does it take us to payback? So, our total debt for the 3 years is going to be $4,342.12 x 3 = $13,026.36. Now we take the total debt divided by the annual profit of $4,855 and we find out it will take us 2.68 years to payback.

To begin we update/add the new table from the Q1 raw data tab onto the Q4 Simulation Worksheet for Simulation.

The main things you have to be concerned about is adding the elite row and updating the probability percentages to reflect.

Next you have to update the service time column like we did in the Q3 worksheet because now the service times go from 5 – 8.

You also have to update column L to include the elite row you added and now you are good to run the simulation. The errors in column M will be gone once you update all of column L.

From running the simulation, we get the following charts and variables:

We now repeat what we did for Q3 in answering all of the table 1 questions however I won’t be fully repeating all the written instructions just mainly showing the images and results.

Here we can see that for 10 minutes or less this is 53%.

Now that we have the annual loan payment, we can figure out the simple payback of the loan assuming if we earn an annual profit of $11,730 how many years does it take us to payback? So, our total debt for the 3 years is going to be $7,362.73 x 3 = $22,088.19. Now we take the total debt divided by the annual profit of $11,730 and we find out it will take us 1.88 years to payback.

To begin doing this simulation we can go off of the Q4 updates that were made and simply change the prices to the new lower amounts updated probability percentages.

From running the simulation, we get the following charts and variables:

Now we do all the same things we did in the Q4 worksheet to get the answers to table 1 however I will not be fully repeating all the written instructions just mainly showing the images and results.

Since the loan was the same but we are earning less than the base year we will never make our money back and this is clearly a bad pricing strategy. This in no way means that the loan is bad, it is just a bad pricing strategy. You can see from the Q4 data that the additional investment in the Rain product is a good idea based all the other data such as probability percentages.