Excel Cost & Pricing Engine for a Home Healthcare Business (Phase 1)
I am looking for an experienced Excel specialist to build Version 1 of an operational costing system for my home healthcare business.
The goal is not to build a complete ERP or a complex business intelligence solution.
The objective is to create a fast, reliable Excel workbook that helps me make better pricing decisions every day.
The workbook must run entirely inside Microsoft Excel (Office 365) without external software, databases, or third-party add-ins.
⸻
Project Goal
The workbook should instantly answer questions like:
* What is the real cost of this medical service?
* How much profit will I make?
* What is my operational cost?
* What is the travel cost?
* Can I offer a discount?
* What is the maximum discount I can give before losing money?
* What is the minimum profitable selling price?
The workbook should become the company’s daily pricing and profitability tool.
⸻
Scope (Version 1)
1. Medical Inventory
Create a simple inventory database.
Each item should include:
* Item Code
* Item Name
* Category
* Unit
* Purchase Price
* Supplier (optional)
Inventory prices must automatically update service costs.
⸻
2. Medical Service Recipes (Bill of Materials)
Each medical service should have its own configurable recipe.
Examples:
* IV Therapy
* Injection
* Blood Collection
* Wound Care
Each recipe should allow multiple medical supplies with configurable quantities.
The workbook should automatically calculate the material cost of each service.
⸻
3. Operational Costs
Create a simple section where I can record business expenses such as:
* Fuel
* Vehicle Maintenance
* Insurance
* Accounting
* Phone
* Internet
* Marketing
* Software
These costs should automatically be included in the service costing.
⸻
4. Vehicle Cost Calculator
Calculate:
* Cost per Kilometer
* Travel Cost
* Round Trip Cost
Travel expenses must automatically be included in every home visit.
⸻
5. Service Cost Calculator
The calculator should allow me to enter:
* Medical Service
* Distance (km)
* Additional Materials (optional)
The workbook should automatically calculate:
* Material Cost
* Travel Cost
* Operational Cost
* Total Cost
* Selling Price
* Gross Profit
* Profit Margin
⸻
6. Pricing & Discount Engine
This is the most important part of the project.
The workbook should automatically calculate:
* Recommended Selling Price
* Minimum Profitable Price
* Current Profit
* Current Margin
* Maximum Safe Discount
If the entered selling price falls below profitability, the workbook should clearly warn the user.
Example:
- Healthy Margin
- Low Margin
- Loss / Unprofitable Service
The goal is to prevent pricing services below cost.
⸻
7. Simple Dashboard
A single-page dashboard showing only essential KPIs:
* Monthly Revenue
* Monthly Profit
* Number of Visits
* Average Profit Margin
* Average Cost per Visit
* Total Travel Cost
No advanced reporting or Power BI is required in Version 1.
⸻
Technical Requirements
* Microsoft Excel 365
* Structured Tables
* Modern Excel formulas
* XLOOKUP
* LET / LAMBDA where appropriate
* Power Query is welcome but optional
* No VBA unless absolutely necessary
* No external add-ins
⸻
User Experience
The workbook should be:
* Simple
* Fast
* Easy to maintain
* Easy to update
* Suitable for daily use
Important formulas should be protected from accidental changes.
⸻
Documentation
Please include a short user guide explaining:
* How to update inventory prices
* How to create a new medical service
* How to modify operational costs
* How to use the pricing calculator
⸻
Acceptance Criteria
The project will be considered complete when:
* Service costs are calculated correctly.
* Inventory prices automatically update service costs.
* Travel costs are calculated correctly.
* Operational costs are included in the calculations.
* The pricing engine calculates profit, margin, minimum profitable price, and maximum safe discount.
* The dashboard displays the correct KPIs.
* The workbook is documented and easy to maintain.
⸻
Long-Term Opportunity
This is Phase 1 of a larger project.
If the collaboration is successful, additional paid phases will include:
* Customer management
* Visit management
* Advanced dashboards
* Business intelligence
* Forecasting
* Automation
* AI-assisted reporting