Question

l. Baxter's Books is considering an order for the latest thriller from author Jimmy Peterson. The store manager assumes that demand will be 50, 100, 150, or 200 copies next month and needs to decide whether to order 50, 100, 150, or 200 copies for that period. Each book will cost Baxter's $20 and can be sold for $30 Baxter will be able to send any unsold books back to the publisher for a refund of $5 each. According to past experience, the probabilities of demand for these types of books has been as follows Demand 50 100 150 200 Probability of Demand 0.20 0.35 0.25 0.20 a. Create a payoff table for this problem b. Calculate the Expected Value for each alternative (number of books to be ordered from publisher) c. Based on Expected Value, how many books should Baxter's order from the publisher? d. Calculate EVwithPI, EVofPI and interpret the EVofPl. 2. Samson's Sweets, located in downtown Centerville, generates profit from combining two purchased ingredients-cake mix and pudding mix into a variety of cupcakes -processing the cupcakes (cooking packaging, delivery, etc.) and selling them to local grocery stores. The company's founder, Sam Adamson intends to build an EXCEL model to explore his options. Sam will sell cupcakes in lots of 6 for $6.00 but wants to play around with that figure and knows that demand will depend on the price. Processing costs ane $1.45 per lot, cake mix costs $2.30 per lot, and pudding mix will cost $0.25 per lot. Fixed costs are $1,500 per week for space rental, $500 per week for machine rental and $1,000 per week for labor. a. Set up a spreadsheet model to calculate total profit, assuming that Sam can sell all of the cupcakes that are produced i. Use the Goal Seek model in excel to determine the break-even quantity for cupcakes. i. Set up a one-way table to demonstrate the sensitivity of profit to changes in selling price per lot. You must use Excel What-I/Data Table for this. iii. Create a graph that shows Total Revenue and Total cost vs Quantity Report the Break-even quantity Use prices of 4.00, 4.50, 5.00, 5.50, 6.00, 6.50, 7.00, 7.50 in your table. (Note: Table results will change when the quantity in the model is changed Show quantity from 0 to 5,000 on your graph b. Set up a new spreadsheet model (on a separate worksheet) to calculate profit with demand quantity as an unknown variable and production quantity as a separate unknown variable. Costs and Revenue same as above, with the following addition -any leftover cupcakes will be sold at a discounted rate of $2.50 per lot. Note: You must use the IF function in this model. i. Create a two-way table showing the sensitivity of profit to changes in demand and production quantities. Use quantities for both as 5000, 6000, 7000, 8000, 9000, 10000. You must use Excel What-If/Data Table for this.

Answer 1

demand 5o 00 150 20D 50 500 50D 50D 500 50 -100 250 500 I5o0 2oD-1156-500 750 2000 ohen deniand 2.0 loe calculated ts all caees 1C) Bachov should order lbo berks ax EV = 1225 = 1225-750 475