Group case project question # 4&5 needed

profilepeaflower9661
Q1BlingMaxRawDataWorksheet-VCedited.xlsx
Home>Business & Finance homework help>Group case project question # 4&5 needed

Q1-Raw Data

Drive up Inter-arrival times (IAT) Wash Service Chosen by Customer Service Time (ST) All data in minutes
3.0 economy 6 Q2 BASE: Situation Now Q3: New Blow-off Q4: New Blow-off + 4 wash "a" Q5: New Blow-off + 4 wash "b"
1.0 deluxe 8 WASH MINUTES PRICE WASH MINUTES PRICE WASH MINUTES PRICE PROBABILITY WASH MINUTES PRICE PROBABILITY
3.0 economy 6 economy 6 $8.00 economy 5 $8.00 economy 5 $8.00 35% economy 5 $7.00 30%
5.0 custom 7 custom 7 $9.00 custom 6 $9.00 custom 6 $9.00 25% custom 6 $8.00 20%
2.0 economy 6 deluxe 8 $10.00 deluxe 7 $10.00 deluxe 7 $10.00 20% deluxe 7 $9.00 20%
1.0 economy 6 elite 8 $11.00 20% elite 8 $10.00 30%
1.0 deluxe 8 ANALYSIS OF SAMPLE VS FITTED DISTRIBUTIONS
1.0 economy 6 INTERARRIVAL TIMES: SERVICE TIMES: IAT Histogram 1 ST Histogram 1
10.0 deluxe 8 Descriptive Statistics IAT Sample IAT Fitted Descriptive Statistics ST Sample ST Fitted
2.0 custom 7 Mean 7.49 ERROR:#N/A Mean 7.03 ERROR:#N/A
2.0 custom 7 Std Dev 6.34 ERROR:#N/A Std Dev 0.80 ERROR:#N/A
4.0 economy 6 Min 1.00 ERROR:#N/A Min 6.00 ERROR:#N/A
1.0 deluxe 8 Max 33.00 ERROR:#N/A Max 8.00 ERROR:#N/A
23.0 economy 6 Data Count 147.00 ERROR:#N/A Data Count 147.00 ERROR:#N/A
2.0 custom 7
2.0 economy 6 distribution 5 distribution 8.000
18.0 deluxe 8
4.0 deluxe 8
3.0 custom 7 LOAN CALCULATION
2.0 deluxe 8 Q3 Q4, Q5
21.0 deluxe 8 Q3 Blower Investment $ 11,500 $ 11,500
1.0 deluxe 8 Q4, Q5 RainX Investmt $ 8,000 IAT Histogram 2 ST Histogram 2
2.0 deluxe 8 TOTAL investment $ 11,500 $ 19,500
6.0 deluxe 8
14.0 custom 7 Loan principal $ 11,500 $ 19,500
18.0 economy 6 Annual interest rate 6.50% 6.50%
11.0 economy 6 Monthly interest rate 0.54% 0.54%
3.0 deluxe 8 Term years 3.0 3.0
1.0 deluxe 8 Term months 36 36
20.0 custom 7 Loan payment/mo ($352.46) ($597.66)
19.0 deluxe 8 Loan payment/yr ($4,342.12) ($7,362.73)
5.0 custom 7
3.0 deluxe 8
9.0 economy 6
10.0 custom 7
2.0 custom 7
1.0 economy 6
6.0 custom 7
3.0 deluxe 8
10.0 deluxe 8
2.0 deluxe 8
9.0 custom 7
2.0 custom 7
33.0 custom 7
1.0 custom 7
5.0 deluxe 8
7.0 economy 6
1.0 custom 7
7.0 economy 6
10.0 deluxe 8
4.0 custom 7
28.0 economy 6
1.0 custom 7
8.0 deluxe 8
2.0 custom 7
9.0 deluxe 8
1.0 custom 7
6.0 custom 7
5.0 deluxe 8
7.0 custom 7
20.0 custom 7
6.0 custom 7
11.0 economy 6
12.0 custom 7
9.0 custom 7
2.0 deluxe 8
16.0 custom 7
22.0 custom 7
9.0 custom 7
14.0 custom 7
15.0 custom 7
1.0 economy 6
7.0 economy 6
8.0 deluxe 8
13.0 economy 6
4.0 custom 7
33.0 deluxe 8
3.0 custom 7
1.0 deluxe 8
8.0 deluxe 8
7.0 deluxe 8
10.0 economy 6
11.0 deluxe 8
9.0 custom 7
5.0 custom 7
6.0 custom 7
5.0 deluxe 8
14.0 economy 6
7.0 deluxe 8
12.0 economy 6
6.0 deluxe 8
5.0 custom 7
8.0 deluxe 8
8.0 custom 7
12.0 economy 6
13.0 deluxe 8
11.0 deluxe 8
4.0 custom 7
14.0 custom 7
3.0 deluxe 8
5.0 custom 7
1.0 custom 7
17.0 custom 7
1.0 economy 6
3.0 economy 6
6.0 deluxe 8
4.0 economy 6
4.0 economy 6
5.0 deluxe 8
4.0 economy 6
1.0 deluxe 8
2.0 economy 6
1.0 custom 7
6.0 economy 6
2.0 deluxe 8
15.0 economy 6
6.0 economy 6
5.0 deluxe 8
7.0 deluxe 8
6.0 economy 6
9.0 economy 6
12.0 custom 7
13.0 custom 7
11.0 economy 6
15.0 deluxe 8
15.0 economy 6
7.0 custom 7
15.0 deluxe 8
7.0 deluxe 8
3.0 economy 6
10.0 deluxe 8
2.0 economy 6
4.0 custom 7
4.0 economy 6
3.0 economy 6
3.0 custom 7
3.0 economy 6
2.0 deluxe 8
8.0 economy 6
13.0 economy 6
12.0 custom 7
19.0 economy 6
8.0 custom 7
8.0 custom 7
1.0 deluxe 8
2.0 custom 7
4.0 economy 6

