Excel-Based Financial Costing Model Design

Project Overview:
We are seeking a skilled freelancer to develop a standardized pricing tool that will be used for upcoming projects and opportunities. This tool will streamline and standardize the structured input sheets from various departments, including Engineering, Operations, Managed Services, Service and Maintenance, and Account Management.
The initial concept of the tool will be integrated with Power Query to form a comprehensive cost model, providing end customer pricing, financial dashboards, and more.
Responsibilities:
- Develop pricing sheets to accommodate different procurement methods, pricing structures, and output pricing methods.
- Create project sheets to capture project stages and milestones.
- Design finance sheets to manage cost multipliers, margins/markups, discounts, BAFO allowances, overrides, CPI over term, and insurance allowances.
- Import these sheets via Power Query into a master sheet.
- Integrate input sheets to:
o Create a cost model
o Provide end customer pricing
o Generate financial dashboards and reports
o Integrate timelines within the Project Plan
o Serve as an internal negotiation tool for pricing adjustments
o Provide ongoing cost tracking and reporting
o Allow for future price changes
Solution Structure:
Input Sheets:
- Project Phases / Milestones
- Infrastructure
o Outright Purchase (Quantity price breaks)
o Lease (Quantity and Lease Term price breaks)
- Software
o Outright Purchase
o License Subscription (Qty and Term breaks)
- Services / Effort
o Cloud Solutions (Data movement variables, CPU cycles, Data Storage Variables)
o Once off effort (Project delivery / implementation Lump sum of effort)
o Periodic effort over a defined duration
- Finance Global Variable Matrices
o Supplier / work package variables (Markup / Margin, Discount, BAFO Allowance, Risk Allowance, Insurance)
o Financing costs based on categories
o CPI
- Finance Overrides
o Override line-item markup, discount, categories, work packages
- Capture clear lists of Assumptions; Exclusions; and Inclusions, from the user inputs
Output Dashboards and Views:
- Financial:
o Supplier Cost Summary (Totals & Over time: Labour, Hardware / SW Purchase, Lease / Software Subscription / ongoing licenses)
- Internal Summaries:
o Risk Allowances
o Profit (estimate)
o Wiped / Discounted cost / reduced margins (see Finance Overrides)
o Budgets
 Departmental effort over time
 Hardware supply
 External Services
 Invoicing Milestones
 Timeline
• Outgoings (Purchases, Services, Internal Effort)
• Incomings (Project Milestone / Phase payments, Managed Services, Lease payments)
• Debit repayment forecast
- Integration with Project Tools:
o Timesheet
o Cost Tracker (Project Phase / Stage overview, Basic Gantt)
o Customer Pricing Tables (Qty break downs, Lease Term Breakdowns)
o Lists of Assumptions, Inclusion, Exclusions, Risks (Pricing)
Qualifications:
- Proven experience in developing complex financial tools and models.
- Proficiency in Power Query and Excel.
- Strong understanding of financial principles and cost modelling.
- Excellent communication skills and attention to detail.
- Ability to work independently and meet project deadlines.
Application Process:
Interested freelancers are invited to submit their proposals, including a portfolio of relevant work and a brief description of their approach to this project.