An entrepreneur who operates a cellular phone store orders inventory of cell phones based on four internal memory specifications - 8 GB, 16 GB, 32 GB, and 64 GB. She wants to evaluate her inventory ordering policy for the phones with four different amounts of internal memory. Any phone unsold at the end of a period is kept in inventory for the next sales period and incurs a holding cost expressed as 10% of the cost per unit per period. If demand during a period exceeds supply for a phone, then sale is lost. The data on the cost and selling prices of the cell phones categorized by these memory specifications are known, and representative data on the past sales are also available. $$\begin{array} { | c | c | c | }
\hline \text { Internal Memory } & \text { Cost (\$) } & \text { Selling Price (\$) } \
\hline \mathbf { 8 G B } & 420 & 430 \
\mathbf { 1 6 } \mathrm { GB } & 500 & 520 \
\mathbf { 3 2 } \mathrm { GB } & 590 & 620 \
\mathbf { 6 4 } \mathrm { GB } & 670 & 700 \
\hline
\end{array}$$ Past Sales: $$\begin{array} { | c | c | c | c | c | }
\text { Period } & \mathbf { 8 G B } & \mathbf { 1 6 } \mathbf { G B } & \mathbf { 3 2 } \mathbf { G B } & \mathbf { 6 4 } \mathbf { G B } \
1 & 276 & 274 & 327 & 332 \
2 & 302 & 211 & 264 & 329 \
3 & 299 & 276 & 306 & 239 \
4 & 261 & 258 & 232 & 277 \
5 & 260 & 244 & 278 & 220 \
6 & 306 & 252 & 262 & 252 \
7 & 227 & 213 & 290 & 253 \
8 & 228 & 217 & 308 & 250 \
9 & 205 & 215 & 246 & 277 \
10 & 236 & 237 & 272 & 284 \
11 & 279 & 251 & 301 & 282 \
12 & 303 & 271 & 271 & 282 \
13 & 266 & 252 & 276 & 317 \
14 & 263 & 306 & 285 & 310 \
15 & 210 & 250 & 328 & 253 \
16 & 247 & 230 & 317 & 340 \
17 & 314 & 285 & 289 & 332 \
18 & 283 & 304 & 312 & 256 \
19 & 283 & 245 & 294 & 272 \
20 & 252 & 237 & 262 & 323
\end{array}$$
a. Construct a spreadsheet simulation model to estimate the total profit the entrepreneur earns in a period when ordering 300 units of each cell phone. To model the respective cell phone demands, fit a realistic distribution to the sales data. What is the average total profit? What is the estimated likelihood that the entrepreneur makes less than $10,000 next period?
b. Using Spearman rank correlation, compute the correlations between the demands for the cells phones based on the four memory specifications. Incorporate a correlation matrix to captures the interrelationships between the demands for each cell phone type. What is the average total profit? What is the estimated likelihood that the entrepreneur makes less than $10,000 next period? Comparing these answers to (a), conclude how the correlated demand affects the model's implications.

Question

An entrepreneur who operates a cellular phone store orders inventory of cell phones based on four internal memory specifications - 8 GB, 16 GB, 32 GB, and 64 GB. She wants to evaluate her inventory ordering policy for the phones with four different amounts of internal memory. Any phone unsold at the end of a period is kept in inventory for the next sales period and incurs a holding cost expressed as 10% of the cost per unit per period. If demand during a period exceeds supply for a phone, then sale is lost. The data on the cost and selling prices of the cell phones categorized by these memory specifications are known, and representative data on the past sales are also available. $$\begin{array} { | c | c | c | } 
\hline \text { Internal Memory } & \text { Cost (\$) } & \text { Selling Price (\$) } \
\hline \mathbf { 8 G B } & 420 & 430 \
\mathbf { 1 6 } \mathrm { GB } & 500 & 520 \
\mathbf { 3 2 } \mathrm { GB } & 590 & 620 \
\mathbf { 6 4 } \mathrm { GB } & 670 & 700 \
\hline
\end{array}$$ Past Sales: $$\begin{array} { | c | c | c | c | c | } 
\text { Period } & \mathbf { 8 G B } & \mathbf { 1 6 } \mathbf { G B } & \mathbf { 3 2 } \mathbf { G B } & \mathbf { 6 4 } \mathbf { G B } \
1 & 276 & 274 & 327 & 332 \
2 & 302 & 211 & 264 & 329 \
3 & 299 & 276 & 306 & 239 \
4 & 261 & 258 & 232 & 277 \
5 & 260 & 244 & 278 & 220 \
6 & 306 & 252 & 262 & 252 \
7 & 227 & 213 & 290 & 253 \
8 & 228 & 217 & 308 & 250 \
9 & 205 & 215 & 246 & 277 \
10 & 236 & 237 & 272 & 284 \
11 & 279 & 251 & 301 & 282 \
12 & 303 & 271 & 271 & 282 \
13 & 266 & 252 & 276 & 317 \
14 & 263 & 306 & 285 & 310 \
15 & 210 & 250 & 328 & 253 \
16 & 247 & 230 & 317 & 340 \
17 & 314 & 285 & 289 & 332 \
18 & 283 & 304 & 312 & 256 \
19 & 283 & 245 & 294 & 272 \
20 & 252 & 237 & 262 & 323
\end{array}$$ 
a. Construct a spreadsheet simulation model to estimate the total profit the entrepreneur earns in a period when ordering 300 units of each cell phone. To model the respective cell phone demands, fit a realistic distribution to the sales data. What is the average total profit? What is the estimated likelihood that the entrepreneur makes less than $10,000 next period?
b. Using Spearman rank correlation, compute the correlations between the demands for the cells phones based on the four memory specifications. Incorporate a correlation matrix to captures the interrelationships between the demands for each cell phone type. What is the average total profit? What is the estimated likelihood that the entrepreneur makes less than $10,000 next period? Comparing these answers to (a), conclude how the correlated demand affects the model's implications.

