Send the link below via email or IMCopy
Present to your audienceStart remote presentation
- Invited audience members will follow you as you navigate and present
- People invited to a presentation do not need a Prezi account
- This link expires 10 minutes after you close the presentation
- A maximum of 30 users can follow your presentation
- Learn more about this feature in our knowledge base article
324-10 Spreadsheet Modeling
Transcript of 324-10 Spreadsheet Modeling
© 2016 Cengage
Time Series Forecasting
There are specialized software packages available for descriptive, predictive, and prescriptive business analytics
They provide the user with numerous options and the capability to perform detailed analyses
(used by HSBC, Barclays, Toyota, Dell, AOL, ...)
(used by Google, CitiBank, ...)
-other options are considerably more expensive
- or require substantial user training
- spreadsheets are less expensive, often come preloaded on computers
- spreadsheets are fairly easy to use
Spreadsheet models are mathematical and logic-based models. Their strength is that they provide easy-to-use, sophisticated mathematical and logical functions, allowing for easy instantaneous recalculation for a change in model inputs. This is why spreadsheet models are often referred to as
Principles for building reliable spreadsheet models
Build a conceptual
model of a decision problem
Convert the conceptual model
to a mathematical model
Implement the mathematical model in a spreadsheet.
A conceptual model that shows the relationships between the various parts of the problem being modeled
Nowlin Plastics produces a line of cell phone covers. Nowlin’s best-selling cover is its Viper model. The annual fixed cost for the Viper cover is $234,000. This fixed cost includes management time and other costs that are incurred regardless of the number of units eventually produced. In addition, the total variable cost, including labor and material costs, is $2 for each unit produced.
Nowlin is considering outsourcing the production of some products for next year, including the Viper. Nowlin has a bid from an outside firm to produce the Viper for $3.50 per unit. Although it is more expensive per unit to outsource the Viper ($3.50 versus $2.00), the fixed cost can be avoided if Nowlin purchases rather than manufactures the product.
Next year’s exact demand for Viper is not yet known. Nowlin would like to compare the costs of manufacturing the Viper in-house to those of outsourcing its production to another firm, and management would like to do that for various production quantities. Many manufacturers face this type of decision, which is known as a
Conceptual Model for Production
Conceptual Model for Make vs. Buy
= quantity (number of units) required
= FC the fixed cost of manufacturing
= the per-unit variable cost of manufacturing
= total cost to manufacture
= total cost to purchase
Excel offers a number of tools to facilitate what-if analysis
General Rules to Follow:
- Document the model and use proper formatting and color as needed
- Use simple formulas
Data Table quantifies the impact of changing the value of a specific input on an output of interest.
Example: Nowlin (One way data table)
Example: Nowlin (Two way data table)
IF and COUNTIF
Example (Foster Generators)
Foster Generators operates plants in Cleveland, Ohio; Bedford, Indiana; and York, Pennsylvania. Production capacities for the
three plants over the next three-month planning period are known.
Spreadsheet Model (using SUMPRODUCT)
Management would like to determine how much of its products should be shipped from each plant to each distribution center via a Whatif analysis.
Example (Granite Insurance)
The director of sales at Granite Insurance needs to award bonuses to her sales force based on performance. There are 15 salespeople, each with his or her own territory. Based on the size and population of the territory, each salesperson has a sales target for the year.
The measure of performance for awarding bonuses is the percentage achieved above the sales target. a salesperson is placed into one of five bonus bands and awarded bonus points.
Spreadsheet Model (using VLOOKUP)
& Evaluate Formulas
creates arrows pointing to the selected cell from cells that are part of the formula in that cell
The Evaluate Formula button allows you to investigate the calculations of a cell in great detail.
The Error Checking button provides an automatic means of checking for mathematical errors within formulas of a worksheet.
The Watch Window, located in the Formula Auditing group, allows the user to observe the values of cells included in the Watch Window box list.
Value you want to look up
range where you want to lookup the value
the column number in the range containing the return value
Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE