The table is needed only in Microsoft Excel!
The table must work correctly on both Windows Microsoft Excel and Microsoft Excel for Mac:
General structure:
• The Excel file must have 11 separate sheets, each of which will record the total + add new ones (so the template can be added to new sheets).
• There must be a Summary Sheet that combines data from all 11 sheets and automatically updates when information changes.
• There must be a 2nd Summary Sheet that combines data from all 11 sheets and automatically updates when information changes, where only the client's name and total quantity will be displayed (Example Company and company 10 pcs * 60 = 600)
General scheme:
Date ——> Receipt ——> Balance ——> Return ——> Date ——> Shipments ——> Quantity * pcs = total ——> Percentage ——> Received ——> Percentage —→ Received minus percentage ——> Date ——> Received ——> Percentage between Shipments and Received
Fields (columns) on each sheet:
Date (light gray color) — Date of receipt.
Receipt (soft green color) — Amount of money received in the account.
**Date** (soft orange color) — Date of transaction.
Shipments (soft orange color) — Quantity of units and total related to shipments.
Quantity (light blue color) — Quantity of units involved in the transaction.
Total Amount (pale purple color) — Total amount calculated for each transaction.
Commission (light yellow color) — Amount of commission related to the transaction.
Received (after commission) (light green color) — Amount received after deducting the commission.
Percentage between Shipments and Received (light brown color) — Percentage ratio between the sent and received amounts, calculated using the formula (Received / Sent) * 100.
Colors do not have to be such that they are readable and not harsh on the eyes. Colors should combine blocks that are related to each other.
Requirements:
• Automation: The Summary Sheet must automatically update when data changes on any of the 11 separate sheets.
• Formatting: It is important to ensure color compatibility, readability, and lack of brightness so that all information is easily perceived.
• Scalability: Ability to add new sheets while maintaining all settings and automation.
Steps for implementation:
Create the structure and layout for each of the 11 sheets with the specified fields.
Apply color formatting to each of the fields.
Add a new column with the percentage calculation based on the formula (Received / Sent) * 100.
Set up automatic data updates on the Summary Sheet.
Conduct testing to verify the correctness of all functions and automatic updates.
The scheme is approximate; during execution, we may change something around (Started with a draft of the scheme, and then we will decide).
