Financial Modeling and Analysis

# How to Run a Two-Way Analysis of Variance (ANOVA) in Excel

Recently, we looked at how to Perform a One-Way Analysis of Variance in Excel. In today’s article, we will take that a step further and a look at a Two-Factor ANOVA.

The Two-Way Analysis of Variance (ANOVA) is a statistical test to evaluate the difference between the means of more than two groups.

It is also known as a Factorial ANOVA with two factors. …

# What are Capital Expenditures (CAPEX)?

Photo by Philipp Birmes from Pexels

Capital Expenditures, or CAPEX for short, are cash or credit payments to acquire goods or services that we capitalize in balance sheet assets. From the company’s perspective, we consider those to be an investment. Here we include all expenses that are not shown on the Income Statement and do not affect profit and loss for the current period.

CAPEX is an important concept, as this is the way for the business to support and expand its operating capacity by investing in property, plant and equipment, and technology.

As financial analysts, we pay close attention to Capital Expenditures, as they are not part of the Income Statement, but may have a significant impact on the Cash Flow Statement. …

# Introduction to Enterprise Value (EV) of the Business

Once you start delving deeper into valuation and especially in the premise of mergers and acquisitions, you notice that Enterprise Value is an essential term in this field.

A company’s value consists of its owned assets, but in reality, obtaining their market value can be tedious and resource-intensive. Following the accounting equation, we can value these by the shareholder’s equity and liabilities, which the company used to finance its assets. By considering the market value of the firm’s equity and liabilities, we can derive the current market value of the business.

It helps investment analysts to evaluate potential new investment opportunities. The EV metric is vital because it’s not affected by capital structure, but only by the core operations of the business. …

Flux Analysis is a type of comparison technique that helps us understand the changes in a company’s financials from period to period.

It is shortened from Fluctuations Analysis and is also sometimes called Horizontal Analysis. We use the method to evaluate the fluctuations in accounts over time. We usually express such changes in both monetary units and percentages.

A useful Flux Analysis aims not only to identify the changes but also to explain the reasons causing them.

# Why Prepare a Flux Analysis?

Investigating the changes in our accounts can be helpful for many reasons. In management accounting and general business management, it can help us understand and manage the operations of the company in a more effective way. The insights we get from evaluating the fluctuations in our accounts also provide us with better information to support our financial and operating decisions. …

# Understand the Market Value of Equity

The Market Value of Equity of the company, also known as Market Capitalization, is the total monetary value of the firm’s equity.

We calculate it as the current stock price multiplied by the number of outstanding shares. Therefore the MVE continually changes, as these two values are quite volatile.

Analysts mostly use the Market Value of Equity as a basis to calculate performance ratios. Investors use it to evaluate the size of companies and diversify their portfolio across investments of different sizes and risk-levels.

# Market Value of Equity (MVE)

The measure represents the total value of a company in the eyes of the investors. MVE can shift a lot during times, at which important information about the company becomes publicly available. …

# Perform a One-Way Analysis of Variance (ANOVA) in Excel

The Analysis of Variance (ANOVA) has many varieties, but in essence, it has the purpose of evaluating whether factors are associated with any outcome values. And factors are categorical variables we use to group the outcome variables.

In this article, we will not be focusing on the underlying statistical principles and formulas. We will briefly touch on how we define the two hypotheses of ANOVA and will then show how to implement the model in Excel, as part of our financial analysis efforts.

# What is the ANOVA test?

ANalysis Of VAriance, written as ANOVA for short, is a statistical technique that compares sample populations based on their means and spread of the data. The model helps us answer the question of whether the means of two or more groups are significantly different. …

# Seasonality and Trend Forecast with Regression in Excel

In our last article, we discussed Seasonality in Financial Modeling and Analysis. We went over an example Excel model of calculating a forecast with seasonality indexes.

Today we will use regression analysis in Excel to forecast a data set with both seasonality and trend.

Let’s look at the quarterly sales revenue of the electronic cameras manufacturer GoPro (source: https://www.macrotrends.net/stocks/charts/GPRO/gopro/revenue).

We have the data for the period 2013 to 2019. …

# Introduction to Seasonality in Financial Analysis and Modeling

Seasonality is a characteristic of time-series where the data has predictable and somewhat regular fluctuations that repeat year over year. It is safe to assume that any pattern of data changes over one-year periods represents seasonality. It is usually driven by weather or commercial seasons.

We have to differentiate the term from cyclical data, as the latter can span over various times periods, shorter or longer than one year.

Please, keep in mind that I am writing this article the intention to avoid getting into statistics. …

# Forecasting Sales Performance and Seasonality

We are approaching the second half of the year, and before we know it, it will be the time of year to start working on our projections for next year and the company’s annual budget. There are many complex and detailed models that we can utilize to forecast the sales performance of the business for the next period. However, I have recently noticed that almost every time I do work for a client, I end up using the most simple and easy to set up methods.

We discussed in our article on why we need a budget that the primary purpose of a forecast is to provide a guideline for the company. We don’t aim to produce a 100%-correct prediction, as in reality, we can never cover all variables that influence the business. …

# How to Calculate the Beta of a Company

Beta is a risk-reward measure from fundamental analysis to determine the volatility of an asset compared to the overall market. We consider the market to have a beta of one. Then all assets are ranked based on their deviation from the market. If an asset’s returns fluctuate more than the market, then this asset has a beta of above one and vice versa. Higher coefficient is often associated with increased risk, but it also brings the potential for higher returns.

Beta helps us identify how much risk an investor can take to gain the associated reward.

# What is the Beta Coefficient?

If we think of risk as the possibility of a value loss, we can see the beta as an appropriate measure to represent this possibility. …