Custom spreadsheets

Management spreadsheets

Spreadsheets for the daily routine of a small business.

4 spreadsheets and 1 cleaned customer database

MY ROLE

Personal project: structure, formulas, validation and formatting

HOW TO READ THE RESULT

Examples with fictitious companies and data. They show the kind of deliverable, not a client's results.

THE DECISION THAT MATTERS

Separate what is typed from what is calculated

Typed values are shown in blue; everything else is a formula. Whoever uses the spreadsheet knows where to edit, and totals, due dates and alerts update on their own.

The challenge

Bills, quotes, commissions and inventory often live in rushed spreadsheets with broken formulas and repeated data. The goal was to build templates that someone without technical knowledge can use every day.

The spreadsheets

Accounts payable and receivable spreadsheet with due dates, status, the situation of each bill and projected balance. — Accounts payable and receivable: the situation of each bill is calculated from the date at the top of the sheet, and the projected balance updates on its own. Open image at original size

Service quote spreadsheet with items, quantities, discounts and total. — Service quote: the service code fills in description, unit and price; discounts and total are calculated. Open image at original size

Sales commission spreadsheet with target percentage, tier, commission and bonus. — Commissions: the tier and rate come from an editable table, and the bonus goes to those who hit their target. Open image at original size

Inventory control spreadsheet with cost, price, margin and status of each product. — Inventory control: stock value, margin and a purchase alert when a product reaches its minimum. Open image at original size

Cleaning a customer database

A system export with names in different formats, phones and dates without a standard, values typed as text and repeated customers. The cleaned database has one customer per row, standardized fields and a notes column with what needs attention.

Comparison between the original, messy customer database and the cleaned, standardized one. — Before and after: 14 rows from the original export become 10 unique customers, with duplicates merged and missing data flagged. Open image at original size

Companies, people and amounts are fictitious.

The result

Ready-to-use templates where the user types only what is needed and the calculations, alerts and totals update on their own.

Explore the technical details

VLOOKUP to fill in quote items and find the commission tier, SUMIFS for totals, conditional formatting for due dates, targets and minimum stock, and validation lists for bill status and service codes. The files only use features compatible with Excel, Google Sheets and LibreOffice.

Talk about a spreadsheet

Next project: Credit data pipeline Discuss an opportunity