In Progress

Premium101

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.

Skills: Data Processing, Excel, Microsoft

See more: where can i find a business plan, to get tax file number, the importance of a business plan, tax manager, sm software, simple business plans, on line business ideas, lay out of a business plan, layout for a business plan, layout business plan, lay out business plan, initial business plan, importance of a business plan, format of a business plan, financial budget plan, a.i.m. property management, actual business plan, uses for excel, ms excel help, excel uses, excel sheet formula, excel help formula, where to find a manager, where can i get a business plan, looking for property manager

About the Employer:
( 1 review ) SURRY HILLS, Australia

Project ID: #5565966

Awarded to:

Yuriy29

Dear Ronald, I am interested to help you with your project. As I have spent over 7 years in corporate finance and financial management, and I have dealt with a number of different Excel tools. So, the main issue is More

$100 AUD in 4 days
(4 Reviews)
2.2

20 freelancers are bidding on average $165 for this job

Teloquence

Consider it done. Check my past reviews for your reference. Eagerly waiting for your reply so that i can start with the implementation.

$250 AUD in 0 days
(184 Reviews)
6.8
maverick44000

Hi Ron. Hope you are doing well. I have read the description and I shall be glad to help you through in organizing the data. I might have a few suggestions for you to have the Model design improved as well. I have sign More

$200 AUD in 5 days
(35 Reviews)
5.8
dpune

Hi, I have more than 14 years of exp and I am expert in this kind of work. I have completed more than 200 projects. Please look at the feedback left by my employer to know more about my work. Waiting for your positive More

$166 AUD in 7 days
(48 Reviews)
5.3
javed1705

I am an ACCA , APA and Mcom finance having full command over advance excel Your project has caught my attention as i know that this is my favorite topic and i am highly interested with due concern... I am full More

$144 AUD in 3 days
(33 Reviews)
5.2
ethicalboy

A proposal has not yet been provided

$126 AUD in 6 days
(42 Reviews)
5.0
SiroosJafary

Good day, I am Excel developer of spreadshetweb.com. I am expert. I teach excel. I can say there is no formula in excel that I cannot handle it. I've got lots of experiences in excel automating and conditional formatt More

$166 AUD in 3 days
(22 Reviews)
4.7
Tablassus

Dear Mr. Ronald This project seems interesting and doable based on my past experience with this type of problem. THE SOLUTION: ============ I have read the project details and also explored the attached file a More

$166 AUD in 3 days
(13 Reviews)
4.6
AndroidAll

Dear sir, I'm interesting in your job. I have much experience in the MS Excel and VBA. I can do this job. If you want, I can develop a demo within a short time. Regards. PS. In order to show my ability, I c More

$250 AUD in 5 days
(5 Reviews)
3.5
teeares

I have done many projects in Excel and I should be able to do this one also. PLease treat this as a tentative bid as I am yet to go through the nitty gritty. I will revise my bid therafter. teeares

$277 AUD in 15 days
(11 Reviews)
3.4
seanmdale

Hi ronaldvangeloven, I am a professional consultant with a breadth of experience in Microsoft Office products specialising in data manipulation and the presentation of business intelligence or management information. I More

$200 AUD in 3 days
(6 Reviews)
3.4
MasterExcel

I am Data Entry, MS Word and MS Excel Expert. i am very much professional in this work i am pretty sure that you cant find a best person for this job like me so i am ready to work on your project with low rate and high More

$30 AUD in 3 days
(10 Reviews)
3.2
rajafaizan

I am software [url removed, login to view] expertise lies in quality service and delivery on Time i guarantee you 100% satisfaction. kindly if you want high quality work then as soon as possible contact me. Hoping Positive Response. More

$122 AUD in 5 days
(4 Reviews)
2.7
tasleem83

Hello, Having gone through your requirement; I feel suitable enough for the role hence my application for the project. I am happy to work with you on this project to achieve set objectives and targets.

$206 AUD in 3 days
(9 Reviews)
2.1
leadsprovider555

Hello sir i am Happy to work on ur project so u can take a look on my [url removed, login to view] my previous projects i did and searched these type of info .so you just need to award me this project to start work on it ASAP ! i will More

$30 AUD in 3 days
(4 Reviews)
1.7
tofazzel91

sir, <My goal is Your satisfaction> I've vast experience on web-searching .....I see Your attachment . I can find out specific company's,product information, Job title, active email address and phone number also web More

$30 AUD in 3 days
(1 Review)
0.0
nomansatti

A proposal has not yet been provided

$155 AUD in 3 days
(0 Reviews)
0.0
rodrigoibanez

La propuesta todavía no ha sido proveída

$88 AUD in 3 days
(0 Reviews)
0.0
aashishchalise

Hello, My intensive experience in Microsoft Excel in various companies in the United States and also here in Nepal has polished my skills to a level where I can execute hardest of the task with efficiency and accura More

$222 AUD in 3 days
(0 Reviews)
0.0
bluerain05

A proposal has not yet been provided

$250 AUD in 7 days
(0 Reviews)
0.0
malaya036

A proposal has not yet been provided

$155 AUD in 3 days
(0 Reviews)
0.0