The fee register of a real shop

The situation

Ayesha runs a stitching shop in Faisalabad with about thirty regular customers. Orders and payments are written in a diary, and at the end of the month she cannot say who still owes her, which areas her customers come from, or how much actually came in.

Build the register she can keep herself — one she could hand to her cousin for a week without explaining it.

What you'll be able to show

• Turn a messy diary into a clean, formula-driven register
• Answer real questions with COUNTIF, SUMIF and a lookup — never a typed-in number
• Build a pivot and prove its total against a plain SUM
• Hand over a workbook a stranger can read

What you need

• Excel 2016 or later, or Google Sheets
• Thirty invented but realistic orders (names, areas of Faisalabad and Lahore, items, amounts)

Tasks

1. 1Build the register — dirty first
   On a sheet called Orders, enter thirty orders with the columns Date, Customer, City, Item, Amount, Paid (Yes/No) and Balance. Make Balance a formula (Amount if Paid is No, otherwise 0). Then dirty it deliberately, as a real diary is: three spellings of Faisalabad, two duplicate rows, and four amounts typed as text with "Rs" in front.
   A correct result: Thirty rows, Balance calculated by a formula, and SUM(Amount) is WRONG because four amounts are text.
2. 2Clean it on a copy
   Copy the sheet to Orders_clean. Fix the spellings (Find and Replace), TRIM stray spaces, remove the two duplicates with Data → Remove Duplicates, and convert the four text amounts to numbers. Keep Orders untouched.
   A correct result: SUM(Amount) now works and is the true figure; a note lists the nine fixes you made.
3. 3Answer three questions with formulas
   On a sheet called Answers: how many orders came from Faisalabad (COUNTIF), the total amount from Lahore (SUMIF), and the total balance still unpaid (SUMIF on Paid = No). Each answer must be a formula that changes when the data changes.
   A correct result: Three cells with formulas in them. Change one order's city and watch the first answer move.
4. 4Look up prices
   Add a Prices sheet with eight items and their standard price. In Orders_clean, add a Standard price column filled by XLOOKUP or VLOOKUP. Leave one item out of the price list on purpose and show "not found" with IFERROR instead of #N/A.
   A correct result: The lookup column is filled for every row; the missing item shows the words not found.
5. 5The pivot, reconciled
   Build a pivot table from Orders_clean showing the number of orders and total Amount by City. Compare the pivot's grand total with a plain SUM of the Amount column.
   A correct result: The two totals are identical. If they are not, something in the cleaning is still wrong — find it.
6. 6Make it handover-ready
   Add a title and today's date at the top, freeze the header row, put units in the headers (Amount (Rs)), and write a Notes sheet that explains every column in one line each. Check print preview fits one page wide.
   A correct result: Somebody who has never seen the shop can read every sheet without asking you a question.

What to hand in

The workbook (a file or a share link with view access), and the Notes sheet containing the nine cleaning fixes and the three answers written out in words.

How it is marked