Now we are going to explain how to translate this profit and loss into an actual vs budget analysis.
This is my forecast detailed by month that I develop as a summary spreadsheet within my forecasting workbooks.
There are individual lines that show each department depending on what it spends: production wages, production contractors and wellness benefits.
You’ll notice a lot of repetitiveness. Wages, bonuses, wellness and benefits all repeat because I’m organizing data down at the department level.
Within the department level, each section is a specific QuickBooks account with its own number and description. I segregate each section by prefixes, which makes it easier for the reporting to come out.
I list the departments on a very detailed basis, which allows for zero-base budgeting and is the best practice at this point. This also allows you to get granular in terms of evaluating how you’re performing and pinpoint where anything was missed in a specific department for the month.
Creating an Actual vs Budget Report
The way I marry this up to an actual versus budget report is by using a SUMIF formula. The SUMIF formula goes to my actual profit and loss details and pulls that information forward. That way I can do a combination of actual versus forecast to come up with a projection that is live and fresh each month.
Here is a standard export file of my profit and loss actuals that I pull out of QuickBooks which includes every account in my forecast, used or unused.
Then I can look up a specific number and description in the profit and loss actuals and pull it into my forecast.
Once you set up the formulas, all you need to do is keep downloading the latest export file for the profit and loss actuals.
Then every month, you simply move the formulas in your forecast over another column and do a checksum to make sure that everything is accounted for. So make sure what you’re showing in the forecast is tying back to the profit and loss.
Once this is completed, it’s a trivial task to do things like an actual versus budget report by month, because you are leveraging the SUMIF formulas to pull everything forward.
It all comes down to how you set up your forecast data and your profit and loss actuals.
Once those two are set up properly, it’s a straightforward process. You simply close the month, download the financial statements into Excel, upload it into your forecast, and move your formulas forward a column. Then you’re all set.
Anthony Nitsos, Founder and Fractional CFO
Anthony Nitsos elevates your financial strategy to meet challenges and drive your company value. Working with pre-seed to Series B stage SaaS startups, he ensures that founders have reliable metrics and a solid understanding of the true economics of their business to maximize valuation. He optimizes financial operations, sales operations, human resources operations, and risk management systems. He’s worked with various startups, including two unicorn exits.