The first personal spreadsheet computer application was released in 1979. Its name, “VisiCalc,” was short for “visual calculator.”
I like to bring this up when explaining the benefits of using spreadsheet applications, primarily Excel. You really to get see the calculations unwind and the numbers crunch in a way not typically possible with a scripting language.
Analysts use financial models to determine investment periods, capital allocations, and price points. Financial modeling requires building assumptions and “guess-timates” into an integrated product, where uncertainty and expected risk-return tradeoffs are made tangible.
What the future holds is anybody’s guess, so the best financial model accommodates for user interaction: change the interest rate at Year 0, for example, and you’ll see the cash flows at Year 5 instantly. Now that’s visual calculation.
At the same time, getting users involved in making changes to the model can be dicey. You’ll want to client-proof your workbook before allowing that, and know how to recover and compare errant versions.
In the below workshop, I provide an overview of financial model-building in Excel, and the workbook management that goes along with it. Learners will use how to build models that are easy to use and hard to break.
Lesson 1: Workbook design
Objective: Student can follow best practices in designing a new Excel workbook
Description:
- Designing an input, process, output flow
- Excel tables
- Managing worksheets in a workbook
- Naming and referring to objects
Exercises: Create a financial model
Assets needed: None
Time: 40 minutes
Lesson 2: Workbook management
Objective: Student can troubleshoot, debug and calculate worksheets in one or more workbook
Description:
- Calculating across worksheets and workbooks
- Setting calculation options
- Formula auditing tools
Exercises: Continue building financial model
Assets needed: None
Time: 50 minutes
Lesson 3: Workbook protection
Objective: Student can add validation and protection features to a workbook
Description:
- Data validation
- Range, worksheet and workbook protection
- Exercises: Continue building financial model
Assets needed: None
Time: 45 minutes
Lesson 4: Workbook analysis
Objective: Student can perform basic sensitivity and scenario-planning analysis on a financial model
Description:
- One- and two-way data tables
- Scenario manager
- What-if analysis
- Goal seek
Exercises: Analyze financial model
Assets needed: None
Time: 50 minutes
Lesson 5: Workbook deployment
Objective: Student can distribute, recover and compare versions of a workbook
Description:
- Workbook co-authoring and version history
- Spreadsheet compare
Exercises: Recover and compare financial model
Assets needed: None
Time: 35 minutes
Leave a Reply