Customer Ledger & Sales Analysis

I have several messy Excel workbooks where all transaction data sits in a single sheet. Every row does not cleanly map to a standard “individual transaction” or “summary by supplier”, so the first task is to tidy and structure that information.

Once the data is properly formatted, I need a comprehensive customer ledger built inside the same file. Each worksheet in the finished workbook should focus on a single supplier and list every product we buy from them, showing purchase price, sales price and the running total of units sold.

After the ledger is in place, I want three clear analytics views created with formulas, PivotTables or Power Query—whatever approach you think best suits a native-Excel workflow:

• A ranked list of the top five customers by total sales value
• Identification of the single highest-selling product across the data set
• A concise, filterable city-wise sales summary

Everything must remain refreshable so that when I paste new raw data in the main sheet, all dependent sheets and summaries update automatically without manual intervention.

Deliver the cleaned workbook, documented transformation steps (so I can repeat them) and a short note explaining any assumptions made during data standardisation.