The reason why this was built in google sheets is because of the charting functionality. The formulas are built in a way where it knows what current date it is and as you move forward in time the dates appear. This allows for the charts to update with the financial data you have entered as it happens and you don’t have to keep re-adjusting the ranges of the charts.
The design is built for tracking your weekly/monthly/annual profit & loss as well as cash flow and cash position. This is not known ahead of time and therefore you need dynamic visuals that will automatically adjust as you enter your data over time. This template is not for forecasting, but rather tracking what has happened in real time.
The data entry happens with a total of 3 columns. They include a date, category, and amount. Based on that, your profit & loss will populate on a weekly/monthly/annual basis automatically. You only need to enter in the data once and that acts like the primarily log of transactions.
The dashboard will display the following on each of the mentioned time frames:
1. Comparison of income streams.
2. Comparison of EBITDA/Expenses/Total Income
3. Cash flow per period and accumulated cash flow.
The weekly and monthly tracking is over the course of 2 years and the annual view is over the course of 5 years.
There is an area for 5 miscellaneous entries for both cash in and cash out activities that may not be directly related to the business operations, such as debt service or capital expenditures. They have been included so you can see a full cash flow picture as well as EBITDA.
Note, this could also be used for personal finance if you want to track your income vs. your expenditures over time.
If you have a high volume of transactions on the revenue side, it might help to batch it into weekly amounts or even monthly so that you don’t need to enter every single item.