Forecast commodity price cycle - Time and price variables

Hello everyone,

I am scratching my head over how to compute the turnover of a farm according to commodity cycles.

I have attached the spreadsheet for a better understanding. Thank you for your help!

My question is in 2 parts:

1/ Income depends on beef prices and costs depend on animal feed prices, who both evolve according to cycles.

I have the commodity cycle time (number of years), the various price signals (peaks and troughs), and the current prices.

I would like to find a formula that would automatically change prices over time according to the cycle. So far, I used

=IF(COUNTA(number of cells from start point)<Number of years in cycle, Previous price *(1+annual change),Previous price*(1-annual change))

This create first the ascending part of the cycle, and when the number of years since start is higher than the number of years of the cycle, it reverses and comes back down.

My problem is that I cannot figure out a way to make it go back up again.

Is there an easier formula to compute this kind of data? Or should I create some kind of super complicated formula?

2/ the second question is regarding the point of the price within the cycle.

Is it be possible to enter the position of the price within the cycle? For example, we already are 3 years into a 5 years cycle, meaning the cycle has 2 more years before reversing.

Please let me know if you need additional information / clarification.

Thanks a lot anyways,

Carlito

Commodity cycle.xlsx

As to question #1, try putting this formula in Model!C4 and dragging it across:

Please Login or Register  to view this content.

Let me know if you have any questions.

Hello JeteMc!

Sorry for my late reply. This formula works perfectly.

Thank you very much for your precious help,

Best regards,

Carlito

You're welcome and thank you for the feedback. Hope that you have a nice day.