Microsoft Excel

Microsoft Excel Template: Business Financial Planner for an SME

Hi Everyone! Starting a few years ago, building excel templates became somewhat of a hobby for me.

Last year I built this excel model. It is useful for financial planning of a small business or individual. This app is currently in beta, so you may notice some bugs. Nonetheless, this is a proof of concept of excel’s power for financial planning. I hope you find it an insightful tool. The sheet is pre-filled with sample data of a small circulation newspaper, to give you a sense of how it works.

Excel Financial Planner SME.png

Features & Notes

Cash Flow: the sheet is structured as a cash flow forecast, with revenue and expense accounts on the top, debt servicing in the middle, and balance sheet (Assets and Liabilities) accounts summarized at the bottom.

Note: the starting date is the excel function =today(), representing today’s date. this can be adjusted the first day of the period, if preferable.

Note: You can toggle the Yearly period between Daily, Monthly, Weekly, and Quarterly.

Revenue & Expenses: Define your period (Date Range), schedule (Monthly, Daily, Weekly, Quarterly, or One Time), and specify the account you’d like to use.

Note: the account on the planner should match the account on the cash flow schedule. You can customize these to suit your needs. For one time expenses, or revenue, leave the ending date blank.

Debt: Since most types of debt generally work the same way, you can safely put them here. This feature offers insight into the cost of your debt over time, it’s amortization, when it will be paid off, and how it effects your net worth (liabilities and equity). Use this to plan the elimination or future utilization of debt  

Note: If you are adding a mortgage, be sure to add an asset for it as well, with the same purchase/finance data. If you dont, the net worth calculation will be incorrect.

Note: Revolving debt: If this is marked “Yes”, the account type can be used to plan leverage (borrowing) to cover expenses.

Assets: Plan the purchase, down payment (financing), sale/resale, and appreciation/depreciation of your assets, and see how it will influence your future capitalization. You may change these account types (included in the sample company) to suit your needs, but be sure that you update the accounts on the Cash Flow to match as well. Bank Data: This data is used to import actuals, and measure budget variance. Export data from your banks and paste categorize it here. (Budget variance only appear for prior periods in the cash flow schedule.

Note: you can export your bank data into a csv file from your online account. Paste it in here and remove any unnecessary columns.  

Note: to pupulate the actuals, the categories must match the categories on the cash flow.

BUGS: There is currently a bug: where the schedule assumes that any recurring transaction will continue continue through the entire period. (Ex: If a $100 recurring expense is only planned for 2 weeks, and the cash flow schedule is set to monthly, it will assume the expense repeated for every week of that month, and result in $400 instead of $200. I am currently looking for a fix!)