Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Tracking version and calculating variance

45 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi, I'm trying to build a project cost tracker but I'm reasonably new to airtable. The issue I'm having is that as the project evolves, the costs change (as requirements change etc.). The business wants to be able to see that variance. Currently, when costs change, a new cost estimate record is created. All are linked back to the opportunity (our proposal to the client). However, I can't think of how to calculate the cost variance by cost centre automatically.  

An example of what I'm trying to achieve is below:

 

 

Opportunity (LINKED TO RECORD WITH PRICE TO CLIENT)

COST CENTRE 1

COST CENTRE 2

COST CENTRE 1 CHANGE FROM THE PREVIOUS ESTIMATE

COST 2 CENTRE CHANGE FROM THE PREVIOUS ESTIMATE

Cost Estimate V1 – Deal 1

DEAL1

£500

£100

N/A

N/A

Cost Estimate V2 – Deal 1

DEAL1

£600

£50

£100

-£50

Cost Estimate V1 – Deal 2

DEAL2

£200

£200

N/A

N/A

Cost Estimate V2 – Deal 2

DEAL2

£300

£200

£100

£0
1 Reply 1

I think you could achieve this by creating a linked field that links to the same table called "Previous Estimate" or something and link, say, "Cost Estimate V2 - Deal 1" to the record "Cost Estimate V1 - Deal 1"

You'd then use lookup fields to pull the previous estimate's data over, and then use the formula fields to find that difference