The Quagmire Challenge: Market Check-up with Dr. Copper
Sometimes when doing valuation work in finance or accounting, you get an answer that is vastly different than what the current market is assuming. A common situation involves the intrinsic valuation on a publicly traded company or a project. The value you derive from doing a discounted cash flow analysis (DCF) or net present value (NPV) calculation might be significantly different than what the current share price is in the market.
When this happens, our first thought is that we probably made a mistake in our calculation somewhere. However, if our calculations are correct then we must be using very different assumptions.
One such situation is in the resources world, such as mining or oil and gas. In these industries, the forecasted commodity price that is used in the model (gold, silver, copper, oil, etc.) could greatly impact the final valuation.
In this challenge, your task is to find out what the implied assumption for the commodity price is given a current share price of the company and a stream of cash flows. Click here to download the file.
We started off by forecasting the unlevered free cash flows of a simple copper mining company with a single mine that will be depleted by the end of 2028. Based on our assumption of a flat $3.00/lb copper price, we calculate a net present value of the company of $2.39/share. This intrinsic value is at a discount to the actual share price of the company of $3.00 per share.
Your task is to dynamically figure out what the implied commodity price the market is assuming when valuing the company. In our example, we know it must be higher than our estimate of $3.00/lb since the current share price is higher. We also assume that public shareholders are using the same assumptions as us for variable costs, capital expenditures, taxes, discount rate, etc. Price will be the only variable we change.
This analysis can be performed by trial and error or using Goal Seek, however, we want you to configure the model so that it is always showing the implied copper price being discounted or implied in the current stock price. We want to do this without a macro either.
Have fun and good luck. The top solutions will receive a Marquee prize pack and winners will be announced in the September edition of The BenchMarq. Submit your answer to email@example.com, subject: Quagmire #8.
In this challenge, we presented a simple model for a copper mining company which calculated an equity value of $2.39/sh using a flat copper price of $3.00/lbs. Participants were asked to have the model dynamically calculate the copper price implied by the observed stock price of $3.00/sh.
The winning solutions that we selected were able to solve for the implied copper price even when the cash flow profile for the company was modified.
One of the winning solutions calculated the NPV of key line items through the model like capex, changes in working capital and fixed and variable costs. The solution started with equity value, solved for enterprise value and then back calculated up through the rows to arrive at the correct implied copper price of $3.22/lb. Since NPV functions were used throughout the solution, this method was able to work on a changing cash flow profile for the company.
We also had a few submissions that used self-referencing functions. Winners were selected for the solutions which work if the stock price was either below or above the equity value. One of the winning submissions had a self-referencing formula in Cell D3 which is similar to the one shown below. This formula moves the copper price towards the implied price in increments of $0.001/lb.
=IF( Stock Price > Equity Value, D3 + 0.001 , IF( Equity Value > Stock Price, D3 – 0.001, D3))
Another winning solution had a self-referencing formula in Cell D3 which was similar to the one shown below. This formula starts by calculating the difference between the stock price and the equity value. This difference is then multiplied by a convergence factor of 0.2 and added to Cell D3. This moves the copper price in the correct direction towards the solution of $3.22/lb.
=D3 + 0.2 * (Stock Price – Equity Value)