Quizplus · Accepted Answer

The Answer of An entrepreneur who operates a cellular phone store orders inventory of cell phones based on four internal memory specifications - 8 GB, 16 GB, 32 GB, and 64 GB. She wants to evaluate her inventory ordering policy for the phones with four different amounts of internal memory. Any phone unsold at the end of a period is kept in inventory for the next sales period and incurs a holding cost expressed as 10% of the cost per unit per period. If demand during a period exceeds supply for a phone, then sale is lost. The data on the cost and selling prices of the cell phones categorized by these memory specifications are known, and representative data on the past sales are also available. $$\begin{array} { | c | c | c | } 
\hline \text { Internal Memory } & \text { Cost (\$) } & \text { Selling Price (\$) } \
\hline \mathbf { 8 G B } & 420 & 430 \
\mathbf { 1 6 } \mathrm { GB } & 500 & 520 \
\mathbf { 3 2 } \mathrm { GB } & 590 & 620 \
\mathbf { 6 4 } \mathrm { GB } & 670 & 700 \
\hline
\end{array}$$ Past Sales: $$\begin{array} { | c | c | c | c | c | } 
\text { Period } & \mathbf { 8 G B } & \mathbf { 1 6 } \mathbf { G B } & \mathbf { 3 2 } \mathbf { G B } & \mathbf { 6 4 } \mathbf { G B } \
1 & 276 & 274 & 327 & 332 \
2 & 302 & 211 & 264 & 329 \
3 & 299 & 276 & 306 & 239 \
4 & 261 & 258 & 232 & 277 \
5 & 260 & 244 & 278 & 220 \
6 & 306 & 252 & 262 & 252 \
7 & 227 & 213 & 290 & 253 \
8 & 228 & 217 & 308 & 250 \
9 & 205 & 215 & 246 & 277 \
10 & 236 & 237 & 272 & 284 \
11 & 279 & 251 & 301 & 282 \
12 & 303 & 271 & 271 & 282 \
13 & 266 & 252 & 276 & 317 \
14 & 263 & 306 & 285 & 310 \
15 & 210 & 250 & 328 & 253 \
16 & 247 & 230 & 317 & 340 \
17 & 314 & 285 & 289 & 332 \
18 & 283 & 304 & 312 & 256 \
19 & 283 & 245 & 294 & 272 \
20 & 252 & 237 & 262 & 323
\end{array}$$ 
a. Construct a spreadsheet simulation model to estimate the total profit the entrepreneur earns in a period when ordering 300 units of each cell phone. To model the respective cell phone demands, fit a realistic distribution to the sales data. What is the average total profit? What is the estimated likelihood that the entrepreneur makes less than $10,000 next period?
b. Using Spearman rank correlation, compute the correlations between the demands for the cells phones based on the four memory specifications. Incorporate a correlation matrix to captures the interrelationships between the demands for each cell phone type. What is the average total profit? What is the estimated likelihood that the entrepreneur makes less than $10,000 next period? Comparing these answers to (a), conclude how the correlated demand affects the model's implications.

An Entrepreneur Who Operates a Cellular Phone Store Orders Inventory