Custom Excel Financial Model Creation
Budget: £250 – £750 GBP
1. Project Goal and Context
The primary goal of this project is to develop a robust, flexible, and customer-facing financial model in Microsoft Excel to accurately estimate the cost of fulfilment services provided by a Third-Party Logistics (3PL) provider.
The model must serve two functions:
Pricing: Calculate estimated costs for prospective customers based on a set rate card and their unique operational profile.
Forecasting: Feed the calculated pricing into a monthly or annual forecast structure to project total cost of ownership.
2. Deliverables (The Excel Model)
The successful Bidder will deliver a fully functional, well-documented, and user-friendly Excel workbook (.xlsx) containing the following minimum tabs/sections:
2.1. Rate Card Input Tab
A dedicated tab where all 3PL service unit costs (rate card) are defined and easily updated.
Required Rate Card Inputs (examples):
Cost per pallet/location for Monthly Storage Retention.
Cost per unit for Inbound Processing / Goods In.
Cost per order for Base Pick/Pack.
Cost per line item picked (Additional Item Fee).
Cost per Kilogram/Weight Band (Shipping/Carrier Cost Matrix).
Cost per specific packaging material (e.g., box, mailer).
2.2. Customer Data Input Tab
A clean, structured tab where the user inputs all required customer operational data points (detailed in Section 3.0). All calculations will reference these cells.
2.3. Forecast Calculation Tab
The engine of the model, calculating the total monthly and annual costs based on the inputs from 2.1 and 2.2.
Calculations must be clearly linked and auditable.
2.4. Customer Presentation Output Tab
A professional, summarized view of the forecast, suitable for presentation to a potential customer.
This tab should clearly display:
Total Projected Annual Fulfilment Cost.
Breakdown of costs by service line (e.g., Storage, Inbound, Pick & Pack, Shipping).
Key assumptions and input metrics (e.g., SKUs held, annual orders).
3. Key Customer Data Inputs (Mandatory)
The model must be designed to accept and utilize the following data points from a potential customer to generate the cost forecast:
Input Metric
Description
Inbound Stock on Day 1
Initial quantity of units arriving at the warehouse (for one-off inbound processing cost calculation).
Monthly Stock Retained
Average number of storage units (pallets, bins, shelves) required monthly.
Monthly Restock
Average quantity of units restocked per month (for recurring inbound processing).
Total SKUs Held
The total number of unique product items (Stock Keeping Units) to be stored.
Quantity of Each SKU
The typical quantity breakdown across the SKUs (e.g., 50% of stock is SKU A, 30% is SKU B, etc.).
Forecasted Orders per Annum
Total number of customer orders projected over a 12-month period.
Seasonal/Monthly Order Split
Assumed percentage split of annual orders across the 12 calendar months (to facilitate monthly forecast accuracy).
Average Weight of Orders
The mean outgoing weight per customer order (used for shipping cost calculation).
Average Items per Order
The mean number of line items or units contained within a single customer order.
4. Pricing Methodology and Logic
The model will operate based on a direct multiplication methodology:
Logic: Customer Input Metric (X) multiplied by the Rate Card Unit Cost (Y) equals the Service Line Cost (Z).
Example: (Forecasted Orders per Annum) × (Cost per Order for Base Pick/Pack) = Annual Pick/Pack Cost.
Forecast: All calculated costs must be spread across a 12-month forecast based on the provided Seasonal/Monthly Order Split input.
Total Cost: The final model must sum all individual cost components (Storage, Inbound, Pick/Pack, Shipping, Materials) to produce the total projected cost of fulfilment.
5. Bidder Qualifications
The successful Bidder must demonstrate the following experience:
Required Expertise: Proven experience in fulfilment, logistics, or supply chain service pricing methodology.
Technical Proficiency: Advanced expertise in Microsoft Excel, including proficiency with data tables, linked formulas, logical functions (IF, SUMIFS), and clean data visualization/presentation.
6. Project Management and Documentation
6.1. Documentation
A supplementary document (.pdf or Word document) or a dedicated tab within the Excel file must be provided, detailing:
Model structure and formula explanations.
Instructions for updating the Rate Card and Customer Data Inputs.
6.2. Key Milestones (To be defined with the selected Bidder)
Phase 1: Model structure and input sheet development.
Phase 2: Core calculation logic implementation and formula validation.
Phase 3: Final presentation tab design and comprehensive testing.
Phase 4: Handover and final documentation delivery.
The primary goal of this project is to develop a robust, flexible, and customer-facing financial model in Microsoft Excel to accurately estimate the cost of fulfilment services provided by a Third-Party Logistics (3PL) provider.
The model must serve two functions:
Pricing: Calculate estimated costs for prospective customers based on a set rate card and their unique operational profile.
Forecasting: Feed the calculated pricing into a monthly or annual forecast structure to project total cost of ownership.
2. Deliverables (The Excel Model)
The successful Bidder will deliver a fully functional, well-documented, and user-friendly Excel workbook (.xlsx) containing the following minimum tabs/sections:
2.1. Rate Card Input Tab
A dedicated tab where all 3PL service unit costs (rate card) are defined and easily updated.
Required Rate Card Inputs (examples):
Cost per pallet/location for Monthly Storage Retention.
Cost per unit for Inbound Processing / Goods In.
Cost per order for Base Pick/Pack.
Cost per line item picked (Additional Item Fee).
Cost per Kilogram/Weight Band (Shipping/Carrier Cost Matrix).
Cost per specific packaging material (e.g., box, mailer).
2.2. Customer Data Input Tab
A clean, structured tab where the user inputs all required customer operational data points (detailed in Section 3.0). All calculations will reference these cells.
2.3. Forecast Calculation Tab
The engine of the model, calculating the total monthly and annual costs based on the inputs from 2.1 and 2.2.
Calculations must be clearly linked and auditable.
2.4. Customer Presentation Output Tab
A professional, summarized view of the forecast, suitable for presentation to a potential customer.
This tab should clearly display:
Total Projected Annual Fulfilment Cost.
Breakdown of costs by service line (e.g., Storage, Inbound, Pick & Pack, Shipping).
Key assumptions and input metrics (e.g., SKUs held, annual orders).
3. Key Customer Data Inputs (Mandatory)
The model must be designed to accept and utilize the following data points from a potential customer to generate the cost forecast:
Input Metric
Description
Inbound Stock on Day 1
Initial quantity of units arriving at the warehouse (for one-off inbound processing cost calculation).
Monthly Stock Retained
Average number of storage units (pallets, bins, shelves) required monthly.
Monthly Restock
Average quantity of units restocked per month (for recurring inbound processing).
Total SKUs Held
The total number of unique product items (Stock Keeping Units) to be stored.
Quantity of Each SKU
The typical quantity breakdown across the SKUs (e.g., 50% of stock is SKU A, 30% is SKU B, etc.).
Forecasted Orders per Annum
Total number of customer orders projected over a 12-month period.
Seasonal/Monthly Order Split
Assumed percentage split of annual orders across the 12 calendar months (to facilitate monthly forecast accuracy).
Average Weight of Orders
The mean outgoing weight per customer order (used for shipping cost calculation).
Average Items per Order
The mean number of line items or units contained within a single customer order.
4. Pricing Methodology and Logic
The model will operate based on a direct multiplication methodology:
Logic: Customer Input Metric (X) multiplied by the Rate Card Unit Cost (Y) equals the Service Line Cost (Z).
Example: (Forecasted Orders per Annum) × (Cost per Order for Base Pick/Pack) = Annual Pick/Pack Cost.
Forecast: All calculated costs must be spread across a 12-month forecast based on the provided Seasonal/Monthly Order Split input.
Total Cost: The final model must sum all individual cost components (Storage, Inbound, Pick/Pack, Shipping, Materials) to produce the total projected cost of fulfilment.
5. Bidder Qualifications
The successful Bidder must demonstrate the following experience:
Required Expertise: Proven experience in fulfilment, logistics, or supply chain service pricing methodology.
Technical Proficiency: Advanced expertise in Microsoft Excel, including proficiency with data tables, linked formulas, logical functions (IF, SUMIFS), and clean data visualization/presentation.
6. Project Management and Documentation
6.1. Documentation
A supplementary document (.pdf or Word document) or a dedicated tab within the Excel file must be provided, detailing:
Model structure and formula explanations.
Instructions for updating the Rate Card and Customer Data Inputs.
6.2. Key Milestones (To be defined with the selected Bidder)
Phase 1: Model structure and input sheet development.
Phase 2: Core calculation logic implementation and formula validation.
Phase 3: Final presentation tab design and comprehensive testing.
Phase 4: Handover and final documentation delivery.