The workbook is effectively a costing and pricing model with three core calculation layers:
### 1. Raw Materials
Sheet: **`C- Raw Products Costs`**
Table: **`StraightGrain`**
This is the base input table.
Main fields:
| Area | Purpose |
| --- | --- |
| Raw Materials | Ingredient / material name |
| Market Value Uncleaned Grain | Base cost |
| Unit of Measure | kg, per tonne, bag, etc. |
| Kg per unit of measure | Converts purchase unit into kg |
| Waste % of Loss | Loss / shrinkage assumption |
| Loss | Formula-driven cost loss |
| Cost per Unit of Measure | Base cost + loss |
| Cost per Kg | Final reusable cost input |
Core formula pattern:
```
Loss = Market Value * Waste %
Cost per Unit = Loss + Market Value
Cost per Kg = Cost per Unit / Kg per Unit
```
This should become the foundation table in the app, with price history and versioning.
---
### 2. Mix Master
Sheet: **`M - All`**
Table: **`AllMix`**
This is the recipe / bill-of-materials table.
Main purpose:
| Area | Purpose |
| --- | --- |
| Client | Product/customer grouping |
| Product | Mix name |
| Total KGs per mix | Formula sum of all ingredients |
| Ingredient columns | Quantity of each raw material in the mix |
| Mix cost section | Calculates cost per mix and cost per kg |
Core formula pattern:
```
Total KGs per mix = SUM(all ingredient quantities)
Ingredient Cost = Ingredient Quantity * Raw Material Cost per Kg
Total Mix Cost = SUM(all ingredient costs)
Mix Cost per Kg = Total Mix Cost / Total KGs per mix
```
This should become a structured recipe module, not a wide spreadsheet table. In the app, each mix should have many ingredient rows rather than 60+ ingredient columns.
Important workbook comments found here:
| Location | Comment |
| --- | --- |
| `M - All!B8` | Need to confirm recipe |
| `M - All!D10` | Mix is 280, but bulka fits 550, therefore 275 for 2 batches |
| `M - All!AB53` | 85 same as Mayreef, was 110 according to files |
These should become data-quality notes or review flags in the app.
---
### 3. Product / Unit Cost / Price
Sheet: **`Product Cost - Price`**
Table: **`ProductCost`**
This is the main output table.
Main fields:
| Area | Purpose |
| --- | --- |
| Client | Pricing group |
| Item ID | Xero/product identifier |
| Name | Product name |
| Mix / Product Name | Links back to Mix Master |
| Standard or Bulka | Packaging / sale type |
| Own Bag | Packaging logic |
| Std Unit of Measure | Usually kg, bag, tonne, etc. |
| Items Per Pallet | Used in freight calculations |
Packaging Cost = Based on Standard / Bulka / Own Bag / Bag Size
Freight Cost = Based on pallet, kg, product type, or special case
Finished Product Delivered =
Cleaned Product
+ Process Cost
+ Packaging Cost
+ Freight Cost
Selling Price =
Finished Product Delivered / (1 - Margin)
```
Important workbook comments found here:
| Location | Comment |
| --- | --- |
| `Product Cost - Price!R4` | 20kg bag or by KG if Ton |
| `Product Cost - Price!X14` | Includes 24% profit. Cost per tonne $68 |
| `Product Cost - Price!C50` | In OLD - Ian McKay Stock Mix 20kg |
These should also become structured notes / flags.
---
## Recommended app direction
Build the app around **controlled costing scenarios**, not just data entry.
The client should enter raw material prices, recipes, product settings, packaging assumptions, freight costs, and margin rules. The backend should calculate final unit cost and pricing outputs. Power BI should consume clean, flattened API/database views.
The most important design decision:
> The spreadsheet formulas should move into the FastAPI backend, not the Svelte frontend.
>
The frontend should display inputs, validation warnings, scenario comparisons, and results. The backend should own the calculation engine, audit trail, versioning, and API outputs.
---
# Proposed system architecture
```
Svelte App
|
|-- Client Portal
| |-- Manage raw materials
| |-- Manage mix recipes
| |-- Manage products
| |-- Run price simulations
| |-- Submit / approve changes
|
|-- Consultancy/Admin Portal
|-- Review client changes
|-- Manage assumptions
|-- Compare scenarios
|-- Export / publish to Power BI
|-- Audit history
FastAPI Backend
|
|-- Auth + tenant access
|-- Data validation
|-- Cost calculation engine
|-- Scenario/version control
|-- Xero item mapping
|-- Power BI API endpoints
|-- Audit logging
Database
|
|-- PostgreSQL recommended
|-- SQLite acceptable only for prototype
```
---
# Core modules
## 1. Authentication and tenancy
Because this is consultancy-built software for a client, the app should support separation between:
| Role | Access |
| --- | --- |
| Client User | Enter and update data |
| Client Manager | Approve changes and scenarios |
| Consultancy User | Review, correct, simulate, publish |
Every calculated product should return a transparent cost build-up.
Example output:
```
{
"product_id":123,
"cleaned_product_cost":0.5444,
"grading_cost":0.055,
"bagging_cost":0.04,
"cracking_cost":0,
"bag_cost":0.63,
"freight_cost":1.45,
"finished_product_delivered":14.31,
"distributor_price":18.46,
"wholesale_price":null,
"warnings": []
}
```
---
## 5. Assumptions module
The spreadsheet has embedded assumption tables around process costs, packaging costs, freight costs, and margins. These should not remain hidden in formulas.
That gives the client safer data entry, gives the consultancy control and visibility, and gives Power BI a clean source instead of fragile workbook logic.
---
# Frontend layout debugging notes
## Full-height layouts inside padded shells
When a child layout uses negative margins to cancel a parent container's padding, `height: 100%` is often not enough to visually fill the container.
Example pattern:
```css
.parent{
--content-padding:1.34rem;
padding:var(--content-padding);
}
.child{
margin:calc(var(--content-padding)*-1);
height:100%;
}
```
This can leave a visible gap at the bottom because the child is still only `100%` tall while being visually expanded outward by the negative margins.
Preferred fix:
```css
.child{
margin:calc(var(--content-padding)*-1);
height:calc(100%+(var(--content-padding)*2));
min-height:calc(100%+(var(--content-padding)*2));
}
```
Worker reasoning rule:
- If a panel "almost" fills the viewport but leaves a strip equal to parent padding, inspect negative margins and the nearest padded scroll container before changing inner child heights.
- In this app, `AppSecondaryRailLayout.svelte` sits inside `ClientShell.svelte`'s padded `.content` container, so full-height fixes should account for `--content-padding`.
