I wrote in Friday’s blog about the Dashboard I kept which tracked the Company’s cash, A/R and A/P. I realized, while enjoying a very warm Vancouver weekend with the boys and Kubae, that I should share a dashboard you can use in your business. If you would like to have a copy of the accounting dashboard emailed to you, send me an email using the contact form below and I will send it right out to you.
A word of warning, I have 2 Macros working on this dashboard. One macro automatically updates the graphs on the Dashboard and the other prints the Dashboard to a pdf. The Excel Workbook is saved as Dashboard.xlsm which is a Macro-enabled file. You will have to enable Excel to run the Macros.
I have also protected the Worksheets and the Workbook. There is no password though so you are free to make changes if you like. I recommend saving the file as a different file name prior to making any major changes so you always have the original to fall back on should something go wrong.
How to Use the Accounting Dashboard
There are two worksheets working in Dashboard.xlsm. DataforDB is the worksheet where you have your accounting person enter the data. There are two sections – the first is the “end of day” balances for Cash, A/R, A/P and an amount for the upcoming payroll. The available cash automatically calculates for you.
The second section is where you can have accounting enter the data for Sales completed that day, Cash received that day, Purchases made that day and Cash Payments made.
After the data are entered into the worksheet, click the Update Dashboard button. This runs a macro which updates the graphs. You can also do this manually by clicking on Data Refresh All.
The second worksheet is the Dashboard. Here is where the graphs are for the balances section from the DataforDB worksheet and for the daily transactions section.
The transaction graphs for A/R and A/P have “Slicers” attached. These are ways you can look at your data without bothering the basic graph. For instance, It may be helpful to how sales are by day of the week so I included that as a Slicer for the A/R section.
You can have your accounting person complete the data at the end of the day (or first thing the next morning) and then it can be emailed to you. All they do is click on the button Print Dashboard and it will generate a pdf file named Dashboard that is saved in the users documents section. They can also go to File Saveas and change the file type to pdf if they want to prefer the manual method.
Your team can also simply email you the spreadsheet or save it to where you can get access to it on your time.
I hope this helps you think of new ways to get access to the pulse of your business without you needing to dig through your accounting system. Try using it and let me know your thoughts. If you or your accounting specialist needs help or would like to understand how a Dashboard can be helpful, send me a comment and I will see what I can do for you.
Enjoy your first taste of Pacific Northwest Summer.
Accounting Clarity 