Summary

BLING MAX CAR WASH PERFORMANCE METRICS TABLE 1
Performance Measure Q2: BASE Situation now (long cycle + 3 wash levels) Q3: Improved (New Exit Blow-off, same 3 wash levels) Q4: New Choices & Prices (Q3 + 4 Wash Levels at Pricing "a") Q5: New Choices & Prices ( Q3 + 4 Wash Levels at Pricing "b")
1 Average number of cars washed per day 76.6 79.0 78.1 78.1
2 95% probability of cars washed per day exceeds 66 67 67 67
3 Average daily revenue $689.28 $711.04 $741.87 $663.78
4 Average daily profit $448.03 $462.18 $482.22 $431.46
5 Average annual revenue (adj for 6% rain days) $236,491.00 $243,959.00 $254,537.00 $227,743.00
6 Average annual profit (adj for 6% rain days) $153,719.00 $158,574.00 $165,449.00 $148,033.00
7 Average wait time (min) 17.81 7.42 11.72 11.72
8 % chance that customers wait 10 min or less 28% 81% 53% 53%
9 % chance that customers wait 10 to 20 min 42% 17% 35% 35%
10 % chance customers wait more than 20 min 30% 2% 12% 12%
11 Improvement in annual profit vs. BASE (Q2) as $ $4,855.00 $11,730.00 -$5,686.00
12 Improvement in average wait vs. BASE (Q2) as % 58.34% 34.19% 34.19%
13 Simple payback vs. BASE (yrs @ 2 decimals) -2.68 -1.88 3.88
14 Annual loan repayment ($4,342.12) ($7,362.73) ($7,362.73)
15 Profit Improvement (#11) less Loan Payment (#14) $512.88 $4,367.27 -$13,048.73
TOTAL INVESTMENT $11,500.00 $19,500.00 $19,500.00
3 year loan monthly repayment ($352.46) ($597.66) ($597.66)
=(4342.12*3)/4855 =(7362.73*3)/11730 =(7362.73*3)/-5686