Securing the value in a cell

Franko76 

To handle the price change scenario and ensure that past sales calculations remain unaffected by future cost changes, you can implement a system where the cost prices are recorded based on the sales date. One way to achieve this in Excel is by using a dynamic approach with tables and additional columns to handle multiple cost periods.

Here's a step-by-step guide to set this up:

Step 1: Setup Your Sheets

Cost Sheet:

Sales Sheet:

Result Sheet:

Step 2: Implement a Lookup System for Costs Based on Date

1. Adding Date Columns to Sales Sheet: Add a corresponding date row to your sales data so you can track when each sale occurred.
2. Using VLOOKUP with Approximate Match: Use a formula that considers both the product and the sale date to determine the correct cost.

Step 3: Create the Necessary Formulas

Result Sheet Formulas:

1. Total Sold:

=SUM(B2:G2) 

2. Revenue:

=B2 * VLOOKUP(A2, 'Cost'!$A$2:$D$1000, 2, TRUE)

This will look up the sell price based on the product name and date.

3. Cost:

=B2 * VLOOKUP(A2, 'Cost'!$A$2:$D$1000, 3, TRUE)

This will look up the buy price based on the product name and date.

4. Profit:

=C2 - D2

Step 4: Dynamic Lookup

For the dynamic lookup to work correctly, ensure your data in the Cost sheet is sorted by date in ascending order. The VLOOKUP function with TRUE as the last argument performs an approximate match, which returns the largest value that is less than or equal to the lookup value (i.e., the date of the sale).

Step 5: Example Implementation

In the 'Sales' sheet:

• Assume the dates for sales are in row 3 (below the sales data).

In the 'Result' sheet:

• For Revenue (Column C):

=SUMPRODUCT(B2:G2, VLOOKUP(A2, 'Cost'!$A$2:$D$1000, 2, TRUE))

This will sum the product of each sales amount and the corresponding sell price.

• For Cost (Column D):

=SUMPRODUCT(B2:G2, VLOOKUP(A2, 'Cost'!$A$2:$D$1000, 3, TRUE))

This will sum the product of each sales amount and the corresponding buy price.

Note: Adjust ranges $A$2:$D$1000 as needed to cover your entire dataset.

Step 6: Ensure Data Integrity

• Make sure to keep the 'Cost' sheet sorted by date to ensure the correct price is fetched.

By using this approach, you can dynamically adjust the cost and sell prices based on the date of the sale, ensuring that past sales calculations remain accurate even if costs change during the season. The text, steps and formulas was created with the help of AI

Hope this will help you.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.