Workbook structure, tables, references and calculation order

What you will learn

Build a transparent line calculation and understand how references behave when copied.

Key ideas

A reliable workbook starts with a clear row definition. At Harbor Office Supply, one row is one transaction line with one product. Transaction ID is unique, while customers and products repeat. A total of 600 rows is therefore 600 lines, not necessarily a universal definition of 600 customer orders. In this dataset each line is also its own transaction, so completed transaction count is a valid denominator for this exercise.

Separate supplied values from calculated values. Quantity, unit price, discount rate, unit cost and status are inputs. Gross sales, recognized revenue, recognized cost and gross profit are outputs. The course excludes tax, shipping and returns. A canceled transaction retains its original quantity and price for traceability but contributes zero recognized revenue and cost.

Relative references move when copied. Absolute references such as $B$2 stay fixed. A structured reference such as [@Quantity] means the quantity on the current table row. Tables improve readability and can extend calculations when new records are added. Parentheses make business intent explicit: quantity times price times one minus discount.

Your practice

Calculate M2:Q601, format currency to two decimals and margin as a percentage, and explain the canceled-row policy in one sentence.

Download Lesson 1.1 - Practice Pack.zip from the Practice resources tab. Save a working copy, fill the blank formula cells, then compare with the Completed workbook in the pack.

Common mistake to avoid

Do not overwrite the source unit price with a net price. Otherwise the discount can be applied twice.

Check your understanding

Why does =E2*F2-10% fail to apply a 10% discount?

Optional AI extension

Ask an AI assistant to explain the exact formula you used and propose one boundary test. Supply only synthetic fields. Record its suggestion, an independently calculated expected result, the observed result and your accept or reject decision. No AI account is required to complete the core exercise.