What are some good resources for learning financial and operational modeling using MS Excel?
Answers
I have never known an excellent modeler that took
Good luck.
1
Expanding on Regis's thought; spending time understanding the requirements and underlying data and the final result will save a tremendous amount of efforts.
In fact, creating a mock-up of the output before creating the inputs and calculations can save a tremendous amount of time; it will validate your thought process (by getting feedback from the client who is requesting the report).
I'm interested too, any websites or examples of what people are doing out there as it would be really helpful to get a start!
An Excel model for
1. Allows the business to extend the cost/benefit of manual processes to delay an investment in a longer term more expensive solution.
2. Creates a manual mini-application using database design that can document the full accounting process for a particular need, accounting standard, reporting outcome and reproduce that result repeatedly every month.
3. Allows the designer to document every aspect of a manual process and incorporate methodology to enable delegation of the model/tool to junior staff.
4. Good design will enable archive on a monthly basis, and good design will make all the conditional decision support in the model visible and easy to review.
The first step to gaining the skills to design simple and efficient models that will result in the 4 points above is to gain an understanding of conditional formulas and string functions. These two areas of Excel functionality will give you the basis to peel apart text, evaluate it, recombine it. Nested string functions and conditional formulas are the core of any spreadsheet based model.
Don't get confused by thinking you need a lot of macros.
Elegant models don't need a lot of stuff going on.
If you dump data from systems be sure you design the data dump to match the data your model needs. I see too many people trying to use data that is not set up to achieve the purpose.
If your IT team knows how to build ODBC connected reports with stored procedures, then you will want to take advantage of that. Its really nice to have a spreadsheet connected to a database where you only have to refresh it and pivot on it.
Someone asked me about a 3-tiered model. These are simply Datasheet, Pivot Table and vlookup from multiple pivots to a report tab.
Add the string and condition functions and there you go.
The string functions you need are
Left
Right
Mid
Trim
Proper
Upper
Lower
Text
The Conditional Formulas are
If/then/else
iferror
Practice with these and learn to insert evaluator columns that produce a 1 or a 0 to make your formula decisions, sort decisions, etc visible to the reviewer.
That's about it.