Financial Tracking and Reporting Automation with Google Sheets

This was designed to be a general template / instructional guide on how to easily track financial performance over time with the input of 3 columns of data over time. Automated visuals are also included. The strengths of this model is in the timing controls and visuals.

*Upon purchase, you will be given access to the g sheet link. When you get the link, make sure to go to the top of the sheet and hit: File> Make a Copy so you then get your own editable version.

This was done in the context of reporting up to 4 revenue streams, 45 expense items, and up to 5 miscellaneous cash in and out items. The result is the automated financial reporting of your businesses income, expenses, EBITDA, and cash flow over time.

The 3 primary summaries are done on a weekly and monthly basis over two years and then there is an annual summary over 5 years.

The charts will automatically change as you roll through time and start adding months/weeks onto the x-axis without any work needed from you.

The database columns are date, description, and $ amount. Based on that, the summaries will all automatically update as new rows are added to the database. You will need to make sure the column B descriptions match the summary descriptions for each area of the financial report (basically an income statement). That is how the right $ amounts get into the right lines and then revenues/expenses/cash flows can auto-populate.

The 3 charts include a high level summary of the main sub-totals (Revenue, Expenses, EBITDA). Then there is a chart that compares the 4 revenue streams and a chart to show the net cash flow per period as well as the accumulated cash flow since the start of the reporting.

For the weekly summary, you can pick if your day ends on a Saturday or Sunday and based on that the weekly periods become defined. Also, a today function and unique formula were applied so that the dates (weekly/monthly/annual) know if they are ahead of the date of the end of the current week and therefore go blank if that is the case. This creates smooth and proportionally charts in google sheets that continually update as time passes.

Use as a Budget

You can also use the functionality as it sits for a budget. You can create a copy of the model and label it as a 'Budget' and then do that again and label it 'Actual'. Then, easily compare them to see how you have done against your budget / set goals / etc...The budget would be manually updated based on you entering data into the 'Data' tab and based on that the summaries/charts will display. If you do that, be sure to make the date on the 'control' tab to the final date that you want your budget to go out to or else nothing will show past the end of the current week.

Note this is not financial advice and the model should be used with your own assumptions and figures.