You have chosen to sponsor your bid up to a maximum amount of .
I work for a property management business (strata management). I need help finalising a reporting dashboard in MS Excel. The dashboard has a Financial reporting component and a Management reporting component.
1) FINANCIAL REPORTING
I have set up Input tables, consisting of:
- last year's actual financial performance by month (sheet FY13 Act)
- this years budget financial performance by month (sheet FY14 Bud)
- this years forecast financial performance by month (sheet FY14 Fcast)
- this years actual financial performance by month (sheet FY14 Act)
The financial data in these tables comes from Excel data dumps generated using the software tool MYOB. I simply dump new monthly data into the relevant month's column each month. All Input tables have a consistent lay-out and consistent line items, although occasionally line items are added.
The financial data is summarised on the sheet 'Dashboard'. This is where you find variance financial comparisons, such as Monthly Actual vs Budget, Quarterly Actual vs Budget, Year-To-Date, Full Year etc. I have grouped and hidden certain line items which are f lesser importance, but want to retain for possible future use.
I still need you to include a formula in column J, which will give me the Actual for the prior month, so I can see Month-on-Month performance.
2) MANAGEMENT REPORTING
Each month another software tool called Strata Master provides me with an excel based data dump listing all properties (named Strata Plans) being managed and the charges (by charge type) made against each property. Each individual data sheet is saved in my dashboard file using the following naming convention: "FY14 Mth Act SM". The only manual rework I do each month is to format, column A, so that each cost line item has the Plan Number in front of it. I'm pretty sure this is needed for any excel formula work. I also remove some lines that come with the original data dump, although these do not impact the numbers and is purely for visual reasons.
Important to note that the $ amounts on each Strata Master monthly dump sheets are inclusive of GST Tax, where as the financial data dumps from MYOB is exclusive of GST. All final reporting should be EXCLUSIVE of GST.
My dashb file has a sheet named "SM codes" which lists every cost code that Strata Master uses.
I need you to think of a way where all of the monthly Strata Master Data is combined into a single sheet (pivot table?), which enable me to create different views of the data. We will need to discuss this in greater detail. See 'Dashb layout' for my initial ideas on this [I dont have enough space in this field]
My dashboard file has a sheet named "Managers Overview" which lists all Strata Plans by number (column C), and the Manager's name (column G). This is a living document. Buildings get added (sometimes removed), manager's change etc. In any work you do, I should be able to change the managers name (currently Manager 1, 2 and 3) and add and remove buildings (Strata Plans, with its unique identifier code e.g. 1421). You will notice at the bottom of the list, 2 buildings which have a slightly different numerical code: BM18604 and 69751BMC; these generate a fixed fee each month, rather than various charges such as all the other buildings, as such they will not show up on the monthly Strata Master Dumps.
I have started with the creation of a 'Input Data' sheet, where I would like to centralise all inputs, such as managers names, months etc. I would like you to continue with this, so that all changes in naming can be made centrally, rather than throughout the entire files. I hope to reduce the change of errors this way.
I am ultimately looking for one dashboard, which provides me with the flexibility to easily update at the start of a new financial year. This is most important to me. I understand some Excel wizardry is needed, but want the file to stay as simple to understand and use as possible. This to allow easy future changes/updating.