Project: monthly sales report
From: Rosa Delgado, sales manager, Harbor & Pine Office Supply (a fictional company)
About: March order report
Here is the March order export: twenty orders, four regions. Before Friday's meeting I need:
- revenue on every order, and the month's total;
- revenue, share of total, order count and average order value for each region;
- who made the single largest order;
- the large orders (2,000 or more) flagged so I can thank those customers.
Please build it with formulas, not typed numbers, so I can paste April's export over it next month.
This project builds a complete one-page report from a raw list. Each step adds one piece, and the next step starts where the last one ended.
You'll use the skills from the Beginner and Intermediate units together: row-by-row formulas filled down, SUM, SUMIF and COUNTIF with locked ranges, a percent of total, AVERAGEIF, a lookup of the largest value, and IF.
The point of building with formulas is reuse: when next month's data is pasted into columns A to G, every number in the report updates by itself. Hard-typed results would have to be redone by hand.
=SUMIF($C$2:$C$21,K5,$H$2:$H$21)
=L5/$L$2
Locked ranges, a relative criteria cell, and a locked total: the two patterns this report repeats.