Construct a profit model for each of the problems on separate worksheets in one Excel file and use the models to answer the questions.

Report answers to questions in the worksheet underneath the models. Use complete sentences and proper grammar.

For both problems assume that quantity produced = quantity demanded = quantity sold

1. A startup company, Cookies Cupcakes 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 companys founder, Cookie Smith intends to build an EXCEL model to explore her options. Cookie will sell cupcakes in lots of 6 cupcakes for $5.00 but wants to analyze different values for the price and knows that demand will depend on the price.

Processing costs are $1.45 per lot, cake mix costs $2.38 per lot, pudding mix will cost $0.15 per lot. Fixed costs are $600 per week.

a. Set up a spreadsheet model to calculate total profit with production quantity as the decision variable - assuming that Cookie will sell all that she produces.

b. Use the Excel tool Data/What-If Analysis/Goal Seek to determine the break-even quantity for cupcakes.

c. Use the Excel tool Data/What-If Analysis/Data Table to create a one-way table to demonstrate the sensitivity of profit to changes in selling price per lot.

Use prices of 4.00, 4.50, 5.00, 5.50, 6.00, 6.50, 7.00, 7.50 in your table.

d. Create a graph of the one-way table results.