Financial Modeling

Summary Statistics

When performing a comparable companies/acquisitions analysis, it is customary to include summary statistics (e.g., min/max, mean, median) below the data set as shown in the image below. Macabacus automates this process, and can populate these statistics with the click of the button.

First, select the data for which you want to compute statistics, as shown above. The first column in your selection is the column in which the statistic labels (e.g., "Min," "Average") will go. Next, click the  Macabacus > Formulas > Summary Statistics  button to insert the summary statistics. Macabacus will insert enough rows to accommodate the summary statistics to ensure that no cells below the data are overwritten.

Macabacus will intelligently conform number formatting of the resulting statistics to the number formatting of your selected data. If number formats in only the first row of your data include percent signs, multiple indicators ("×"), etc., Macabacus will apply these formats to only the first row of the statistics, as shown above. If each row of your data uses the same number formats, then Macabacus will apply those formats to each row of the statistics.

The statistics computed by Macabacus are customizable in the Summary Statistics dialog. You can select which statistics to compute and change the labels for each statistic. For example, you could change the AVERAGE function's label from "Average" to "Mean." You can also choose to ignore currencies when computing statistics, since statistical analysis on certain currency data (e.g., market capitalization, stock price) for a set of companies may be meaningless. Macabacus will remember your last used settings from this dialogue box so after you configure it to suit your needs, you will only need to hit enter on the keyboard to populate the statistics unless other changes are required.

Add Scenarios

Use the Add Scenarios tool to add scenarios, or cases, to your financial projections. Scenarios are often used in financial modeling to project inputs like growth rates and margins that drive results. Common scenarios include an "upside case," a "management case," and a "downside case."

To add scenarios to your model, select the range of cells where you would like the scenarios inserted. For example, you might select the growth rate inputs that drive sales projections. Your selection must include a single row and two or more columns. Then, click the  Macabacus > Formulas > Add Scenarios  button. In the Add Scenarios dialog, specify the names of the scenarios you want to create, and specify whether you want these scenarios to be local (intended for one-time use in a specific part of your model) or global (intended for reuse throughout your model), then click the OK  button.

With  global scenarios, you can use a single master scenarios toggle to change the active scenario for multiple input drivers. When you create global scenarios, Macabacus adds a range name called "Scenario" to your workbook that refers to the master scenarios toggle cell. Macabacus also applies validation to this cell allowing you to choose only a valid scenario number. More importantly, this validation is what Macabacus uses to apply global scenarios to other parts of your model. If you remove this validation or the "Scenarios" range name, Macabacus will be unable to add further global scenarios in your workbook.

Replicate Model

Replicating a module is an efficient way to reuse a piece of analysis or part of a model, or "module." For example, you can quickly replicate a P&L for each of ten business segments and sum the segment P&Ls into a single consolidated P&L. Macabacus can replicate a module multiple times on the same worksheet, or on separate worksheets, creating exact copies of the original module.

To Replicate a Module in Excel

  1. Select the cell range to be replicated, including all components to be copied such as titles, column headers, item labels, etc. The module to be copied must be a contiguous range of cells.
  2. Click the Macabacus > Formulas > Replicate Module button.
  3. Specify your desired settings in the Replicate Module dialog (pictured). See below for a description of various Replicate Module settings. These settings will be retained for the next time Replicate Module is used.

Same Worksheet

When replicating modules on the same worksheet as the original module, Macabacus inserts newly created modules directly below the original module, inserting rows as necessary. You can specify how many rows of separation are desired between each new module in the Replicate Module dialog.

Separate Worksheets

When replicating modules on separate worksheets, Macabacus will add a new worksheet for each replicated module, using the base name you specify to name new worksheets. For example, if your chosen base name is "Balance Sheet," then new worksheets will be named Balance Sheet 1, Balance Sheet 2, etc.

Sum Copies

You can choose to sum each new module created into the original module. Note that Macabacus will intelligently decide which items to sum, adding up numeric inputs but ignoring dates and text.

Quick CAGR

Macabacus can quickly insert compound annual growth rate (CAGR) formulas, so you don't have to remember the formula or manually count periods. Just place the cursor in a cell where you want to insert a CAGR, click the  Macabacus > Formulas > Quick CAGR  button, and Macabacus will intelligently determine which cells to the left or above are to be included in the CAGR formula by analyzing surrounding data. In addition to an annual CAGR, you can compute quarterly and monthly CAGRs.

Model Library

Macabacus' Model Library is a collection of model templates intended for reuse. There are three separate Model Libraries; a personal library intended for an individual user, a team library shared by members of a team, and a company library shared by all Macabacus users in an organization that can maximize workforce efficiency and standardize modeling practices. Models can be accessed from the  Macabacus > New  menu, and Macabacus installs with several of its popular model templates included in the personal Model Library.

Administrator privileges

To publish to the company model library or make changes to that library in Library Manager, either (a) activate Macabacus using the email address of a Macabacus account administrator, or (b) run Excel as a Windows administrator. This prevents unauthorized changes by end users.

Adding Model

Models can be published to the personal, team, or company library by clicking the  Macabacus > Settings > Libraries > Publish Model  button in Excel, PowerPoint, or Word. You will be prompted to select a workbook to publish, and to specify a document group to which the model should be published. If the path to the library to which you are publishing is not set, Macabacus will prompt you to choose a library folder.

Shared models

Models in the team and company libraries should reside in server locations available to Macabacus users with read access.

Deleting Models

The Library Manager dialog (shown above) can be used to view and manage all models in the libraries, and is accessed from the  Macabacus > Settings > Libraries  menu in Excel. Select the models you wish to remove and click the Delete button.

Insert Symbol

Insert symbols commonly used in financial models, presentations, and documents from the  Macabacus > Insert Symbol  menu. Available symbols are shown below. Relative to inserting symbols with Office's native Symbol dialog, which can require digging through hundreds of symbols to find the right one, this approach is much quicker. Also, you can use accelerator keystrokes to reach the desired symbol on the Macabacus tab, so you don't even have to use the mouse.

Available Characters

Dollar ($) Euro (€) Pound (£) Cent (¢) Minus (−) Times (×) Divide (÷) Ellipsis (…) Bullet (•) Section (§) Beta (Β) Delta (Δ)

This documentation refers to the latest Macabacus version. Some features and descriptions of these features may not apply to older versions of Macabacus. Update your Macabacus software to take advantage of the latest features.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.