I85PREP
⌘ K
CAPE Information Technology Unit 1 study materials
CAPE Information Technology Unit 1 study resources

The Hardware Chain That Runs on One Workbook

CAPE Information Technology Unit 1 · Put your knowledge to work in context.

All case studies

The Hardware Chain That Runs on One Workbook

Island Build Supplies runs three hardware stores from a single Excel workbook kept on a shared network folder. It holds about 12,000 product lines and 38,000 invoice lines; recalculating it takes roughly 45 seconds, and staff save a copy each morning named final_v7, final_v8 and so on. Two people editing at once have twice produced a file that opens with a 'repaired records' warning. Supplier names appear as 'Carib Supply', 'Caribbean Supplies Ltd' and 'CARIB SUPPLY CO', so the same supplier is paid three times in reports. Stock figures disagree with shelf counts by 5–10 per cent, and closing the month takes three days of manual matching. The owner wants to keep Excel and pay someone to write macros that 'tidy it up'. The business has no IT staff, a server that runs the shared folder, and three staff who are comfortable with spreadsheets but have never used a database.

1. Advise the owner on whether macros in the existing workbook can solve the problem, or whether a database is justified. Support your answer with the symptoms given.

Read the worked answer

What macros can fix: repetitive formatting, import of supplier price lists, and generating a standard report. That is real but peripheral value.

What macros cannot fix, mapped to the symptoms:

  • Duplicated suppliers. The root cause is that supplier name is stored as free text in every product and invoice row. A macro can standardise spelling today, but nothing prevents a user typing a new variant tomorrow. The fix is a separate Supplier table with a primary key and a lookup from Product - a structural change, not a cosmetic one.
  • Concurrent editing and 'repaired records'. Excel's sharing model is file-level. Two editors plus a copy-per-day versioning habit means there is no single source of truth and no record of who changed what. A database server handles concurrent transactions and locking properly.
  • 45-second recalculation over 38,000 rows. A spreadsheet recalculates dependent cells in memory; an indexed query returns a stock position in milliseconds because it does not load the whole dataset.
  • 5–10 per cent stock drift. Stock is an accumulation of movements. It needs a StockMovement record per transaction, enforced referential integrity and a rule that prevents selling more than is on hand; a spreadsheet cannot enforce that across three sites.
  • Three-day month-end. This is the cost of manual reconciliation of inconsistent keys.

Judgement: keep Excel as the analysis tool - connect it to the database for pivot-table reporting - but move storage and transaction entry to a relational database. Macros on the current workbook would add maintenance cost, still allow duplication, and leave the corruption risk in place.

2. Identify the main entities, attributes and relationships for the replacement database, and explain how the design prevents the supplier duplication problem.

Read the worked answer

Entities and key attributes

  • Supplier (SupplierID PK, Name, Contact, PaymentTerms) - one row per real-world supplier.
  • Product (ProductCode PK, Description, Unit, ReorderLevel, SupplierID FK).
  • Branch (BranchID PK, Location, Manager).
  • StockItem (ProductCode FK, BranchID FK, QtyOnHand) - composite key, because the same product sits in three stores with different quantities.
  • Invoice (InvoiceNo PK, Date, CustomerID FK, BranchID FK, StaffID FK).
  • InvoiceLine (InvoiceNo FK, LineNo, ProductCode FK, Qty, UnitPrice) - composite key; the line, not the invoice, carries the price actually charged.
  • StockMovement (MovementID PK, ProductCode, BranchID, Date, QtyChange, Reason) - the audit trail that lets QtyOnHand be rebuilt and reconciled against shelf counts.

Relationships: Supplier 1:M Product; Product 1:M StockItem; Branch 1:M StockItem; Invoice 1:M InvoiceLine; Product 1:M InvoiceLine; Product and Branch 1:M StockMovement.

How duplication is prevented

Supplier details are stored once and referenced by SupplierID. The data-entry form uses a dropdown driven by the Supplier table, and referential integrity stops a product being saved with a SupplierID that does not exist. Name spelling lives in one field, so correcting 'Carib Supply' to 'Caribbean Supplies Ltd' corrects every report at once. If a supplier can trade under several names, model that as a separate SupplierAlias table rather than by duplicating rows. Reports then group on SupplierID, and the three-way split disappears.

3. Outline a migration and control plan the owner could actually follow with three non-technical staff, including the risks you would manage.

Read the worked answer

Migration plan

  1. Clean before you move. Extract the distinct supplier names, agree one canonical list, assign SupplierIDs, and standardise product codes. Migration exposes bad data rather than fixing it.
  2. Build a small prototype with two or three tables and the invoice-entry screen, and let the staff who will use it try entering a real day's invoices. Their objections are cheapest to fix now.
  3. Load and reconcile. Import opening balances and stock counts, then prove that the total value of stock and the number of invoice lines match the old workbook exactly.
  4. Parallel run for one month at one branch, with the spreadsheet kept as a read-only reference. Compare daily takings and stock movements before switching the other two branches.
  5. Train on tasks, not features - entering an invoice, receiving a delivery, running a stock report - and nominate one staff member as the local first-line support.
  6. Back up nightly and test a restore once in the parallel period.

Risks and controls

  • Data loss during import → keep the original workbook untouched and archived; reconcile totals.
  • Staff reverting to spreadsheets → withdraw write access to the old folder after cut-over, and provide a read-only reporting view via Excel so the familiar tool is still available.
  • No IT support → choose a mainstream database with a hosted or managed option, document the schema, and budget for a support contract rather than relying on one person's macros.
  • Corrupt or unvalidated entries → input masks, validation rules, referential integrity, and role-based user accounts so stock adjustments are logged against a named user.