Excel Sample Sheet 32913 | Exercises 6 On Normal Distribution

Partial file snippet.

Sheet 1: ATM

	
	
		Supermarket ATM
		
	
		

		

		

		

		

		

		

		

	
	
		A chain of supermarkets has learned (from the collection of data over several
		

		

		

		

		

		

		

	
	
		months) that the daily weekday draw from the cash machine at its Tel Aviv store
		

		

		

		

		

		

		

	
	
		is normally distributed (and varies independently from day to day), with a mean
		

		

		

		

		

		

		

	
	
		of $15,000 and a standard deviation of $6,000.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		What is the probability that the Monday draw exceeds $18,000?
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		They stock the cash machine on Mondays and Fridays (four days apart). On
		

		

		

		

		

		

		

	
	
		Monday, they put $75,000 in the machine. How likely is it that the machine will
		

		

		

		

		

		

		

	
	
		run dry before the Friday refill?
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$15,000
		mean daily draw
		

		

		

		

		

		

	
	
		$6,000
		std dev of daily draw
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$18,000
		Monday limit
		

		

		

		

		

		

	
	
		30.85%
		Pr( Monday draw over limit )
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		4
		k (days before restocking)
		

		

		

		

		

		

	
	
		$60,000
		mean over k days
		

		

		

		

		

		

	
	
		$12,000
		standard deviation over k days
		

		

		

		

		

		

	
	
		$75,000
		limit
		

		

		

		

		

		

	
	
		10.56%
		Pr( k-day draw is over limit )
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Via simulation:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$18,359
		Monday draw
		

		

		

		

		

		

	
	
		$20,814
		Tuesday draw
		

		

		

		

		

		

	
	
		$17,108
		Wednesday draw
		

		

		

		

		

		

	
	
		$5,803
		Thursday draw
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$62,084
		four-day draw
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		0
		is the four-day draw over $75,000?
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$B$35
		$B$37
		monitored cell
		

		

		

		

		

	
	
		$59,986
		10.56%
		mean
		

		

		

		

		

	
	
		$11,968
		30.73%
		sample standard deviation
		

		

		

		

		

	
	
		$6,858
		0
		minimum
		

		

		

		

		

	
	
		$112,838
		1
		maximum
		

		

		

		

		

	
	
		100,000
		100,000
		number of simulation runs
		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$372
		0.07%
		margin of error (95% confidence)
		

		

		

		

		

	



Sheet 2: Feingeld

	
	
		Feingeld Industries
		
	
		

		

		

		

		

		

		

		

	
	
		Once a year, in December, Feingeld Industries prints a product catalog for the
		

		

		

		

		

		

		

	
	
		coming year. The press run (done by an outside firm) costs them $7,000 (for
		

		

		

		

		

		

		

	
	
		setup), plus $10 per catalog. (At this point, they throw away any old catalogs.)
		

		

		

		

		

		

		

	
	
		Marketing estimates a need for 2000 catalogs over the next year, with a forecast
		

		

		

		

		

		

		

	
	
		standard error of 200 (i.e., one standard-deviation's-worth of uncertainty in the
		

		

		

		

		

		

		

	
	
		forecast is 200 units); actual demand is normally distributed. They also estimate
		

		

		

		

		

		

		

	
	
		a cost of $50 for each catalog request which cannot be immediately fulfilled.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		What is their “critical fractile ” for product catalogs?
		

		

		

		

		

		

		

	
	
		How many catalogs should they print this December?
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$7,000
		setup cost; irrevelant to problem
		

		

		

		

		

		

	
	
		$10
		variable cost per catalog
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		2000
		mean demand for next year
		

		

		

		

		

		

	
	
		200
		standard deviation of demand
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		$50
		cost of leaving a catalog order unfilled
		

		

		

		

		

		

	
	
		

		

		

		

		

		Assuming that, when a request cannot be immediately fulfilled, it
		

		

	
	
		$10
		per-unit cost of being "over"
		

		

		

		is put on temporary hold and filled out of the next-December
		

		

	
	
		$50
		per-unit cost of being "under"
		

		
 
		
		publication run, then - one way or the other - $10 will be spent
		

		

	
	
		

		

		

		

		

		fulfilling that request. The net cost difference between fulfilling it
		

		

	
	
		83.3%
		critical fractile
		

		

		

		immediately, and fulfilling it after a delay, is $50.
		

		

	
	
		2193
		number of catalogs to print
		

		

		

		

		

		

	
	
		

		

		

		

		

		If requests which can't be fulfilled immediately are never fulfilled,
		

		

	
	
		

		

		

		

		

		the cost of being "under" would be $50-$10 = $40.
		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		Similarly, if fulfilling a delayed request with next-year's edition
		

		

	
	
		

		

		

		

		

		cancels out a request for that addition, the cost of being "under"
		

		

	
	
		

		

		

		

		

		is only $40.
		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		Accounting is a non-trivial subject!
		

		

	



Sheet 3: Newsstand sales

	
	
		Demand for weekly newspaper
		
	
		

		

		

		

		

		

		

		

	
	
		Weekly newsstand sales of a local (weekly, nonsubscription) newspaper are
		

		

		

		

		

		

		

	
	
		normally distributed, with a mean of 6,000 and a standard deviation of 700.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		How likely is it that a press run of 7,000 copies will sell out?
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		What total monthly sales (4 issues) could the newspaper publisher guarantee to
		

		

		

		

		

		

		

	
	
		a regular advertiser, while having a 90% chance of meeting the guarantee?
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		6,000
		mean weekly sales
		

		

		

		

		

		

	
	
		700
		standard deviation
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		7,000
		press run
		

		

		

		

		

		

	
	
		7.66%
		Pr( press run insufficient )
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		4
		issues / month
		

		

		

		

		

		

	
	
		24,000
		mean monthly sales
		

		

		

		

		

		

	
	
		1,400
		monthly standard deviation
		

		

		

		

		

		

	
	
		90%
		Pr( meet guarantee )
		

		

		

		

		

		

	
	
		22,206
		sales that can be guaranteed

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet atm supermarket a chain of supermarkets has learned from the collection data over several months that daily weekday draw cash machine at its tel aviv store is normally distributed and varies independently day to with mean standard deviation what probability monday exceeds they stock on mondays fridays four days apart put in how likely it will run dry before friday refill std dev limit pr k restocking kday via simulation tuesday wednesday thursday fourday b monitored cell sample minimum maximum number runs margin error confidence feingeld industries once year december prints product catalog for coming press done by an outside firm costs them setup plus per this point throw away any old catalogs marketing estimates need next forecast ie one standarddeviation sworth uncertainty units actual demand also estimate cost each request which cannot be immediately fulfilled their ldquo critical fractile rdquo many should print irrevelant problem variable leaving order unfilled assuming when...

Related files

Share

Help

Supermarket ATM

A chain of supermarkets has learned (from the collection of data over several
months) that the daily weekday draw from the cash machine at its Tel Aviv store
is normally distributed (and varies independently from day to day), with a mean
of $15,000 and a standard deviation of $6,000.

What is the probability that the Monday draw exceeds $18,000?

They stock the cash machine on Mondays and Fridays (four days apart). On
Monday, they put $75,000 in the machine. How likely is it that the machine will
run dry before the Friday refill?


$15,000	mean daily draw
$6,000	std dev of daily draw

$18,000	Monday limit
30.85%	Pr( Monday draw over limit )

4	k (days before restocking)
$60,000	mean over k days
$12,000	standard deviation over k days
$75,000	limit
10.56%	Pr( k-day draw is over limit )


Via simulation:

$18,359	Monday draw
$20,814	Tuesday draw
$17,108	Wednesday draw
$5,803	Thursday draw

$62,084	four-day draw

0	is the four-day draw over $75,000?

$B$35	$B$37	monitored cell
$59,986	10.56%	mean
$11,968	30.73%	sample standard deviation
$6,858	0	minimum
$112,838	1	maximum
100,000	100,000	number of simulation runs

$372	0.07%	margin of error (95% confidence)

Feingeld Industries

Once a year, in December, Feingeld Industries prints a product catalog for the
coming year. The press run (done by an outside firm) costs them $7,000 (for
setup), plus $10 per catalog. (At this point, they throw away any old catalogs.)
Marketing estimates a need for 2000 catalogs over the next year, with a forecast
standard error of 200 (i.e., one standard-deviation's-worth of uncertainty in the
forecast is 200 units); actual demand is normally distributed. They also estimate
a cost of $50 for each catalog request which cannot be immediately fulfilled.

What is their “critical fractile ” for product catalogs?
How many catalogs should they print this December?


$7,000	setup cost; irrevelant to problem
$10	variable cost per catalog

2000	mean demand for next year
200	standard deviation of demand

$50	cost of leaving a catalog order unfilled
		Assuming that, when a request cannot be immediately fulfilled, it
$10	per-unit cost of being "over"	is put on temporary hold and filled out of the next-December
$50	per-unit cost of being "under"	publication run, then - one way or the other - $10 will be spent
		fulfilling that request. The net cost difference between fulfilling it
83.3%	critical fractile	immediately, and fulfilling it after a delay, is $50.
2193	number of catalogs to print
		If requests which can't be fulfilled immediately are never fulfilled,
		the cost of being "under" would be $50-$10 = $40.

		Similarly, if fulfilling a delayed request with next-year's edition
		cancels out a request for that addition, the cost of being "under"
		is only $40.

		Accounting is a non-trivial subject!

Demand for weekly newspaper

Weekly newsstand sales of a local (weekly, nonsubscription) newspaper are
normally distributed, with a mean of 6,000 and a standard deviation of 700.

How likely is it that a press run of 7,000 copies will sell out?

What total monthly sales (4 issues) could the newspaper publisher guarantee to
a regular advertiser, while having a 90% chance of meeting the guarantee?


6,000	mean weekly sales
700	standard deviation

7,000	press run
7.66%	Pr( press run insufficient )

4	issues / month
24,000	mean monthly sales
1,400	monthly standard deviation
90%	Pr( meet guarantee )
22,206	sales that can be guaranteed

Related files

Share

Share to social media

Help

Login Area