more-arw search

Q&A Forum

What are some good resources for learning financial and operational modeling using MS Excel?

Answers

Topic Expert
Regis Quirin
Title: Director of Finance
Company: Gibney Anthony & Flaherty LLP
LinkedIn Profile
(Director of Finance, Gibney Anthony & Flaherty LLP) |

I have never known an excellent modeler that took a course or class. You just dive in and learn as you go. Modeling is a documented thought process. I have been asked to fix models in the past, but very quickly learned that it is more efficient to scrap the old and start fresh vs. trying to reconstruct someone's thought process.
Good luck.

Topic Expert
Wayne Spivak
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO, SBAConsulting.com) |

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).

Ben Welch-bolen
Title: CEO
Company: World Wide Web Hosting LLC
(CEO, World Wide Web Hosting LLC) |

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!

(Agent, JKS Solutions, Inc.) |

An Excel model for accounting purposes does a few things well if it is designed properly.

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.

1489 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.