This issue is brought to you by Genius Sheets. Tired of copy/pasting CSV files into your financial model? I was too. Then I found Genius Sheets.

Unlike other "solutions" that require external software or clunky add-ins, Genius Sheets uses formulas directly inside Excel and Google Sheets to pull QuickBooks data into your model. In addition, Genius Sheets leverages the power of AI to custom build reports (again, directly inside your model).

I used to hate tracking down client login info, exporting to Excel, and manually reconciling things. Now I just refresh formulas. Save yourself the monthly headache and check out Genius Sheets today.

Editors note: please support our sponsors, it helps keep this series free!

Where do I Start?

After many years of working through busted models, errors, poor formatting and sleepless nights, I've developed a layout that works for nearly every model I build.

For context, this would be a layout for a 3-Statement Model, but you could apply it to any analysis you're working on.

I'll talk through each piece so you can use it too.

Wait Isn't There Software That Does This?

(A quick rant on this...)

You know, I feel like I see this online everyday. I'll make an Excel post and someone says "hey Chris don't use Excel, use [software] instead."

I'll say this -- you (and I) should always keep our ears to the market on financial planning software, as there are constantly great strides being made in the world of data aggregation, automation, and collaboration.

However, time and again when I review this software, it is usually limited in some key functionality that I need in a model, especially when it comes to M&A.

In addition, 1.1 billion people use Microsoft's Productivity Services. That's 1 in every 8 people in the world. So, while we don't have an exact number of Excel users, it's safe to assume it's a large portion of that 1.1 billion.

Lastly, Microsoft Office is so much cheaper than financial software that it's hard to beat that value proposition.

So, to close out my rant, unless and until a holistic software solution comes out, I think one of the best skills you can develop is to go deep on Excel, which means understanding how to build a good model.

Alright, moving on...

Section 1: Mapping (gray)

I'll make a large list of all the chart of accounts, but then add an adjacent column to group them into buckets for a more digestible presentation.

For example, I might classify Salary, Bonus, Payroll Taxes, and Benefits into "Labor."

Section 2: Raw Data (red)

I will usually copy/paste data from an accounting system directly into my model, often exported as a CSV.

A more sophisticated approach is to use PowerQuery to clean things up, but that is often beyond the understanding of a lot of people I work with.

Section 3: Budget Schedules (light yellow)

Some of my model can be forecast in a single line. For example, I might want to grow "Office Supplies" by 4%. Easy enough, I can build that right into my Budget tab (bright yellow).

Other line items, however, require more thought and detail. For example, Revenue, Headcount, Capital Projects, Known Contracts, etc.

These detailed items need their own tab or "schedule," the subtotal of which will be pulled into my Budget (bright yellow).

Section 4: Actuals (bright green)

This is just a consolidation of the Raw Data into a time series format, i.e., each month laid out next to one another.

You never know when the old Raw Data won't be available anymore, so I make sure to store everything here in the Actuals.

Section 5: Budget (bright yellow)

Easy to build, this is just a duplicate of the Actuals tab, but the forecast period either:

• Links to one of my Budget Schedules, or;
• Has a growth assumption right then-and-there like Year-over-Year Growth, Percent of Revenue, etc.

So now, it has actual data for my history, and a clean forecast for the future.

Section 6: Financial Model (orange)

It all comes together here in a dynamic 3-Statement Model.

Given all the support I have, it's actually fairly easy to update.

I have a "Latest_Actuals" cell sitting on my Admin tab and I will update this cell each month. My 3-Statement Model will then pull Actuals if available, else, pull my Budget.

The Balance Sheet and Statement of Cash Flows update automatically from there.

Section 7: Summaries (green)

I'm just pulling information from the Financial Model into a print-friendly one-pager.

Everyone wants to see summaries in their own way, so I've learned to build the model as a standalone module (that I trust), and then build printable summaries thereafter.

Pro tip: don't print where you model or model where you print. Just keep them separate.

Section 8: Admin (gray)

Last section but the most critical.

This section consolidates all of the potential errors in one place as well as houses the "Latest_Actuals" cell I mentioned earlier and other global update options.

Pro tip: at the top I will include a "Master Error Check Cell" that I can link to every other tab in my workbook, so that way whenever something goes wrong, I can investigate it right away.

Action Items

Okay that was a lot, I get it.

For this one I'd say take a look at the latest file you're working on, does it follow this general flow?

Output <--- Calculations <--- Data

If so, great! You're building models better than 95% of what I see on a daily basis.

If not, see if you can make a few tweaks to clean and de-risk the file, or apply it to your next build.

That's it for this one. See you next time.

—Chris

How I Can Help Further...

As finance professionals there's one thing we all have in common -- our financial modeling skills need to be dialed.

If you need to refresh or sharpen your modeling skills, you can learn to build the exact models I use with FP&A and Private Equity firms around the world.