The owner of a relatively small business is trying to determine the breakeven level of sales of the company's main product.The fixed cost of manufacturing this product each month is $50,000.The variable cost of producing this product is $85 per unit.The current selling price is $120. The business owner knows that demand for this product is influenced by price. Using some limited historical data, the owner decided to model the relationship between price and demand with the following step function:

Selling price Demand

95 2500

105 2250

115 2000

125 1750

135 1500

145 1250

155 1000

165 750

175 500

185 250

195 0

So, if the price is set to anywhere between $95 (inclusive) and $104.99, the predicted demand level is 2500. If price is between $145 (inclusive) and $154.99, the predicted demand level is 1250, and so on. We'll call this relationship the Selling Price Function (or SPF for short). In this case, the SPF is essentially a lookup table.

Assume for now that this business can meet the demand for its product.Your goal is to build a spreadsheet model that will allow you to do things like find the break even selling price (i.e. the value of selling price that gives a profit of zero) and do some sensitivity analysis to some of the key inputs.

1a - Inf Diagram) Develop an influence diagram for this problem to use as the blueprint for a spreadsheet model you'll build to help you find the breakeven selling price. You can draw it by hand or use any electronic drawing/diagramming tool you'd like. Figure out a way to get your influence diagram into a pdf file and name it: BreakEven-InfDiagram-<your last name>.pdf. So, my file would be called BreakEven-InfDiagram-isken.pdf.

1b - Base Model) Open a new Excel workbook and name it BreakEven-BaseModel-<your last name>.xlsx. Create spreadsheet model to that relates profit to the inputs and will then make it easy to use Goal Seek to find the breakeven selling price. Name the sheet containing the model logic Model1. Since I'm going to ask you to create some Data Tables and to do some Goal Seeking, the structure of your model should be roughly similar to the Quality Sweaters Break Even model we did in class. Here's a "check value" - for Selling Price=$120 you should get PredictedDemand=2000 and Profit=$20000. Make sure you Save often.

When implementing the SPF, you must give the SPF table a range name of spf_lookup_table. In addition, the cell containing the PredictedDemand formula itself (that references the spf_lookup_table) should be ranged named as predicted_demand. HINT: VLOOKUP is a useful function.

1c - Defined Styles) Create Defined Style called BaseInput and use it to format all of the base input values in your model (just as we did with Quality Sweaters problem in class.) You can decide on how the style looks but it must make your model more readable.

1d - 1-way Data Table) Once your base model is done, create one-way Data Table to show how profit and demand are related to selling price. Your Data Table should have four outputs: Demand, Profit, Total Cost, and Total Revenue.Profit. Selling Price is the input. Let Selling Price range from $95 to $195 in steps of $10. In addition, create graph based on your Data Table so that it is easy to visualize the relationship between Selling Price and Profit. Include Total Revenue and Total Cost (but NOT Demand) on your graph. Make sure your graph has all axes labeled, has titles, has axes properly formatted, and looks good. Apply Conditional Formatting to your Data Table so that it is easy to distinguish between positive and negative profit values. Include a text box that discusses the shape of the relationship between Profit and Selling Price. Is it linear? Is it nonlinear? Why do you think the Profit line has the shape it does? Why do the Total Revenue and Total Cost lines have the shape they do?

1e - Break Even) Use Goal Seek to find the break even selling price. So that I can tell you did the Goal Seek correctly, take a screen capture of the Goal Seek dialog box when it is filled in and paste the screen capture into your worksheet. You will know that you've got this right by confirming your result with the Data Table output. Put the resulting break even selling price into a cell so that I can see you found the right price. Then you can set the original selling price back to $120.

1f - 2-way Data Table) You are also interested in the sensitivity of profit to different combinations of selling price and the variable production cost. So, create 2-way Data Table for these two input variables. Let price range from $95 to $195 in steps of $10 and let the variable production cost vary from $75 to $100 in steps of $5. Again, use Conditional Formatting on the resulting table to allow the user to quickly see where profits are positive and negative.

1g - Formula Auditing) Use the Formula Auditing tools to display the precedents and the dependents of the Profit cell in the main model. Then take a screen shot and paste it into a new worksheet tab. Name this tab: ProfitAudit. Hit Save.