Cash flow modelling in excel

Go with the cash flow: Calculate NPV and IRR in Excel

Have you been losing sleep figuring out the best way to maximize profitability and minimize risk on your business investments? Stop tossing and turning. Relax and go with the flow.

Cash, that is. Take a look at your cash flow, or what goes into and what goes out of your business. Positive cash flow is the measure of cash coming in (sales, earned interest, stock issues, and so on), whereas negative cash flow is the measure of cash going out (purchases, wages, taxes, and so on). Net cash flow is the difference between your positive cash flow and your negative cash flow, and answers that most fundamental of business questions: How much money is left in the till?

To grow your business, you need to make key decisions about where to invest your money over the long term. Microsoft Excel can help you compare options and make the right choices, so that you can rest easy both day and night.

Asking questions about capital investment projects

If you want to take your money out of the till, make it working capital, and invest it in the projects that make up your business, you need to ask some questions about those projects:

Is a new long-term project going to be profitable? When?

Is the money better invested in another project?

Should I invest even more in an ongoing project, or is it time to cut my losses?

Now take a closer look at each of those projects, and ask:

What are the negative and positive cash flows for this project?

What impact will a large initial investment have, and how much is too much?

In the end, what you really need are bottom-line numbers that you can use to compare project choices. But to get there, you must incorporate the time value of money into your analysis.

My papa once told me, «Son, it’s better to get your money as soon as possible and hold on to it as long as possible.» Later in life, I learned why. You can invest this money at a compounded interest rate, which means that your money can make you more money — and then some. In other words, when cash goes out or comes in is just as important as how much cash goes out or comes in.

Answering questions by using NPV and IRR

There are two financial methods that you can use to help you answer all of these questions: net present value (NPV) and internal rate of return (IRR). Both NPV and IRR are referred to as discounted cash flow methods because they factor the time value of money into your capital investment project evaluation. Both NPV and IRR are based on a series of future payments (negative cash flow), income (positive cash flow), losses (negative cash flow), or «no-gainers» (zero cash flow).

NPV returns the net value of the cash flows — represented in today’s dollars. Because of the time value of money, receiving a dollar today is worth more than receiving a dollar tomorrow. NPV calculates that present value for each of the series of cash flows and adds them together to get the net present value.

The formula for NPV is:

Where n is the number of cash flows, and i is the interest or discount rate.

IRR is based on NPV. You can think of it as a special case of NPV, where the rate of return that is calculated is the interest rate corresponding to a 0 (zero) net present value.

When all negative cash flows occur earlier in the sequence than all positive cash flows, or when a project’s sequence of cash flows contains only one negative cash flow, IRR returns a unique value. Most capital investment projects begin with a large negative cash flow (the up-front investment) followed by a sequence of positive cash flows, and, therefore, have a unique IRR. However, sometimes there can be more than one acceptable IRR, or sometimes none at all.

Comparing projects

NPV determines whether a project earns more or less than a desired rate of return (also called the hurdle rate) and is good at finding out whether a project is going to be profitable. IRR goes one step further than NPV to determine a specific rate of return for a project. Both NPV and IRR give you numbers that you can use to compare competing projects and make the best choice for your business.

Choosing the appropriate Excel function

hich Office Excel functions can you use to calculate NPV and IRR? There are five: NPV function, XNPV function, IRR function, XIRR function, and MIRR function. Which one you choose depends on the financial method that you prefer, whether cash flows occur at regular intervals, and whether the cash flows are periodic.

Note: Cash flows are specified as negative, positive, or zero values. When you use these functions, pay particular attention to how you handle immediate cash flows that occur at the beginning of the first period and all of the other cash flows that occur at the ends of periods.

Use when you want to

Determine the net present value using cash flows that occur at regular intervals, such as monthly or annually.

Each cash flow, specified as a value, occurs at the end of a period.

If there is an additional cash flow at the start of the first period, it should be added to the value returned by the NPV function. See Example 2 in the NPV function Help topic.

Determine the net present value using cash flows that occur at irregular intervals.

Each cash flow, specified as a value, occurs at a scheduled payment date.

