Forum Discussion
Securing the value in a cell
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
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
- Adding Date Columns to Sales Sheet: Add a corresponding date row to your sales data so you can track when each sale occurred.
- 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.
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.
