simulation

User Generated

CebsrffbeUryyran

Business Finance

Description

The instructions require you to develop and run a simulation model using Excel and write up an Executive Summary for this Case Study.

Unformatted Attachment Preview

CASE STUDY: Simulation BRU-THRU For this assignment, you will analyze a case study, develop a simulation model in Excel, and prepare a short executive report (plus appendices) that discusses the conditions of the case and your recommendations. Bru-Thru, Inc. is a chain of beverage supply stores located throughout upstate New York. The stores are designed to enable customers to pick-up beverages, snacks, and party supplies without getting out of their cars. A typical store design is shown in the Figure below. A service lane runs through the middle of the store, and soft drinks, beer, and other supplies are stored at various locations along both sides of the service lane. When a customer drives into the store, the store clerk takes the order, fills the order, and collects the money. The customer remains in the car when receiving the service. When additional customers arrive at the store, they wait in a line outside the store until the preceding customer's order is complete. Then the next customer in line drives into the store for service. Service Entrance Car 3 Car 2 Service Lane Car 1 Exit Waiting Area Bru-Thru's President is considering opening a new store near a shopping center and has requested planning information on the projected operation of the store, including profitability of the store, and the potential number of lost sales due to waiting lines. The fixed costs for the new store including rent, salaries, and overhead comes to $1275 per day. In modeling the system we will study the store's operation in terms of what happens during time periods of 6 minutes each. That is, we will count the number of customer arrivals, count the number of customers lost, and determine whether or not a customer is being serviced during each 6-minute interval. We assume that the average time to fill each order is 6 minutes. Based on a study of traffic flow, the company has estimated that the probability distribution of customer arrivals is as shown in Table 1. As the table shows, there is a 0.32 probability of no customers arriving during a given 6-minute interval, a 0.41 probability of one customer, and so on. Number of Customers Arriving 0 1 2 3 4 Probability 0.32 0.41 0.18 0.07 0.02 Sales records from the company's other stores show that customers vary in terms of the size of the order placed. The revenue generated by each customer is normally distributed with a mean of $15 and a standard deviation of $4. As an additional operation condition, experience with other company stores indicates that customers will wait for service only if there are less than four cars in the waiting line. If a customer arrives and there are already four cars in the waiting area, the customer will drive off. This failure to enter the waiting line is referred to as balking and results in a lost customer and a lost profit. Use simulation techniques to determine if the Bru-Thru president should open a new store near the shopping center. You should develop a simulation model for one day or 100 periods -- since each period represents a 6minute time interval, ten customers can be serviced per hour and the store is open 10 hours per day (thus 100 customers (periods) per day). After developing the simulation, you will record the daily totals (profit, lost customers, etc) and then repeat the day (using the F9 key) thirty times to simulate the profitability and lost sales for the month. USING THE INFORMATION PROVIDED IN THIS CASE STUDY, PREPARE AN EXECUTIVE SUMMARY: The Executive Summary should be approximately 1 to 2 pages (plus appendices) and include the following sections: 1. 2. (20 points) Case Synopsis (include a brief summary of the case and the data provided) (40 points) Methodology (including a discussion of what information was provided and how you used this information to analyze the problem) a) Conduct a one day simulation using Excel (100 customers (periods) per day). HINT: set up a simulation to repeat what happens during each of the 100 six-minute periods. For each period, keep track of the number of customers currently waiting, the number of customers arriving, number of customers being served (0 if there is no one waiting or arriving and 1 otherwise), the order size (profit) for that customer (if there is a customer), and number of customers balking. b) For each day, determine the total revenue per day and the number of lost customers per day. c) Simulate 30 days to find out the average total profit per day and the average number of lost customers per day. You can repeat the simulation thirty times (eg. using F9) and keep track of the total profit and number of lost customers per day. 3. (20 points) Findings and Conclusions (include summary of analysis results. What other factors should 4. (20 points) Recommendations (based on your simulation and other factors, what advice would you give to also be considered in making this decision) Bru-Thru's President regarding opening a new store near a shopping center? Consider profitability of the store and the number of lost sales due to waiting lines.) *Based on a case problem from Anderson, Sweeney, Williams, Camm, Cohran, Fry, Ohlmann, An Introduction to Management Science.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

BRU-THRU
In this case, BRU-THRU is beverage chain supply store in New York and this store are designed
to enable customers collect beverages, snacks and party supplies apparently, without getting out
of the car. They have a service lane which runs across the mile of the store and customers drive
into the store.as such, when a customer comes to pick his products at the store after arriving he
request his products while in the car and remain on the lane to be served while he is in the car.
In this context BRU-THRU president again wants to start up another new store just in the near
shopping center and there by planning has to be done to improve from the current running store
system to a more advanced system. Notably, profit and system efficiency are key components to
start a new store. However planning has to be done to project the operation of the store and as
such this included, profitability of the store, potential number of lost sale due to waiting on
lines.in this case the fixed cost of $ 1275 in a day included salaries ,and rent.
As such, in implementing the new system we have to note the operations of the store in terms of
activities in due time of 6 minutes each and count the number of customers being served at that
particular time within that interval time.as shown in the figure below.
Number of customers arriving

Probability

0

0.32

1

0.41

2

0.18

3

0.07

4

0.02

In this case the revenue generated by each customer is normally distributed with a mean of $15
and a standard deviation of $4.
Therefore the profit per day with the number of customer being served as well as loses put into
consideration given by
NUMBER OF CUSTOMERS * PROBERBILY IN ONE DAY *30 to get the both loses and
profit in one day incurring to 30 days.
Methods used
In this case methods used to distinguish the implementation the current system with the new on
include
Sampling
This where the comparison of activities and operation in the current system
Data analysis
In this case data is being analyzed in both current and the new implemented system
Findings
It is imperative to note that implementing a system a new system BRU-THRU president has to
consider the above findings to improve the his business in terms of profit, customer services and
effective operations between his workers and clients.
As a conclusion, it’s imperative to note that BRU-THRU is beverage chain supply store in New
York and this store are designed to enable customers collect beverages, snacks and party supplies
apparently, without getting out of the car. As such, implementing a new beverage store with the
aim of providing quality service and making profit as well and this is done in consideration of
the operation and services to the clients saving time and making more profit in due time.

Recommendation
In regards to BRU-THRU beverage store it’s imperative to note that implementation of a new
system in consideration to the key components mentioned above its clearly seen that with the
above information in terms of profit and service the stores operation will be affected positive
since the system will work effectively and I there recommend the above system to be
implemented by the president.

Attached.

AAPL Beta Estimation Page 1 of 21

This page shows the reformatted Excel regression output and graph of the line of best fit. The last tab
of the spreadsheet shows how to convert price and index data into the returns used in this analysis.
Monthly data

Adj. R-squared measures the %
variation in loss and profit returns
due to its relation with the market.

SUMMARY OUTPUT
number of customers

300
0.47032
2.01%
0.45%
0.10023
60

profit
loses
Observations

BRU-THRU

Alpha is the predictable firm-specific
component of returns. Over this period
loss average monthly return was about
3.4% higher than the market index. You
can see the positive alpha (regression
intercept > 0 on the graph).

20%
15%

df

SS
0.1655
0.5827
0.7482

MS
0.1655
0.0100

Coefficients Standard Error
0.034
0.0131
1.004
0.2474

t Stat
2.5939
4.0588

1
$1,275
$1,275

Intercept

F
Significance F
16.4735
0.0001

PROFIT

10%
implimentation process
operation
daiy
Total

5%
0%

P-value Lower 95% Upper 95%Lower 95.0%
Upper 95.0%
0.0120
0.0077 0.059986 0.00773
-5% 0.059986
0.0001
0.5090 1.499489 0.508955 1.499489

-10%
Beta is the slope of the regression
line. Beta measures how sensitive
profit returns are to market-wide
information (beta = 1 implies about the
same sensitivity as the average stock).
The graph depicts a regression line
with a slope = +1.00.

-15%
-20%
-20%

ˆ
number of customers arriving
Observation
0
1
2
3
4

Proberbility
0.32
0.41
0.18
0.07
0.02

-15%

CAPM

-10%

-5%
0%
LOSS

5%



10%

ˆ CAPM
 Ri  
 rf   1  RMKT  rf



15%

20%

AAPL 4-Factor Alpha & Betas Page 2 of 21

In recent years, estimating alpha over and above multiple sources of systematic risk has become the industry practice. In the Fama-French 4-factor model, there are systematic risk factors for the market
(Mkt-RF), the size premium (SMB stands for small minus big, the amount by which small-cap stock returns are expected to exceed large-cap stock returns), the value premium (HML stands for high minus
low, the amount by which the returns of high book-to-market (value) stocks are expected to exceed the returns of low book-to-market (growth) stocks, and momentum (UMD represents a momentum
factor that takes into account the extra returns expected to be earned when professional investors chase momentum trends, or the amount by which the returns of stocks with good price momentum -AAPL's alpha and market beta are substantially different when measured against the 4-factor model because the market factor used in the Fama-French model is less volatile than the NASDAQ index used
on the previous spreadsheet tab. AAPL registers a market beta of 1.56, higher than their single-factor market beta of 1.00, when measured against a less volatile market index. Because their market beta
is higher, their alpha is lower -- 2.4% per month compared with the 3.4% obtained from the single-factor model. Even though AAPL only has significant exposure to the market beta (the t-statistics on the
remaining systematic factors are insignificant), the higher market component of risk in AAPL's returns when measured according to the 4-factor model lowers the amount by which their returns appear to
beat the market (alpha), after adjusting for risk. It could be argued, however, that the higher F-stat of the first regression model indicates that it does a better job of modeling AAPL's returns than the 2nd
model, which includes insignificant factors (the higher the F-stat, the more vigorously we reject the hypothesis "the entire model is garbage"). One last technical note: the regression depicted on this page
uses the more statistically correct method of regressing AAPL's excess returns (over and above the risk-free rate) on the excess market factor returns (the factors Mkt-RF, SMB, HML and UMD are
constructed as excess returns and require no additional adjustment).
The data below have already been converted to monthly percentage changes.
Date
AAPL
AAPL-RF Mkt-RF
SMB
HML
UMD
RF
9.12
8.99
4.34
4.35
1.09
-1.70
0.13
2.53
2.38
-5.11
5.78
4.23
7.92
0.15
-4.04
-4.18
-1.19
-3.69
2.37
3.05
0.14
-23.95
-24.08
-7.15
3.50
1.55
6.17
0.13
-13.88
-14.03
-8.26
-5.11
-3.70
3.41
0.15
-3.28
-3.42
0.66
-2.17
2.15
1.68
0.14
-1.76
-1.90
-10.14
2.73
1.13
9.09
0.14
10.76
10.62
7.36
-2.99
-6.57
-5.17
0.14
-3.49
-3.61
6.01
3.16
-1.52
16.26
0.12
-7.61
-7.72
-5.44
-0.54
3.85
9.63
0.11
0.28
0.18
-2.44
1.37
-0.84
1.53
0.10
4.60
4.51
-1.63
-0.35
-1.54
1.29
0.09
-5.86
-5.96
0.93
0.88
-1.71
1.50
0.10
0.57
0.47
8.18
1.21
-0.08
-9.48
0.10
26.30
26.21
6.26
4.71
0.27
10.79
0.09
6.12
6.02
1.53
1.47
0.64
-1.06
0.10
10.60
10.53
2.24
5.64
-2.11
-0.35
0.07
7.31
7.24
2.42
2.64
1.78
-0.55
0.07
-8.40
-8.48
-0.99
0.56
0.99
-0.07
0.08
10.42
10.35
5.96
2.91
1.75
3.70
0.07
-8.65
-8.72
1.59
2.23
1.39
1.63
0.07
2.30
2.22
4.47
-2.87
2.76
-5.67
0.08
5.52
5.45
2.24
2.60
1.66
2.58
0.07
6.03
5.97
1.49
-1.20
0.37
-1.14
0.06
13.04
12.95
-1.16
1.85
-0.01
0.20
0.09
-4.66
-4.74
-2.50
-2.53
-1.69
-5.33
0.08
8.84
8.78
1.35
-0.12
-0.31
1.64
0.06
15.97
15.89
2.08
2.25
1.72
2.08
0.08
-0.61
-0.71
-3.87
-3.82
4.42
-2.32
0.10
6.68
6.57
0.16
-1.56
1.13
-1.54
0.11
12.35
12.24
1.95
2.82
0.40
5.28
0.11
35.19
35.08
1.67
0.49
-0.95
-1.54
0.11
27.98
27.83
4.67
4.11
1.96
3.24
0.15
-3.97
-4.13
3.36
0.18
-0.35
-2.82
0.16
19.41
19.25
-2.82
-1.67
2.52
3.12
0.16
16.67
16.51
2.11
-0.76
2.85
3.19
0.16
-7.11
-7.32
-1.90
-1.37
1.71
0.93
0.21
-13.46
-13.67
-2.73
-3.95
-0.49
-0.84
0.21
10.26
10.02
3.55
3.01
-1.16
0.46
0.24
-7.42
-7.65
0.92
2.58
2.84
2.10
0.23
15.87
15.63
4.09
2.77
-0.47
0.05
0.24
9.94
9.64
-0.89
-0.88
1.44
2.24
0.30
14.33
14.04
0.77
-0.64
1.22
3.50
0.29
7.42
7.15
-2.35
-1.05
-0.74
-1.37
0.27
17.76
17.45
3.73
0.98
-1.75
0.39
0.31
6.00
5.68
0.03
-0.47
0.51
0.77
0.32
5.04
4.69
3.66
5.32
1.18
2.77
0.35
-9.30
-9.64
-0.50
-0.31
-0.76
-1.80
0.34
-8.42
-8.79
1.54
3.51
0.04
1.22
0.37
12.23
11.87
0.94
-1.22
3.07
0.65
0.36
-15.09
-15.52
-3.53
-3.00
2.75
-3.66
0.43
-4.18
-4.58
-0.44
-0.47
1.51
1.52
0.40
18.67
18.27
-0.59
-3.90
3.25
-2.24
0.40
-0.16
-0.58
2.09
0.80
-1.66
-3.48
0.42
13.46
13.05
1.54
-1.21
-0.49
-0.98
0.41
5.33
4.92
3.30
1.68
0.49
-0.18
0.41
13.05
12.63
1.95
0.70
0.39
-1.00
0.42
-7.44
-7.84
0.68
-0.93
2.56
0.81
0.40
1.05
0.61
1.50
0.02
0.00
0.22
0.44
-1.31
-1.69
-1.78
1.38
0.32
-1.32
0.38

196801
196802
196803
196804
196805
196806
196807
196808
196809
196810
196811
196812
196901
196902
196903
196904
196905
196906
196907
196908
196909
196910
196911
196912
197001
197002
197003
197004
197005
197006
197007
197008
197009
197010
197011
197012
197101
197102
197103
197104
197105
197106
197107
197108
197109
197110
197111
197112
197201
197202
197203
197204
197205
197206
197207
197208
197209
197210
197211
197212

SUMMARY OUTPUT
Regression Statistics
Multiple R
0.49
R Square
23.8%
Adjusted R Square
18.3%
Standard Error
10.19
Observations
60
ANOVA
df
Regression
Residual
Total

Intercept
Mkt-RF
SMB
HML
UMD

4
55
59

SS
1785.74
5707.17
7492.91

Coefficients Standard Error
0.024
1.46
1.565
0.43
0.098
0.62
0.345
0.71
0.237
0.39

MS
446.44
103.77

F
4.30

Significance F
0.00

t Stat
P-value
1.68
0.10
3.65
0.00
0.16
0.88
0.48
0.63
0.62
0.54

Lower 95%
-0.48
0.70
-1.15
-1.08
-0.53





R i  rf    1  RMKT  rf    2  RSMB   3  RHML    4 RUMD 
ˆ

FF 4

















FF 4
FF 4
FF 4
FF 4
 R i   rf  ˆ 1
RMKT  rf  ˆ2
RSMB  ˆ3
RHML  ˆ4
R UMD 

SIF Alpha and Beta Estimation Page 3 of 21

SIF Dates

SIF Port (Notice that this example uses weekly data -- the AAPL example used monthly data).

196801 100,179
196802 100,902

SIF Returns SIF x 100 SIF-RF
0.722%

0.722

0.622

Mkt-RF
1.250

SMB

HML

RF

-0.940

-0.560

0.100

S&P 500 NASDAQ
1.441

0.015

Single-factor regression
SIF x 100 vs. S&P 500

196803

99,101

-1.785%

-1.785

-1.885

-1.250

-0.540

-0.950

0.100

-1.185

-0.007

196804
196805

93,535
92,673

-5.617%
-0.922%

-5.617
-0.922

-5.717
-1.027

-5.310
-1.810

-1.310
-1.000

-0.870
-1.170

0.100
0.105

-4.899
-1.775

-0.047
-0.020

196806
196807
196808

93,660
93,020
95,756

1.066%
-0.684%
2.942%

1.066
-0.684
2.942

0.961
-0.789
2.837

1.120
-1.070
2.670

2.080
-0.200
-0.860

-0.940
0.980
-0.230

0.105
0.105
0.105

1.436
-0.530
2.312

0.013
-0.016
0.029

Regression Statistics
Multiple R
0.776
R Square
60.2%
Adjusted R Square
59.8%
Standard Error
2.868
Observations
114

196809
196810

95,328
94,077

-0.447%
-1.312%

-0.447
-1.312

-0.552
-1.392

-0.310
-1.180

-0.030
-0.570

-1.060
-0.150

0.105
0.080

-0.364
-1.387

0.008
-0.012

ANOVA

196811
196812

96,170
98,667

2.225%
2.597%

2.225
2.597

2.145
2.517

196901

98,602

-0.066%

-0.066

-0.146

196902 101,516

2.956%

2.956

2.876

1.880
2.810

-1.230
0.280

-0.640
-0.130

0.080
0.080

0.240

-0.880

-1.110

0.080

2.290

1.820

0.070

0.080

2.112
2.796
0.066
2.020

0.014
0.027
0.011
0.029

196903 101,483

-0.033%

-0.033

-0.113

0.410

-0.960

-0.460

0.080

0.270

0.009

97,131

-4.289%

-4.289

-4.369

-3.750

-0.890

-0.700

0.080

-3.917

-0.029

196904
196905

99,230

2.161%

2.161

2.081

2.240

0.360

-0.440

0.080

2.309

0.029

196906

97,476

-1.767%

-1.767

-1.852

-1.340

-0.910

-1.170

0.085

-1.669

0.002

196907

94,168

-3.394%

-3.394

-3.479

-3.710

0.540

1.590

0.085

-3.706

df
Regression
Residual
Total

Intercept
S&P 500

1
112
113
Coefficients
0.0498
0.8676

-0.065

196908

94,493

0.345%

0.345

0.260

-0.290

-0.740

-0.550

0.085

0.347

0.004

196909

92,459

-2.153%

-2.153

-2.238

-1.490

-0.220

-0.270

0.085

-1.237

-0.015

3-Factor regression
(SIF x 100)-RF vs. 3 FF Factors

2.597

2.512

196911

94,891

0.033%

0.033

-0.034

1.650

0.180

0.250

0.067

1.588

0.017

196912

98,052

3.332%

3.332

3.265

-2.700

-1.150

-0.200

0.067

-2.440

-0.026

197001
197002
197003

93,210
96,195
94,302

-4.939%
3.202%
-1.967%

-4.939
3.202
-1.967

-5.006
3.135
-2.019

1.250
-0.170
-4.500

2.280
-1.200
-1.290

-0.510
0.180
0.220

0.067
0.067
0.052

1.125
-0.402
-4.522

0.021
-0.007
-0.063

Regression Statistics
Multiple R
0.793
R Square
62.8%
Adjusted R Square
61.7%
Standard Error
2.830
Observations
103

197004
197005
197006
197007

90,635
90,481
85,777
87,393

-3.888%
-0.170%
-5.199%
1.885%

-3.888
-0.170
-5.199
1.885

-3.940
-0.222
-5.251
1.853

-1.320
-5.420
0.920
4.980

-1.350
1.430
0.880
0.490

-0.350
-0.060
1.690
2.140

0.052
0.052
0.052
0.032

-0.752
-5.412
0.409
4.871

-0.026
-0.041
-0.006
0.037

ANOVA

197008
197009
197010

89,717
87,248
87,697

2.659%
-2.753%
0.516%

2.659
-2.753
0.516

2.627
-2.785
0.484

-4.220
1.220
0.390

0.660
-1.450
-0.740

0.510
0.280
-0.090

0.032
0.032
0.032

-4.596
1.405
0.231

-0.045
0.007
-0.008

197011

89,361

1.896%

1.896

1.864

-1.220

0.480

-0.760

0.032

-1.661

-0.014

196910

94,860

2.597%

2.690

-1.500

-0.890

0.085

2.807

0.025

197012

86,879

-2.777%

-2.777

-2.819

-2.940

-0.550

0.170

0.042

-2.800

-0.026

197101
197102

83,392
85,991

-4.013%
3.117%

-4.013
3.117

-4.055
3.075

-0.360
1.840

0.780
-0.670

-0.310
0.250

0.042
0.042

-0.404
3.212

0.000
0.021

197103
197104
197105

85,712
89,208
89,906

-0.325%
4.079%
0.782%

-0.325
4.079
0.782

-0.367
4.037
0.740

-0.220
4.250
-2.480

1.260
-0.460
-0.620

-0.330
0.090
0.370

0.042
0.042
0.042

-1.075
4.195
-2.742

0.001
0.049
-0.034

197106
197107

87,676
91,509

-2.480%
4.372%

-2.480
4.372

-2.522
4.330

4.220
0.280

-0.190
-0.560

0.260
-0.720

0.042
0.042

4.314
0.540

0.049
0.008

197108
197109
197110
197111
197112

92,914
92,798
92,684
90,461
92,493

1.535%
-0.125%
-0.122%
-2.399%
2.247%

1.535
-0.125
-0.122
-2.399
2.247

1.493
-0.167
-0.164
-2.441
2.205

1.040
-1.070
2.820
-3.040
1.810

-0.640
0.910
0.070
1.270
0.790

0.850
0.170
-0.260
0.090
-0.410

0.042
0.042
0.042
0.042
0.042

1.149
-1.812
2.670
-3.467
1.777

0.022
-0.013
0.034
-0.033
0.032

197201
197202
197203
197204
197205
197206
197207
197208
197209
197210
197211
197212

90,376
90,292
90,965
88,461
85,776
82,710...


Anonymous
Goes above and beyond expectations!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags