What would be a simple Excel model that will calculate the daily profit given any order quantity and any realized daily demand.That is, the order quantity should be entered in one cell, the daily demand should be entered in a second cell, and the model should calculate the daily profit and have it appear in a third cell.
Now suppose the order is 32 croissants per day.Using the spreadsheet you developed to do the daily profit calculations, graph daily profit as a function of daily demand (which varies from 25 to 40).That is, your plot should have daily profit on the y-axis and croissant demand on the x-axis.Turn your Excel model with the graph. Just remember that if the demand is less than 32, any leftover croissants are sold at a loss, whereas if demand exceeds 32 you can't sell what you don't have.
Part II: ALISON'S RESTAURANT Alison Rae is owner and operator of Alison's Restaurant located on West Franklin Street in Chapel Hill. As Alison proudly states. \"You can get anything you want at Alison's Restaurant." And though it may be true that you can get anything you want at Alison's m, the establishment is particularly known for its gourmet French breakfasts. There is no better way to startthe morning than with a cup of Alison's strong black coffee and a delicious buttery croissant. Alison found a French bakery in Montreal that was willing to airmail croissants on a daily basis. The croissants were expensivethey cost her $1.20 eachbut she sold them to her restaurant customers for $2.40 apiece. Croissants left over at the end of the day are not very palatable. but a nearby bowling alley was willing to buy any and all leftovers at 40 cents each. Alison is trying to figure out the optimal number of croissants to stock each day. After observing sales for several weeks. she estimated the following probability distribution for the number of croissants demanded each day: Unsdemanded mmmmmmmmmmmmmmm Probability mmmmmmmn-Hmmmm Although Alison thought there might be an analytic model for determining how many croissants to stock, she knew she could build and run a spreadsheet simulation model more quickly. The Montreal bakery required that in any given week she order the same number of croissants each day, so she decided to simulate a week's worth of croissant activity. Alison wishes to determine how many croissants to stock each day to maximize her expected weekly prot. Your task: Write a simple Excel model that will calculate the daily prot given any order quantity and any realized daily demand. That is. the order quantity should be entered in one cell, the daily demand should be entered in a second cell, and the model should calculate the daily prot and have it w in a third cell. Now suppose the order is 32 croissants per day. Using the w you developed to do the daily prot calculations. graph daily prot as a function of daily demand (which varies from 25 to 40). That is, your plot should have daily prot on the y-axis and croissant demand on the x-axis. Turn your Excel model with the graph. Just remember that if the demand is less than 32, any leftover croissants are sold at a loss. whereas if demand exceeds 32 you can't sell what you don't have