OM 300 1nd Edition Lecture 8 Outline of Last Lecture I Environmental Regulations and Industry Standards II ISO 14000 III Process Strategy a b c d Process focus Repetitive focus Product focus Mass customization Outline of Current Lecture II Spreadsheet Modeling III Breakeven Point IV Using Goal Seek V Using Data Tables and Charts Current Lecture The Spreadsheet Modeling Process 1 Turn off the computer Draw a picture diagram identify controllable uncontrollable inputs outputs Influence Chart 2 Sketch out overall plan for spreadsheet model Determine where inputs intermediate calculations and outputs will go 3 Develop the base case spreadsheet model 4 Test the model using trial values 5 Use the model to perform the needed analysis 6 Document the model so others can understand it Influence Chart You should start the spreadsheet modeling process by drawing an influence chart It is a simple diagram to show the relationships between inputs and outputs in a spreadsheet model The goal is to define problem structure The chart ignores all available numerical data It identifies the main elements of a model These notes represent a detailed interpretation of the professor s lecture GradeBuddy is best used as a supplement to your own notes not as a substitute The chart helps to define the assumptions of the model Key Relationships On Formula Sheet PROFIT Revenue Total Cost REVENUE Quantity made and sold x Unit Price TOTAL COST Variable cost Fixed Cost VARIABLE COST Unit Cost x Quantity made and sold Spreadsheet Model Yellow Boxes B5 B9 B7 B9 B6 B13 B14 B12 B15 Evaluating Spreadsheet Models Correct o Correct numerical answer for base case i e given information Flexible o Accurate results if any of the input values are changed o Each input value entered only once in the model o Formulas contain only cell references not numerical values Good B1 C1 Bad B1 55 Documented o Descriptive labels units of measure numerical formatting cell formatting cell comments o Printouts include row column headings gridlines footer Finding the Breakeven Point What Quantity results in a Profit of 0 o Calculate the breakeven point by hand We have a flexible model Can do what if analysis on cell B9 to determine when the Profit becomes 0 However Excel has Goal Seek tool which can automate this what if analysis Goal Seek works backwards to find the value of an input quantity that causes an output quantity to have a particular value Using Goal Seek Excel 2003 Tools Goal Seek 2007 Data What if Goal Seek o Set Cell Output Cell cell must contain a formula o To Value Specify the numerical value you want the output cell to have e g 0 for a breakeven analysis o By Changing Cell Input Cell cell must contain a value o Spreadsheet Goal Seek Comments Breakeven analysis is simple and can be calculated by hand However there are many common business problems that aren t this simple and a spreadsheet model is required We may be interested in some other level of profit say 2 000 Goal Seek can be used to find specific values for the other input quantities as well For example we could find the variable cost the fixed cost and the selling price for which profit equals 0 for a specified quantity made and sold Using Data Tables Spreadsheets are a great what if tool But what if analysis can be tedious Excel has a feature called a Data Table o Data Table allows one to systematically vary one or two input quantities and keep track of a resulting output value o For example vary quantity made and sold and keep track of profit Constructing a Data Table 1 Enter labels in Rows 22 23 as shown in previous slide 2 Cells A25 A41 Enter 0 500 8000 use Edit Fill or write a formula to add 500 to the above quantity 3 Cell B24 Enter B16 This is the output that Excel will compute each time 4 Select A24 B41 Keep this range selected 5 From menu Data Table For the column input cell select Cell B9 6 Click OK If all the profit values are the same press the F9 key F9 forces Excel to recalculate the spreadsheet Constructing a Chart 1 Select the data that you want to chart Listed either down columns or across rows Put the X axis data in the first row or column 2 Click the Chart Wizard button or choose Insert Chart on the menu bar 3 Select the XY scatter plot when the X axis data are numerical values 4 Select some other type of plot when the X axis data are just labels 5 You can include multiple plots on the same chart by adding additional Source Data