Learn how to build non-financial metrics into your financial reporting
When building a monthly reporting package the typical starting point is putting together a P&L, Balance Sheet, and Cash Flow Statement that you update each period. Our newest feature allows you to generate live reports linked directly to QuickBooks within your existing Google Sheets or Excel models. With these live reports we made it easy for you to add your own calculations in order to customize your reporting in addition to the basic reporting from QBO.
The reports use our Genius Sheets custom formulas - already pre-built - so you don’t have to adjust or modify anything. This saves you time from having to learn and understand the Genius Sheets formulas, while instantly generating a report with your data linked to QuickBooks.
Since the Genius Sheets formulas are already built in, that means you can customize and adjust your report without worry about the cell references breaking.
What types of calculations should you add?
Let’s start with the Profit and Loss Report.
Gross Margin - You can add a row in your P&L to show the gross margin, which is calculated as your revenue minus COGS (cost of goods sold). This can help you understand how profitable the business is on a product level before factoring in your overhead and fixed costs.
Profit Margin - Your total revenue divided by total expenses. This comes in handy if your revenue and expenses can both be somewhat volatile to understand how healthy the business is performing each quarter.
Growth Rate - Typically for revenue you may want to see the rolling 12 month growth rate each month, or even month of month growth rate.
EBIT - For larger businesses you may want to add non-financial metrics like EBIT (earnings before interest and taxes) or EBITDA (earnings before interest taxes, depreciation and amortization). These rows and calculations can easily be added in the newly created report with live data which you can update on command.
Sparklines - We have covered this in previous posts, but adding the spreadsheet feature sparklines in reports is a great way to visualize data changes in a succinct way. As a refresher sparklines is a feature that lets you create a mini line graph in an individual cell.
Burn Rate - This refers to the amount of money your company is spending that is above what you are bringing in. This is most frequently used for earlier stage startups that have high expenses and very little revenue. This calculation is the net for each period of your cash outflows and cash inflows from operations.
From the Balance Sheet we can use metrics around receivables and inventory
Inventory Turnover Rate - This is your COGS divided by the average inventory balance for the period. This can help you understand how often your inventory is selling through and whether or not you are order too much or too little inventory each time.
Accounts Payable (AP) Turnover - Calculated as the Net Credit Purchases divided by the average AP balance for the period. Shows how often the company cycles through its AP per period and measures how effectively you are using AP.
Accounts Receivable (AR) Turnover - Sales on credit divided by the average AR balance for the period. Shows how often the company collects AR per period and measures how effectively you are collecting receivables.
Adding any or all of these metrics can help turn your monthly reporting package from a standard accounting report to a strategic finance model that gives a window into the true performance of your business.
By using our live sheets you can easily and quickly start building a very simple model and layering on projections in the columns next to where the data is populating. This makes it easy for you to roll your forecasts into actuals each period.
This is an easy first step towards building your first financial model!
