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.
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
- 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.
- Click the
Macabacus > Formulas > Replicate Module
button. - 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.