Project Brief ? MYOB OBDC Excel Dashboard
Aim: To develop a basic MS Excel reporting dashboard that uses ODBC to update automatically from MYOB as required.
Background: The current system of monthly reporting entails copy and pasting reporting information from MYOB excel reports into an ?aggregator? spread sheet that interprets the data and summarizes it into a dashboard for distribution to the client. This process is slow and cumbersome, resulting in 4 or more hours spent by the bookkeeper to manually update the report each month when new data is available. This project proposes to eliminate the time spent by the bookkeeper to manually update the report by creating an OBDC connection between the MYOB data file and the Excel reporting dashboard.
Target Outcome: Increased efficiency in monthly dashboard reporting
? Primary Output: An Excel based dashboard application that updates data from an OBDC connection to a specified MYOB data file. The excel dashboard will be operational for all client MYOB data files.
? Secondary Output: A relationship is developed with an expert MYOB and Excel freelancer, enabling further work to be completed via the freelancer as required.
Requirements of Excel Dashboard Spread sheet:
? Display of a reporting dashboard that covers:
o Table of accounts payable/receivable
&#61607; Ability to adjust the time periods displayed in days or months. (4 different periods required i.e. 0-5 days, 6-15 days, 16-30 days and 30 plus days). This could be done using a drop-down list.
o Cumulative graphs of income, operating profit and gross profit. Including last year, year to date and projections (projections based on last year?s PnL).
o PnL reporting table for last month, current reporting month, YTD and full year projection, including:
&#61607; Total Income
&#61607; Total Cost of Sales
&#61607; Gross Profit
&#61607; GP %
&#61607; Total Expenses
&#61607; EBITDA Margin %
o Cash Flow summary
&#61607; Cash at Bank at end of current reporting month
&#61607; plus incoming commitments
&#61607; less outgoing commitments
&#61607; less GST Liability
&#61607; less Income Tax Provision
&#61607; Free Cash Flow
o BAS liabilities for current reporting month and previous month
&#61607; PAYG withholding payable
&#61607; GST collected and paid
&#61607; Total BAS liabilities
&#61607; Ability to select between either cash or accrual for BAS liabilities
o Other relevant liabilities
&#61607; Superannuation for current year
&#61607; Income tax provision for current year
? To refresh the data in real time, i.e. as MYOB is updated
? To update the dashboard in real time, i.e. as MYOB is updated
? Easy to understand layout, i.e. client friendly
? Ability to be used with multiple MYOB data files
A screenshot of a sample dashboard is attached. Please use the Clearwater Pty Ltd company file to program the dashboard.