Securing the value in a cell

Copper Contributor

Hi! 

 

Say I have a small shop at a sports stadium, and want to calculate profits on each product I sell for the season. 

 

The way I've done it is having three sheets; 'sales', 'cost' and 'result'. 

 

In the 'cost' sheet, I have 

A1 product name

B1 amount I sell it for

C1 amount I bought it for

 

In the 'sales' sheet, I have

Column A: Product names

Column B-> G: Opponent for the team

Row 2: Number of sales

 

Example:

A2: Popcorn B2: 52 (sales vs Miami), C2: 15 (sales vs Tampa) etc

 

In the 'result' sheet, I have:

A2 Popcorn

B2 The amount of popcorn sold in total (B2:G2 from 'sales')) 

C2: B2*'cost'!b1 (amount of popcorn x sale price) 

D2: B2*'cost'!c1 (amount of popcorn x amount I purchased the popcorn for) 

E2: C2-D2 (turnover minus cost) 

 

All this is fine, and this is obviously a simplified version of it. 

 

BUT! Halfway through the season, my provider increased their prices so my total result will be lower. If I change the prize on 'cost' C1, it will change all of the sales I've had, including the time before the price went up. 

 

Is there a way that I can freeze the values in the cells from the beginning of the season so it's not affected by the changing of the purchasing price moving forward? 

 

Basically, if I buy the popcorn for $5 and sell it for $10 in May, but buy it for $6 and sell it for $10 in June that will have an effect on my total revenue. 

 

I hope this made sense and that anyone can help me! 

 

Cheers, 

Frank

 

 

 

3 Replies

@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:

A

B

C

D

Product

Sell

Buy

Date

Popcorn

10

5

1/1/2024

Popcorn

10

6

6/1/2024

Sales Sheet:

A

B

C

D

E

F

G

Product

Miami

Tampa

Atlanta

New York

Boston

Dallas

Popcorn

52

15

20

10

30

25

Date

1/15/24

2/20/24

3/15/24

6/5/24

7/10/24

8/15/24

Result Sheet:

A

B

C

D

E

Product

Total Sold

Revenue

Cost

Profit

Popcorn

=SUM(B2:G2)

=B2*price

=B2*cost

=C2-D2

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.

@NikolinoDE

 

Thanks! 

 

I hoped it could be as easy as to lock some cells so they wouldn't be updated, but dreaded the solution would be complicated.

 

Thank you so much for the answer, I'll try it out! 

 

If you are ok with locking cells, you can always save your formula in a cell (just add a single quote in front of it) set the cost for the first half of the season, copy the relevant cells and past values so they will no longer calculate, then update your cost to the next value and the formulas calculate until the next cost change. There are many ways to adjust the formula to not require a workaround like this, but it will do if you are dealing with data that really doesn't change except for the addition of new information in the existing structure.