This project deals with issues of pricing and production that are typical in manufacturing. It is a simplified problem in that each day can be treated separately. In other situations, you would carry unsold inventory for the next day which would make the sheet more complicated.

Introduction

Faiths Bakery bakes and sells fresh bread. Each morning, the bakery satisfies the demand for the day using freshly baked bread. Faiths can bake the bread only in batches of a half dozen loaves each. Each loaf costs (C) $2.25 to make. A loaf sells for a price (P) that she needs to determine, and any bread leftover at the end of the day is sold to a charitable kitchen for a salvage price (SP) of $1.00 loaf. If demand exceeds supply, we assume that there is a lost-profit cost (LC) of $1.75 loaf (because of loss of goodwill, loss of customers to competitors, and so on). The bakery records show that the demand (D) follows a normal distribution with the mean that is a linear function of price (intercept + slope * P). The slope will be negative, as people will buy less at a higher price. By using the historical data, Faiths would like to determine the best price and the optimal number of loaves (Q) to bake each day to maximize profit (revenues + salvage revenues - cost of bread - cost of lost profit). As an intern in a consulting firm, you have been given the task of analyzing her situation.

Part 1: Determine the demand data with respect to price

Historical daily sales have been recorded at different prices and are included in the attached file. You need to analyze this data to predict future sales. To do this, plot sales vs price and use a linear curve fit to calculate the slope and intercept of the data. Use the slope and intercept to calculate the predicted sales at each price in a separate column. The slope and intercept should be calculated in spreadsheet cells. In another column, calculate the difference between the predicted sales and actual sales. Calculate the standard deviation of the difference using the STDEV function in excel. The slope, intercept and standard deviation will be used to model the demand at different prices.

Part 2: Monte Carlo Simulation to determine expected profit.

In this part you will be doing simulations for 1000 days of transactions to determine the best number of loafs of bread to make to maximize profit. If you make too much, you have unsold inventory which you can sell to a charitable kitchen at a loss, and if you make too little you lose customers since they will start to shop at another since they cannot buy what they want at your store. Since demand changes from day to day you want to simulate many days and calculate the results for an average day.

There are several components that contribute to the profit which can be calculated as follows:

The revenues you make by selling the bread can be calculated by:

Revenue=P*min(Q,D)

Salvage revenues you make by selling the leftover bread to a charitable kitchen can be calculated by:

SR=SP*max(Q-D,0)

Cost to make the bread can be calculated by:

Cost of bread=C*Q

Cost of Lost Profit from people no longer shopping at the bakery can be calculated by:

Cost of Lost Profit=LC*Max(D-Q,0)

Therefore the total profit you make is to be calculated by:

Profit=P*min(Q,D)+SP*max(Q-D,0)-C*Q-LC*max(D-Q,0)

To generate demand data based on the analysis you did in part1, use the following equation:

Demand=max(round(norm.inv(rand(), slope * P + intercept, stdev),0),0)

where the slope, intercept, and standard deviation (stdev) are what you calculated.

To do the analysis, generate a spreadsheet to calculate the expected profit for 1000 days for different quantities of bread. Create a column for the day and the calculated daily demand based. In general, it is good to have the variables (P,SP,C,LC,) in separate cells rather than hard coding

them in your spreadsheet. It would be helpful to name these cells to make your other formulas more readable. Create several columns each with a different quantity (Q) (6, 12, 18,) where you calculate the Profit for that quantity. You can also calculate the max of all the Profits to quickly see what it is for the price.

When using min and max, since these functions work on ranges, if you label ranges for Q and D, the min() function will give you the minimum of all the numbers in the Q range and all the numbers in the D range, rather than just the Q and D from your row and column. You have to use the appropriate absolute and relative cell functions in your formula. You could also use and

IF(Q>=D, profit1, profit2)

where

Profit1=P*D+SP*(Q-D)-C*Q

and

Profit2=P*Q-LC*(D-Q)-C*Q

and not use the max() function.

Once you can calculated the profit for each quantity generate a plot of profit vs quantity. You should be able to see that there is an optimum (best) value of the number of loafs to make.

Once you have the spreadsheet created, you can find the best price by manually changing the price from $2.50 to a value that the max possible profit starts to drop. Use increments of $0.50 to simplify pricing. In a separate tab or sheet, record the profit for each price. Plot this to visually show that you have found the optimum price.

Here is the sales data:

Price Sold 3 76 3 106 3 97 3 92 3 79 3 90 3 86 3 84 3 72 3 93 4 74 4 80 4 69 4 69 4 72 4 68 4 75 4 83 4 84 5 64 5 62 5 63 5 78 5 68 5 56 5 59 5 66 5 61 6 41 6 46 6 44 6 36 6 43 6 31 6 44 6 44 6 42 7 27 7 30 7 22 7 28 7 28 7 10 7 25 7 29 7 25 8 9 8 15 8 10 8 1 8 14 8 21 8 3 8 3 8 2