Sensitivity analysis for a drinks project

What it is and why readers ask for it

Every model rests on assumptions you cannot prove before you trade: price, volume, cost, timing. Sensitivity analysis answers a simple question: if this number is wrong by 10 or 20 percent, does the project still work?

Credit officers and investors almost always ask. A report that has already done it saves a round of questions and shows the promoters know their own weak points. For how to choose the base case, see making assumptions defensible.

A worked example, step by step

This is a hypothetical in neutral units. A project costs 100. It returns cash of 30, 35, 40, 40 and 40 over five years. At a 12 percent discount rate the net present value (NPV) is about 31.

Now test one input at a time:

• All cash flows 10 percent lower: NPV about 18.
• All cash flows 20 percent lower: NPV about 5.
• Project cost 10 percent higher, at 110: NPV about 21.
• Discount rate 10 percent: NPV about 38. Discount rate 20 percent: NPV about 8.

The project stays positive in every case, but a 20 percent shortfall in cash leaves very little margin. The cash flows would have to fall by about 24 percent for NPV to reach zero, because the five flows are worth about 131 today against 100 invested and 100 divided by 131 is about 76 percent. That 24 percent is the margin of safety.

Which inputs to test in a drinks project

Pick the ones that move the answer most and are least certain. For a brewery or distillery these are usually:

• Net selling price, after trade margin and tax.
• Volume and the speed of ramp-up.
• Raw material cost, especially imported malt or glass.
• Plant cost and delay in commissioning.
• Loss and yield through the process.
• Interest rate on the loan.

Price and volume do not hurt equally. Hypothetically, with a price of 10, variable cost of 6 and 100,000 litres a month, contribution is 400,000. A 5 percent price cut leaves 350,000, a fall of 12.5 percent. A 5 percent volume cut leaves 380,000, a fall of 5 percent. Price cuts hurt more because variable costs do not shrink with them.

Delay is a sensitivity too

Many models treat a six-month delay in licences as a footnote. Test it. A delayed start means months of rent, salaries and loan interest with no sales. If a brewery has 15 units of fixed cost a month, a six-month delay costs 90 before the first sale. Put that into the cash flow and see whether the working capital line still holds.

In India, where licensing is a state matter and timelines vary, delay is often the largest single risk to the early cash. Check timings with the regulator or an adviser rather than assuming.

From single tests to scenarios

One-at-a-time tests miss combinations. Real bad years bring several problems together: slow sales, higher costs and a late start. Build three scenarios: base, downside and upside. Define each by a short list of changed inputs, not by mood. Report debt service cover and NPV for each.

A downside case that still repays the loan is a strong answer to a lender. A downside case that does not repay is information too. It tells you to raise equity, phase the build or reduce the size. See writing a DPR a bank will accept.

How to present it

Use a small table. Rows are the inputs tested. Columns show the change, the NPV and the repayment cover. Highlight the ones where the result falls below an acceptable line. Add two sentences on what you would do if that happened. A tornado chart, which ranks inputs by effect on NPV, helps if the report is read on screen.

Do not drown the reader. Five to eight well-chosen tests beat forty.

Say what you would do in each bad case. If volume falls 20 percent, perhaps you defer the second packaging line. If plant costs rise 10 percent, perhaps you phase the cold room. A test with a response shows management. A lender reading it sees a plan rather than a warning.

Keep the model honest

Sensitivity only works if the model is built properly. Each assumption sits in one cell, each calculation links to it and nothing is typed over. If changing the selling price does not change the loan cover, something is hard-coded. Test this yourself before you send the file: change one input, watch every output that should move and check that it does.

Common questions

What is the difference between sensitivity analysis and scenario analysis?

Sensitivity changes one input at a time to see its effect. Scenario analysis changes several inputs together to describe a coherent good or bad year.

How much should I vary each input?

Use a range that is plausible for that input, often 10 to 20 percent. Justify it. A wider range for volume and a narrower one for plant cost is common.

What is a margin of safety?

It is how far an input can fall before the project stops meeting a chosen test, such as NPV reaching zero or debt cover falling to 1.

Can I do this in a spreadsheet?

Yes. If every calculation links to an assumptions sheet, changing one cell and recording the result is enough.