Corporate Finance

Mastering Dynamic Summing in Excel with OFFSET and MATCH

by

If you need to sum a variable number of cells, such as calculating a year-to-date total for a monthly budget (e.g., from month 1 to month 5), here’s an efficient way to do it.

Step 1: Define Your Parameter

Start by defining the parameter. This could be the month number or the last day of the month, from which you can extract the month number using the formula MONTH().

Step 2: Use the SUM and OFFSET Functions

To achieve this, combine the SUM function with the OFFSET function. Here’s how OFFSET works:

  • Offset: You provide a reference range, and OFFSET shifts the starting point by a specified number of rows or columns.
  • Size Constraints: You can also define the number of rows and/or columns to include in the new range, which is key for our situation.

Example:

Here’s how it looks in action:

Let’s have a look at the formula :

There are certainly other ways to achieve this, but this method works well and is straightforward. Do note that you’ll need an up-to-date version of Excel to use the OFFSET function.

‌

Going Deeper:

To make it more dynamic, you can also use the MATCH function to select a specific category, such as “Commerce” instead of “Marketing.” This adds flexibility, allowing you to fine-tune what data you want to sum.

This proves to be useful when you want to compare year-to-date Actuals with year-to-date Budget at the end of a month.

Adjust a monthly budget in a second

by

Sometimes you need to adjust a monthly budget for a certain item. For instance, let’s say you want to change the total budget from €2,000k to €2,150k.

Here’s a quick way to do it:

  1. Calculate the Adjustment Factor: Compute the ratio of the new total to the old total:

Adjustment Factor = 2,000 / 2,150 = 1.075

  1. Copy the Adjustment Factor: Copy the cell containing the value 1.075.
  2. Select the Monthly Budget Cells: Highlight the 12 cells that contain the monthly budget values.
  3. Apply the Adjustment Factor:
    • Right-click on the selected cells.
    • Choose Paste Special > Paste Special.
    • In the dialog box, select “Multiply” and “Values”.
    • Click OK.


By multiplying all the monthly budget cells by 1.075, your annual budget will be adjusted to €2,150k:

The Various Ways to Value a Company

by

This is the first post in a series about company valuation. We’ll start by exploring the different methods to value a company and then dive deeper into each one.

Method 1: The Discounted Cash Flow (DCF) Method

The DCF method views the company as a cash flow machine. Here’s how it works:

  1. Forecast the Cash Flow: Project the company’s future cash flows.
  2. Discount the Cash Flow: Apply the WACC (Weighted Average Cost of Capital) to discount the projected cash flows.
  3. Sum the Discounted Cash Flows: Sum these discounted cash flows over a specific period to determine the Enterprise Value.

If you’re unfamiliar with WACC, don’t worry—we’ll cover it in more detail later.

This will give you the Enterprise Value. If you want the Equity Value, you will need to substract the Net Debt (which is Debt – Cash and cash equivalents).

Equity Value = Enterprise Value - Net Debt

Method 2: The Multiple Method

This method involves comparing the company to others, either with historical purchase of companies known to the public or with publicly traded companies:

  1. Find Comparable Companies: Look for publicly known historical purchases or publicly traded companies.
  2. Determine Multiples: Calculate the Enterprise Value of these companies (Equity Value + Net Debt), then derive an EBITDA or EBIT multiple.
  3. Apply the Multiple: Use this multiple to compute the Enterprise Value for the company in question.
  4. Calculate Equity Value: Subtract the Net Debt from the Enterprise Value to get the Equity Value.

Method 3: The Balance Sheet Approach

For companies that don’t generate significant cash, their value might lie in their assets:

  1. Assess Assets: Evaluate the market value of the company’s assets, including brands, tangible assets, intangible assets, patents, or shares in other companies.
  2. Sum the Asset Values: Add up the market value of all assets.
  3. Subtract Liabilities: Subtract the total liabilities from the sum of the asset values to get the company’s value.

We won’t delve deeply into this method as it heavily depends on the specific company and the market value of its assets.

Divergent Results

When valuing a company using the DCF and Multiple methods, you might get different results. It’s crucial to understand why there are discrepancies rather than just averaging the outcomes from both methods.

Stay tuned for more detailed posts on each valuation method!

Debt amortization

by

Understanding how to compute the monthly reimbursement amount of a debt is crucial for financial planning. Let’s explore two methods to achieve this:

Method 1: The Formula

Using a straightforward formula, you can easily calculate the monthly reimbursement amount.

Méthode 2: Debt Amortization Schedule

This method involves computing the amount of capital reimbursed and the interest paid each month. Let’s create a table with headers and rows for each month (N = 60 for a 5-year term). The table will have six columns:

  • Period: Sequential month number
  • Beginning Loan Balance: Ending balance from the previous month
  • Payment: Total Payment – Interest
  • Interest: Calculate the interest paid
  • Total Payment: we will find the correct value using Goal Seek
  • End Loan Balance: Beginning balance minus the capital reimbursed

See below the formulas for each column:

To find the monthly reimbursement amount of €1,797, we use the Goal Seek function. Set the capital due to zero at the end of the period, and let Goal Seek adjust the monthly reimbursement amount accordingly.