Determine the internal rate of return using cash flows that occur at regular intervals, such as monthly or annually.

Each cash flow, specified as a value, occurs at the end of a period.

IRR is calculated through an iterative search procedure that starts with an estimate for IRR — specified as a guess — and then repeatedly varies that value until a correct IRR is reached. Specifying a guess argument is optional; Excel uses 10% as the default value.

If there is more than one acceptable answer, the IRR function only returns the first one it finds. If the IRR doesn’t find any answer, it returns a #NUM! error value. Use a different value for the guess if you get an error or if the result is not what you expected.

Note A different guess might return a different result if there is more than one possible internal rate of return.

Determine the internal rate of return using cash flows that occur at irregular intervals.

Each cash flow, specified as a value, occurs at a scheduled payment date.

XIRR is calculated through an iterative search procedure that starts with an estimate for IRR — specified as a guess — and then repeatedly varies that value until a correct XIRR is reached. Specifying a guess argument is optional; Excel uses 10% as the default value.

If there is more than one acceptable answer, the XIRR function only returns the first one it finds. If the XIRR doesn’t find any answer, it returns a #NUM! error value. Use a different value for the guess if you get an error or if the result is not what you expected.

Note A different guess might return a different result if there is more than one possible internal rate of return.

MIRR function (values, finance_rate, reinvest_rate)

Determine the modified internal rate of return using cash flows that occur at regular intervals, such as monthly or annually, and consider both the cost of investment and the interest that is received on the reinvestment of cash.

Each cash flow, specified as a value, occurs at the end of a period, except the first cash flow, which specifies a value at the beginning of the period.

The interest rate that you pay on the money that is used in the cash flows is specified in finance_rate. The interest rate that you receive on the cash flows as you reinvest them is specified in reinvest_rate.

More information

To learn more about using NPV and IRR, see Chapter 8, «Evaluating Investments with Net Present Value Criteria,» and Chapter 9, «Internal Rate of Return,» in Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston. to learn more about this book.

Источник

13 Week Cash Flow Model (TWCF)

• 
• 
• 
• 

What is a 13 week cash flow model

As the name suggests, a 13-week cash flow model is a weekly cash flow forecast. The 13 week cash flow uses the direct method to forecast weekly cash receipts less cash disbursements. The forecast is frequently used in turnaround situations when a company enters financial distress in order to provide visibility into the company’s short-term options.

How the 13 week cash flow model is used in practice

In the example below, shutter-maker American Home Products filed this 13 week cash flow (“TWCF”) to support their request for a $400,000 Debtor-in-Possession (DIP) revolver in court:

AHP’s TWCF shows the company expects to need the additional financing almost immediately on June 7, 2019, followed by a second DIP draw July 5, 2019

While every 13-week cash flow model will show receipts and disbursements that are unique to its business and circumstances, most thirteen week cash flow models follow a generally similar structure:

Structure of a 13 Week Cash Flow Forecast.

Download the Free 13 Week Cash Flow Model Excel Template

Enter your name and email in the form below and download the free 13-Week Cash Flow Model Excel template:

The 13-week cash flow model is a tool for decision making

By identifying the immediate cash flow needs at the most granular level, the model helps distressed firms evaluate the immediate impact of a variety of possible operational, financial, and strategic remedies:

Operational	Financial	Strategic
Improving the speed of collections Cutting payroll (i.e. reducing headcount) Delaying supplier payments Liquidating inventory Selling assets	Seek additional funding sources Negotiate with creditors Delay debt related payments	Attempt a turnaround out of court File for bankruptcy (Chapter 11 or Chapter 7) M&A: Seek a buyer or strategic investor

Why is the TWCF So Important?

A credible TWCF often quite literally is the difference between survival and Chapter 7 liquidation.

The reality for many liquidity-constrained companies under financial distress is that even if they are viable as a going concern in the long run, they must convince prepetition lenders or a third party to extend debtor-in-possession (DIP) financing to bridge to a medium term and ultimately a long term plan. Securing this financing is almost always supported by credible 13-week cash flow forecast.

The TWCF is designed to increase the level of transparency and trust between management, creditors and other stakeholders.

