Comprehensive Sales-Purchase Tracker Spreadsheet

I need a single, well-structured spreadsheet where I can enter and later update every sale and purchase in one place. The sheet must include columns for transaction type, date, item description, quantity, amount, customer or supplier name, payment method, and payment status, with running totals that instantly show revenue, costs, and net margin.

Please add data-validation drop-downs for payment status and method, clean date and number formatting, and conditional formatting that flags overdue or unpaid items. A compact summary—pivot table or similar—that rolls up monthly totals and outstanding balances will keep the overview simple.

Once the layout is finished, I’ll send roughly 500 lines of historic data for you to import and align with the same structure. Everything should work seamlessly in Excel; if you can keep it compatible with Google Sheets, even better.

Acceptance criteria
• One combined worksheet containing all listed columns, clearly labeled
• Correct, automatically updating formulas for totals and margins
• Validation, drop-downs, and conditional formatting stable when new rows are added or the file is copied
• Historic data imported without errors and matching the new format

If you can suggest lightweight charts or a mini-dashboard without cluttering the main sheet, I’m open to it.