Technical Task
Project: procurement accounting and demand forecasting system based on Google Sheets or your proposal
Goal: to create a single table for tracking inventory, sales, goods in transit, and orders from various suppliers, so that the system automatically identifies items that need to be reordered.
1. General Description
A system needs to be developed based on Google Sheets for managing the procurement of goods across various:
brands
models / SKUs
suppliers
orders
deliveries in transit
The system should allow manual data entry and updates, as well as automatically calculate the need for procurement based on inventory levels, sales, delivery times, and goods in transit.
2. Core Functionality
2.1. Product Directory
A separate sheet with a list of products, where the following is stored:
2.2. Inventory and Sales Accounting
A separate sheet for current data for each SKU:
stock on hand
quantity of sold goods for the selected period
number of days in the period
average daily sales (ADS)
Formula:
ADS = Sales for the period / Number of days in the period
2.3. Accounting for Goods in Transit
A separate sheet for all open deliveries:
The system should account for not all goods in transit as a total amount, but only those that will arrive before the date of the new delivery.
2.4. Accounting for Supplier Orders
A separate sheet for tracking all purchases:
order number
creation date
supplier
brand
SKU
model
quantity
purchase price
total
expected arrival date
status
3. Forecasting and Procurement Logic
The system should automatically determine which items need to be ordered based on the following calculations:
3.1. Average Daily Sales
ADS = Sales / PeriodDays
3.2. Safety Stock
SafetyStock = ADS × SafetyDays
3.3. Demand for the New Delivery Period
DemandDuringLeadTime = ADS × LeadTimeDays
3.4. Incoming Goods that Will Arrive Before the New Delivery
IncomingBeforeNewArrival = sum of all shipments in transit where ETA <= date of new order arrival
3.5. Projected Stock on the Date of New Delivery
NetStockByArrival = OnHand + IncomingBeforeNewArrival - (ADS × LeadTimeDays)
3.6. Condition for Ordering Need
If:
NetStockByArrival <= SafetyStock
then the system should mark the item as needing to be ordered.
3.7. Recommended Order Quantity
TargetStock = ADS × (LeadTimeDays + ReviewPeriodDays + SafetyDays)
OrderQty = TargetStock - NetStockByArrival
If the value is less than 0 — set to 0.
3.8. Final Order Quantity
The system must consider:
The final quantity should be rounded up to the required multiple.
4. What the System Should Display
For each product, the system should automatically display:
current stock
average daily sales
goods in transit
goods in transit that will arrive on time
safety stock
projected stock on the date of new delivery
status:
recommended quantity to order
final quantity to order
5. Required Sheets in Google Sheets
The system must contain at least the following sheets:
Products — SKU, brand, model, supplier directory
Sales / Inventory — inventory and sales for the period
In Transit — all active deliveries with ETA
Supplier Orders — order log
Procurement / Recommendations — automatic sheet with items to order
Settings — analysis period, review period, standard lead times, buffers
6. Automation
It is necessary to implement:
formulas in Google Sheets
convenient dropdown lists for brands, models, suppliers, statuses
conditional formatting:
red — critical
yellow — order soon
green — all good
filtering by suppliers, brands, statuses
automatic list of items to order
7. Expected Outcome
The output should be a ready system in Google Sheets, where you can:
manage products of various brands and models
manage orders from various suppliers
track deliveries in transit
see the actual procurement needs
receive automatic recommendations on what, when, and in what quantity to order
I can also immediately prepare for you a more "business-like" version of the technical task for transfer to the developer or integrator, so that it can be sent to work.