13 Week Cash Flow is a Tool for Decision Making

Learn to Build a 13-Week Cash Flow Model From Scratch

Get the same training we deliver to some of the world’s leading turnaround consulting & advisory firms, investment banks and distressed debt funds.

Modeling an integrated 13 Week Cash Flow Model

As I mentioned, every thirteen-week cash flow model is unique, but there are several common elements you will encounter in nearly every model.

13 Week Cash Flow Model Structure

The 13 Week Cash Flow Output

The 13 week cash flow output is the star of the show. It is a summary of cash receipts and cash disbursements over a 13-week period (usually with 1 week of actuals). The bottom of the summary will usually contain a cash forecast that identifies any additional revolver or DIP financing required to maintain a desired minimum cash balance. The screenshot of AHP’s 13 week cash flow above is an example of such a summary. In order to arrive at this summary, however, the other elements of the model below need to be constructed.

Cash to EBITDA Reconciliation

While the focus of the TWCF is on cash, reconciling the weekly cash forecast to a weekly EBITDA forecast helps management and other stakeholders connect the dots from management’s profit forecasts which are used to support a sale or plan from emergence from bankruptcy to the company’s short term liquidity issues.

Example of an EBITDA to Cash Reconciliation in a 13 Week Cash Flow Model

Working capital roll-forwards

Forecasts for balance sheet items, most notably working capital items are critical for a 13 week cash flow model. Assumptions about the timing of near term vendor payments, payroll, and inventory purchases often have material impact on the 13 week cash flow model. A properly constructed TWCF will reflect those assumptions in a “roll forward” – which identifies how key balance sheet items change week by week.

The Roll-Forward Summary Output:

Accounts receivable roll-forward

Opening balances will usually come from A/R aging. Forecasts for future A/R driven off days sales outstanding (DSO) and even invoice-level assumptions for larger customers. Once combined with revenue forecasts, cash receipt projections can be made:

Inventory roll-forward

Historical inventory will usually comes from a company’s inventory ledger. The roll-forward adds inventory purchase forecasts and subtracts COGS forecasts (projected on the income statement). The purchase forecast is arrived at by forecasting inventory turnover / or days of inventory on hand (DIOH). Notice the inventory roll has no impact on cash disbursements directly – only indirectly via the AP roll-forward (below).

Accounts Payable Roll-Forward

Inventory purchases are referenced from the inventory roll-forward and inventory payments are back-solved based on both days payable outstanding (DPO) assumptions as well as vendor specific invoice reviews.

Accrued Wages Roll-Forward

Accrual-based wage expense forecasts come from the income statement. The roll-forward is then reduced by cash disbursement forecasts for wages. Because these are contractually defined payments, disbursements are usually fairly predictable and companies can generated them from their payroll systems. Accrued wages and benefits often represent the largest disbursement.

Borrowing base (revolver) modeling

For companies that are running out of cash, existing lines of credit and revolving credit facilities are often the last line of defense. However, these facilities are usually constrained by complicated borrowing base formulas and other limits that can materially reduce additional cash availability. Being able to model the actual availability a company has will critical for quantifying the amount of unmet funding needs requiring either DIP financing or an alternate strategy.

Additional TWCF Model Features

In addition to the elements discussed above, building an integrated 13-week cash flow model often involves the following modeling mechanics:

• Timing: Companies usually forecast on a monthly, quarterly or even an annual basis. Arriving at weekly basis forecasts thus often requires converting longer term forecasts.
• Weekly Updating: Unlike monthly, quarterly or annual models which have longer gaps between updates, the 13 week cash flow must be updated weekly. Every update adds risk of model error so it is important to construct a 13 week cash flow in a way that doesn’t break the model every time you update it
• General Ledger and Accounts Mapping: One of the most time consuming parts of modeling the 13 week cash flow is identifying, aggregating and re-framing client data. Often the historical data that you need in order to build the 13 week cash flow model is scattered, incomplete with inconsistent (or outright incorrect) general ledger and expense categories. Understanding Excel’s data and reference functions can dramatically improve productivity when working with messy client data.

Источник