Accounts Receivable Aging Report in Excel

The Receivables Aging (or Ageing, if you prefer British English) report is a tool that lists all unpaid customer balances by pre-defined date ranges (buckets). It shows the relationship between open invoices and their due dates. It is the primary tool to determine overdue balances for collection. It’s useful for the company’s management, as it helps to evaluate the effectiveness of the credit control function. Typically, we separate the outstanding balances in buckets, at multiples of 30 days. Doing so Read more…

Hypothesis Testing for Complete Beginners

Gathering data in itself is meaningless unless we can analyze it and draw powerful insights. What makes data interesting is the ability to evaluate and interpret it. Hypothesis testing refers to a term in statistics where we, as the analysts, evaluate an assumption related to a data set parameter. Based on the purpose of the analysis and the specific characteristics of the data, we can use different methodologies. In general, the technique gives us a standardized way to assess the Read more…

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. We use the model when we have one measurement variable and two nominal variables, also known Read more…

What are Capital Expenditures (CAPEX)?

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 Read more…

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 Read more…

Your Business Needs a Flux Analysis

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 Read more…

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 Read more…

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 Read more…

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. The aim is to create a model that can help us Read more…

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 Read more…