With these calculations, you can simulate the financial results for any given period. For a P&L simulation, consider the interest paid. For cash flow or balance sheet simulations, use both the capital reimbursed and the interest paid.

Note: If you have a grace period (e.g., 6 months without payments), you can easily incorporate this into your simulation.

EBITDA, EBIT, Free cash flow and Cash flow

by

EBITDA: Earnings Before Interest, Taxes, Depreciation and Amortization

EBITDA = Revenue – Costs of Goods Sold – Variable Costs – Fixed Costs (excl. depreciation and amortization of fixed assets, financial results and income taxes)

It is often used when we want a short answer to the question “How much this company is valued?” It depends on the company’s industry but a number we often multiply the EBITDA by 7 to get the Enterprise Value. This gives you:

Equity Value = Enterprise Value - Net Debt with Enterprise Value = 7 * EBITDA and Net Debt = Debt - Cash and cash equivalents

‌

EBIT: Earnings Before Interest and Taxes

EBIT = EBITDA - Depreciation and Amortization of fixed assets

‌

Free cash flow: Cash flow from Operations less Capital Expenditure.

It is cash flow that can be dedicated to the reimbursment of debts, paying of dividends, …

Free cash flow = EBITDA - Change in Working Capital - Income Taxes - Investments

‌

Cash Flow: The variation of cash from one period to another, resulting from operating activities, investments and financing activities.

Cash flow = Free cash flow - financing activities (reimbursment of debts, funding, …)

‌

Let’s see an example:

Margin Variance: Revenue/Volume Effect, Price Effect, Costs Effect, Mix Effect

by

Explaining a margin variation between actual and budgeted results for last month is essential. While we’ve discussed how to explain revenue variation previously, let’s delve into margin variance.
There are several approaches, but here’s what I find most useful:

  • Method 1: Volume & Unit Margin
Volume Effect = (Actual Volume - Budgeted Volume) * Budgeted Unit Margin
Price effect = Sum of Price Effect on all products
Costs effect = Sum of Costs Effect on all products
Mix effect = (Actual Unit Margin - Budgeted Unit Margin) * Actual Volume - Price effect - Costs effect
  • Method 2: Revenue & Margin Rate
Revenue Effect = (Actual Revenue - Budgeted Revenue) * Budgeted Margin Rate
Price effect = Sum of Price Effect on all products
Costs effect = Sum of Costs Effect on all products
Mix effect = (Actual Margin Rate - Budgeted Margin Rate) * Actual Revenue - Price effect - Costs effect

Let’s examine an example using both methods:

‌

What can we infer from this example?

  • Both methods yield different results as the volume is lower (-2 cars) and the revenue higher (+€110 k)
  • Method 1 is more representative for a range of similar products with unit margins in a reasonable range. If you sell screws and nails along with cars, the second method will be more suitable.
  • Method 2 is preferable when dealing with products with varying unit prices, focusing on revenue and margin rate rather than volume and unit margin.

Should you automate your Excel Files ?

by

Whether it’s for daily, weekly, or monthly tasks like revenue analysis, margin tracking, or cash flow forecasts, automation can be a game-changer.

Some may argue that manual work helps uncover errors or aids in analysis, but I see it differently. Automating your files offers numerous benefits:

  • Deep understanding: Automating forces you to grasp the intricacies of your processes, including handling exceptions.
  • Time savings: You’ll save a lot of time, for sure. Time that can be dedicated to being a business partner, doing non recurring analysis that will make your company move forward (which is not the case with recurring analysis).
  • Delegation Ease: Automated files are easier to delegate, freeing up even more of your time.
  • Continuous Improvement: With automation in place, you can continuously refine and enhance your files. This will allow to push further the analysis.
  • Fastest Way to the End Result: You’ll get faster to the end result and you will spot with more ease potential mistakes. Then, and only then, you need to go into the details.

So, the next time you open a familiar Excel file, consider automating it step by step.
Tomorrow we’ll see exactly how to do it.

Analyzing Revenue Variations

by

Let’s say your revenue budget for last month was €1,070k, but you exceeded it by €110k. Now, you need to explain the main reasons for this variation to your shareholders.

Here’s how to break it down:

  • Volume Effect:
    Calculate the difference between actual and budgeted volumes, multiplied by the budgeted unit price
(Actual Volume - Budgeted Volume) * Budgeted Unit Price
  • Price Effect:
    For each product, compute the difference between actual and budgeted unit prices, multiplied by actual volumes. Sum up the price effects for each product. Bear in mind not to add a price effect for a product if the Budget did not anticipate this product to be sold.
ΣAll products (Actual Unit Price - Budgeted Unit Price) * Actual Volume if Budgeted Unit Price ≠ 0
  • Mix Effect:
    Determine the overall price effect minus the sum of price effects for each product. This reveals the impact of changes in product mix.
(Actual Unit Price - Budgeted Unit Price) * Actual Volume - Price Effect

Now that you have the figures, focus on explaining the significant impacts only.

Here’s an example to illustrate:

Now you can explain the price effect of the convertible model (impact of +€58k) and the mix effect due to you selling more convertible models (which has the higher unit price) than expected (8 vs. 5).