Premium101

Budget $30 - $250 AUD
Bids 21
Average Bid $159

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.

Post a Project Like This

This project was awarded to

Yuriy29

Perfect as always. Excellent academic research work. No need to look for other freelancers. THANK YOU!
About the Freelancer
Yuriy29 Profile Picture

Hi,I'm experienced in Financial analysis, Accounting, Economics and Project Management. Ready to complete your projects: 1) Business Reports - Business Plan, Investment Memorandum, Financial Performance 2) Financial modeling, Business valuation, Financial Projections 3) Econometric modeling - Stata, E-Views 4) Essays in Finance, Economics, Accounting, Business, Management, International trade. Accroding to the established formatting and referencing styles - APA, Harvard

Looking to make some money?

  • Set your budget and the time frame
  • Outline your proposal
  • Get paid for your work

Bids on this Project

  • SiroosJafary Profile Picture

    SiroosJafary

    Pretoria,  South Africa

    Over the past 16 years, I developed my skills in various fields such as VBA, Filemaker, Autocad, Excel, etc. I have been re-hired by my clients, on the ground that, I make a very positive relationship as I believe this will help us to work in a productive environment. My 5 stars reviews, would proof that your project will be done on time, on budget and with the highest possible quality. Here are highlights of my expertise: - 2D and 3D design(16 Yrs): I have a solid knowledge of Auto CAD and Civil 3D. I am experienced in various fields such as constructional, architectural and industrial designs. - Filemaker(3 Yrs): With solid knowledge of Filemaker, I can provide you professional solutions either hosted on cloud or on a local network. My scripting techniques can do magic in Filemaker. - Microsoft Office(16 Yrs): I am a guru in Excel, Word and PowerPoint. There is no function or formula which I am not able to work with. I always looking for new challenges in this area. Thanks.

    Visual Basic, Data Processing, Project Management, and Engineering

  • malaya036 Profile Picture

    malaya036

    Noida,  India

    Excel, Data entry, Analytics, SAS --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Data Processing, Data Entry, Excel, and Statistics

  • bluerain05 Profile Picture

    bluerain05

    Ankara,  Turkey

    I'm an English instructor, who has been active since 2004. I have worked in all levels of schools from Primary to graduate, both in public and private. I was assigned to important translation tasks, which were all completed successfully. I carried out an EU Comenius project, which was a multicultural study. I have master's degrees in English Literature and Educational Administration. I lived in US for two years.

    PHP, Graphic Design, Translation, and Audio Services

  • aashishchalise Profile Picture

    aashishchalise

    Kathmandu,  Nepal

    Detail-oriented, Excel Expert with hands on experience in quantitative / statistical analysis, budgeting, forecasting, Business Development and Administration side of business. • Manage robust modeling and reporting to facilitate executive-level decision making • Strong analytical skills; support business operations through analysis of key performance indicators and trends. • Consensus-driven communicator; liaise across various business units and promote organizational success. • Rapidly adapt to new technologies, Expert in MS excel, emphasize on time reduction and accuracy through automation of processes MICROSOFT EXCEL SKILLS • Develop application for FINANCIAL MODELLING. • Create relational database for better analytics. • Create automated templates (for financial reporting, and analyzing tools). • Expert in Pivot Tables, with training in Power Pivot and many other Excel 2013 tools. • Develop VB applications for various purposes. For e.g. Expense Management, Employee Management, Forecasting, Budgeting. • Conversion/extraction of data from different formats/programming language to excel. • Creating web based application within excel. For e.g. (Stock Price Tracker, EBay Data Organizer)

    Data Processing, Accounting, Excel, and Finance

  • Yuriy29 Profile Picture

    Yuriy29

    pittsburgh,  Ukraine

    Hi,I'm experienced in Financial analysis, Accounting, Economics and Project Management. Ready to complete your projects: 1) Business Reports - Business Plan, Investment Memorandum, Financial Performance 2) Financial modeling, Business valuation, Financial Projections 3) Econometric modeling - Stata, E-Views 4) Essays in Finance, Economics, Accounting, Business, Management, International trade. Accroding to the established formatting and referencing styles - APA, Harvard

    Project Management, Data Entry, Research, and Proofreading

  • Tablassus Profile Picture

    Tablassus

    Sacavem,  Portugal

    *More than 15 years of programming experience. *7 years working with Visual Basic languange. *5 years working with VBA for applications and building automated bots. *Solid knowledge on SQL, XML, TCP/IP. *Skilled in Math, Statistics and Data Mining. *Special interest on Artificial Intelligence projects. *Frequecy of University (Degree in Chemical Eng.) *4 years experience teaching Math and Chemistry. *3 years working on Data Mining projects. *World Customs Organization e-learning Course. *6 years experience on International Trading for a Multination al Corporation. *Adviso r for several projects in the area of software management / developpement. *Perfectionist, Professional, Pro-active..

    XML, Visual Basic, Data Processing, and Project Management

  • seanmdale Profile Picture

    seanmdale

    Cambridge,  United Kingdom

    Visual Basic, Data Processing, Accounting, and Excel

  • rodrigoibanez Profile Picture

    rodrigoibanez

    Mendoza,  Argentina

    More than 8 years developing Excel spreedssheets for engineering calculation, standards, information managment and any other solution related. More than 4 years working as Electrical engineer, experience at design of BOP systems for hydroelectric plants.

    Excel, Electrical Engineering, and AutoCAD

  • MasterExcel Profile Picture

    MasterExcel

    faridabad,  India

    Hi, I am master in excel, word, i have 5 yrs experience in web scrapping.

    SEO, Data Entry, Excel, and Photoshop

  • ethicalboy Profile Picture

    ethicalboy

    Sarajevo,  Switzerland

    We are a team of professionals and we try to deliver high quality work with a fair price to you. We also have different skills and work in different divisions, our review speaks about our quality and commitment to work. We have experience in excel, power-point, Prezi, Camtasia and making presentations and we are very professional in Microsoft word (very professional), statistics, mathematics, business analysis and business plan, financial analysis, video making, research, web search, visualizing and processing your data. As the head of team I like business consulting and business promotions to o, also we like to help to teach others how they can do their homework but we do not like to do their homework instead and when they do not want to learn it. Also I am familiar with different decision making methods such as fuzzy analytic hierarchy process (FAHP) and AHP and love to deliver best of the best service. Also we just do legal and ethical projects and do not do harassing or plus 18 projects. In short, some of our service include (but not limited) to: -Analyze your qualitative and quantitative data using SPSS, NVIVO, SAS, Excel -Develop WBS for projects and other documents -Help to develop Prezi slides and videos - Develop WordPress websites - Develop Excel macros, pivot tables, formulas, data manipulations -Create PowerPoint slides, templates -Solve your Microsoft word problems, create your customized styles, develop dynamic references, form, header and footer customization, solve printing issues... -Create PDF forms and edit PDF files -Help in write business plans -Help to create videos, specially tutorial videos. -Search the web, do market research, do process analysis and financial analysis

    Audio Services, Data Processing, Project Management, and Engineering