Automated Excel Inventory Spreadsheet

I run a business that needs a single, professional-grade Excel workbook to monitor both raw materials and finished products. The file must let me enter data once and have every key figure update automatically—stock on hand, incoming and outgoing quantities, MRP, batch numbers, and looming expiry dates.

Here is the scope as I see it:

• Build a clean master data sheet with dropdown-validated entry points so weekly updates stay error-free.
• Apply the right formulas, VLOOKUP/XLOOKUP, and dynamic named ranges so totals, reorder alerts, and cost calculations recalc instantly.
• Add Pivot Tables and slicers for quick analysis of current stock, soon-to-expire lots, and batch-specific movement.
• Present expiry tracking in a dashboard-style view that highlights items inside a user-set threshold (e.g., 30 days before expiry).
• Automate conditional formatting to flag negative stock, low levels, or mismatched batch numbers.
• Keep the entire workbook tidy—locked formulas, colour-coded input cells, and a menu sheet that guides any staff member through weekly updates.

When it’s finished, I’ll need:

1. The fully functional Excel file (no external add-ins, just standard Excel features).
2. A short walkthrough—either embedded notes or a separate sheet—explaining how to refresh data weekly and customise alert thresholds.

If you have questions about the data structure or additional automation ideas, let me know so we can refine the layout before you start coding the formulas.