Overview
The lecture covers sensitivity analysis in Microsoft Excel 2016, focusing on how to use data tables to analyze operating profit based on different unit sales and pricing scenarios.
Sensitivity Analysis Basics
- Sensitivity analysis examines results under different combinations of assumptions.
- Used in business to test outcomes (e.g., profit) with varying input values (like units sold and price).
Creating a Financial Model Example
- Example scenario: chair store selling 1,000 chairs/year at $150 each, with $50 cost per chair.
- Fixed costs (like rent and payroll) are specified separately.
- Income statement includes: revenue, cost of sales, gross profit, fixed costs, and operating profit.
Setting Up the Sensitivity Analysis Table
- Create a table in Excel with units sold as row headers and chair price as column headers.
- Example values for units sold: 500, 750, 1,000, 1,250, 1,500.
- Example values for price: $100, $125, $150, $175, $200.
- Link the desired result (operating profit) in the top-left cell of the table.
Populating the Table Using Data Table Tool
- Highlight the whole table, including the corner cell with the linked result.
- Go to Data tab → What-If Analysis → Data Table.
- Set the row input to the units sold assumption and the column input to the price assumption.
- Excel will fill the table with operating profit for each combination.
Analyzing Results
- The table shows how operating profit changes for each price and unit sold combination.
- Allows quick identification of optimal pricing and sales strategies by comparing profits at various points.
Key Terms & Definitions
- Sensitivity Analysis — Evaluating how different input values affect a specific output in a model.
- Fixed Costs — Business costs that do not change with the level of goods sold (e.g., rent).
- Operating Profit — Profit after deducting operating expenses (fixed and variable costs) from gross profit.
- Data Table — Excel tool for displaying results of formulas for various input value combinations.
Action Items / Next Steps
- Practice creating a sensitivity analysis table in Excel using your own data.
- Review Excel’s "What-If Analysis" and "Data Table" features for further understanding.
- Analyze the generated table to determine optimal prices and quantities for maximum profit.