Problem 1

A company selling a perishable good (so there's no need to worry about inventory) has been doing some research on the demand and its sensitivity to price. They've been pricing their product at $5.00 for a long time and have seen demand fluctuate randomly. The most recent 30 weeks of sales data are contained in the table below:

Week	Sales		Week	Sales		Week	Sales
1	417		11	413		21	352
2	376		12	366		22	398
3	406		13	397		23	404
4	348		14	425		24	405
5	402		15	418		25	468
6	400		16	417		26	379
7	400		17	430		27	408
8	345		18	385		28	443
9	380		19	427		29	359
10	431		20	407		30	432

The problem is that they're not sure how their profits would respond if they were to try different prices. The company agrees to explore a few different prices. The following table summarizes the prices they tried and the demand they observed.

Price	Observed Demand
$4.00	494
$6.00	344

In such a small price window, it's generally safe to assume that the demand is a linear function of the price with an additive stochastic element with a mean of zero:

demand=0+1price+

To understand how to properly price and supply their product, the company needs to know the mean and standard deviation at all prices.

The mean is given by completing the regression and using the equation:

demand=^0+^1price

Your task in the Regression assignment was to find the estimates, ^0and ^1.

For this problem, your task is to find the optimum price the company should charge and the quantity the company should have on hand while taking into account the randomness. The optimum price and quantity are the price and starting quantity that maximize the expected profits. There are 3 different sources of randomness. There's randomness in the slope estimate, the intercept estimate, and the natural randomness in demand.

Based on the data, you can simulate demand at any price using the equation below:

^0+^1p+tn2MSE1+1n+(pp)2(pip)2

In this equation,p is the price at which you'd like to simulate the demand,MSE is the calculated mean squared error from your regression,n is the number of data points in your regression,p is the average price of the data in your regression, andpi refers to the individual prices in the data used in your regression. The termtn2 is the only random element in the equation -- it's a random draw from the t-distribution with n2 degrees of freedom. Random draws (i.e., simulations) can be generated in Excel using the formula:

=T.INV(RAND(),n-2)

(you'll need a real number instead of just "n" for Excel to work properly with this formula -- see the paragraph above for a description of what "n" means).

Your second task is to answer questions about the randomness in demand.

A.

Using the procedure outlined above, simulate 2000 values of demand. What is the mean value of demand for a price of $6.00?

b.

What is the standard deviation of demand for a price of $5.00?

c.

What is the standard deviation of demand for a price of $6.00?

Note: Round to the nearest hundredth (two digits to the right of the decimal)

d.

Now calculate the profits for each simulation. Each unit costs $1.00 to purchase. What are the average profits for a price charged of $5.00 and a starting quantity in-hand of 400?

Note: make sure you calculate profits based on revenue from sales (not just demand -- you can't sell units you don't have in-hand).

Note: round answer to the nearest hundredth.

E.

Now that you understand, to some extent, how a change in price affects demand, you can use a simple model to find the optimal price and quantity to have at the start of each day. Since the product is perishable, any units that don't sell are wasted. Each unit costs $1.00; so if the company purchases an extra 75 units that aren't sold, then the company could have had higher profits by having 75 fewer units at the start of each day. If, on the other hand, the company had a demand for the day of 500 units but only started the day with 400 units, then their profits could have been much higher if they'd started the day with an additional 100 units. The amount of additional profits would depend on the price they charge. The company must choose how many units to have on hand at the start of the day before demand for the day has been observed. Generate at least 2000 demand simulations. Calculate the sales for each simulation (recall that the number of sales is the minimum of the number of units on hand at the start of the day and the number of units demanded). Then calculate the revenue and profits for each demand simulation. Recall that each unit on hand at the start of the day costs $1.00 whether the company sells it or not. Find the average of the simulated profits. Then find the price and quantity that maximize the expected (i.e., average simulated) profits. The quantity to have on hand at the start of the day must be an integer, but the price can be any number.

What is the optimal price? In other words, there is a price and starting quantity on-hand that maximize the average of the simulated profits. What is the price that does this?

Note: round to the nearest hundredth of a dollar (two digits to the right of the decimal).

HINT: Remember to "freeze" any randomness before running Solver. You can do so by copying the cells in which the random numbers are first generated and then pasting them as values. Before you run Solver, you should hit F9 or click Formulas > Calculate Now. If any cells change when you do so, you've still got more randomness in the spreadsheet that needs to be "frozen" before running Solver.

HINT: The GRG Nonlinear solver method might generate an acceptable answer when coupled with an integer constraint.

F.

What is the optimal starting on-hand quantity?

Note: this should be an integer with the integer constraint.