- Sydney, AU
- £35 /hr
- Available now
I am a Data modeler and analyst who can provide robust Excel, Access, VBA based models and databases as time-saving tools, and create data-flow procedures that will allow for efficientmore...I am a Data modeler and analyst who can provide robust Excel, Access, VBA based models and databases as time-saving tools, and create data-flow procedures that will allow for efficient and accurate data capture, reporting and analysis.
I have 7+ years experience as a finance modeler and analyst working alongside finance teams in large international companies such as Deloitte, Accenture, British Telecom, and EMI music Publishing with expereince in the Finance, Consulting, Telecoms, Media and Property industries.
Area Covered: Anywhere (working remotely)
Summary: Provided numerous highly complex product budget models, in conjunction with system migration support, in aid of creating a consolidated multi-dimensional (product/customer/account/feecode level) company-wide rolling forecast within the new SAP based custom built system (BPC). Also provided monthly management reporting support, customer and product data analysis for customer queries, end of financial year roll-over of finance models, and general streamlining and automation of financial processes.
Re-development and automation of numerous detailed Transactional Product budget models:
• Due to time restrictions and sheer volume of data involved in each model, the redevelopment of previously inaccurate, slow and unstable excel models required copious historic data mapping and cleansing, creation of a master (access) database to house all volume data, individual back-end product databases to house and calculate tiered pricing impact at customer and sub-product level, along with vba driven front-end excel model templates that were sent out to the business for completion.
• Involved meeting with numerous product, client and finance managers to collect user and business requirements with the aim of creating models to easily capture variable pricing, volume, customer growth and incentive data to form part of the consolidated company P&L forecast.
Development and automation of all budget model Output files:
• VBA, Excel and Access-based add-on for various types of budget models, utilised by 50+ users across all departments.
• Automatically created a standardised output file from each model, auto-saved to BPC upload folder, converted into correct upload format, time/version/date-stamped, automated flag check to allow BPC administrators to allow/disallow uploads at certain times, and automatic reconciliation of output file to original model and to BPC upload, all via user-operated macro buttons.
• Involved liaising with SAP consultant, in-house BPC project manager, financial controller and numerous users to implement controls, work-arounds for system restrictions, and ensure accurate data handling.
Re-development and automation of Securitisation model:
• VBA and Excel based model built at customer, product, rate and trust level detail, for a 5 year (month level) forecast, used for budget process and for feasibility analysis by external business consultant.
• Contained numerous variables, complex formulae, in-built option to add future products/trusts and flexibility to change existing income and cost lines via drop-down boxes, various pivot-table based output perspectives, simplified one-tab input sheet, automated and instantaneous 3 dimensional profit and loss views.
• Involved liaising with finance, non-finance users and external consultants and providing technical support/analysis.
Redevelopment and automation of Operational Expenses model:
• One Excel and VBA based budget model created containing all cost data, and sent out to each individual manager to be used as cost-centre specific budget model for each of the 80+ cost centres.
• Involved complex formulae and data entry controls to provide cost-centre specific P&L view, comparison against prior year, automatic submission to appropriate finance team member, and unlocked data entry cells based on the specific cost-centre chosen in a dropdown cell.
Creation of Market Rent Template:
• VBA based, Excel model for Finance Manager, Non-Finance Executive and Divisional Manager use.
• Used by 10+ Finance Managers, and 30+ Leasing Executives/Site Managers to capture and consolidate detailed tenant sales and rental property information from multiple sources, and reconcile and create over 30+ site specific templates. Output used by Leasing executives and General & Divisional Managers for analysis of current market views, tenant affordability, site-layout profitability, effect of forecasted developments and other rental analytics and variance analysis.
• Involved advanced VBA and Excel formulae for the automatic consolidation and reconciliation of 3 different data sources into one workbook, customisable per site. An emphasis on clean and simple presentation of copious amounts of information, automated customised pivot-table output based on user-type selection in dropdown box, and ease of use for non-finance and non-spreadsheet users via buttons, dropdowns, data validation and other spreadsheet controls.
Creation of Marketing Forecast model:
• VBA based, Excel model for non-finance users in Retail Marketing division.
• Created for detailed monthly tracking of Budget, Forecast, Actuals and Purchase Order data from SAP downloads and non-finance user input.
• Involved complex VBA & Excel Formulae to automatically consolidate and reconcile 30+ marketing campaigns each with 30+ expense & revenue accounts, to SAP Actuals and Purchase Order transaction level downloads, and to allow non-finance users to easily track Current Balance, Re-Forecast and Roll-over data on a monthly basis with the click of a button.
Streamlining of existing Business Development model and Creation of Custom Scenario tool:
• VBA based, Excel ‘add-in’ for Retail Finance business development team and general streamlining of existing model.
• Custom Scenario Tool created for business development team for boardroom discussions regarding business opportunities.
• Involved streamlining model to drastically reduce calculation time, align with best modeling practice and to allow for the automatic creation and comparison of over 225, 3-variable Scenarios and their output, within minutes, by the press of a button. Custom Scenario tool could also be applied to virtually any existing Excel model by non-advanced users.
Creation of Management Reporting Pack:
-Excel, Hyperion Retrieve, VBA Automation based model for UK Finance
-Used to report detailed monthly Actual, Budget, Forecast and Variances to Senior Vice President of European Finance, the -Involved automating the retrieval of large amounts of data at a granular level from Hyperion and Dynamics and creating automated customised views and printouts to meet various reporting needs.
Automation of Employee Expenses, Concert Ticket payroll journaling, and Personnel Holiday Tracking model
-Excel based, VBA automated models used by Accounts personnel
-Used for the automatic creation of transaction line level journals and upload into Dynamics; reporting/tracking of personnel holiday/sick/training days with automatic upload into MS Outlook calendar for team viewing; Automatic logon and retrieval of reports from the Web Expenses website and creation of Journal for upload into dynamics.
-Involved building robust jounaling models in excel; complex visual basic processes to automate Outlook and Web Expenses website from Excel; and complex formulas to convert large amounts of data into journal lines entries.
Streamlining/Automation of upload, review and reporting process of detailed Actuals data.
-Excel, Jet Reports, VBA Automation based models for both Europe and UK finance teams
-Used to retrieve transaction level overhead Actuals data from Dynamics, consolidate with Budget/Forecast and upload into Hyperion; to facilitate month end accruals, re-forecasting and provide management reports for review by Senior staff.
-Involved creating Jet Reporting formulae, advanced excel formulae and VBA automation of pivot-table reports in multiple workbooks.
Development of Revenue Database
-Access, Excel, VBA automation based model for European Finance team.
-Used to consolidate multiple country templates with revenue and cost data, at transaction level, of the Income type Synchronisation and to allow for easy and consistent analysis and reporting across Europe.
-Involved creation of robust excel templates for completion by individual European countries to submit; automatic upload into Access database with user interface; and automatic output to Access reports or Excel based reports for analysis of top 3 earning deals, summarised revenue and profit, top clients etc.
Creation of Personnel Cost models
-Excel based personnel models for UK & European personnel.
-Used by the Senior Vice President of Finance and by HR for budgeting & costing analysis by person, department and country; and for variance analysis.
-Involved working with the Senior Vice President for Finance, and the VP of Human Resources, to apply varying payroll criteria by European country to a persons cost with complex excel formulae and an appreciation for discretion.
Analysis of transaction level income data and Creation of Income Type Model
-Excel based model used by UK finance team.
-Used to classify transaction level income to high-level income stream for easy analysis and comparison of income types and for forecasting/tracking purposes by the Finance Director and Management Accountant.
-Involved consolidation of disparate and copious historic income data into excel model for a view on how best to create a model going forward; subsequent creation of Income Type model; creation of Excel template for allocation of Income to Income stream and automatic excel journal creation for upload into Dynamics.
Built 3 tier Cost Benefit model Excel & VBA based
-Used to collate specific Cost Benefit projects templates and to automate upward consolidation to Line of Business level (for further input), and further automated consolidation to a summary level for the purposes of reporting to the company at a group level, tracking cost transformation and related Capital/Operational Expenditure across the company.
Design and creation of Cost transformation model. Excel & Access based
-Used for collection of activity, costing and driver data from all lines of the Business and consolidation at company's group level to determine target areas for cost transformation
Description: VBA automation of spreadsheet to track movement and create variance analysis.
Creation of automated excel-based template
Mapping of multiple sets of large data and comparison via access and excel
Education Summary: MICROSOFT OFFICE SPECIALIST London, UK (2007)
WORKERS EDUCATIONAL ASSOCIATION Sydney, Australia (2004)
Business Administration Certificates II and III
UNIVERSITY OF NEW ENGLAND by Correspondence (2002 / 2001) Part Time
Bachelor of Commerce - Deferred
Financial Modeler, Data Analyst, Excel, Access, and VBA Specialist, Developer