The Split-Screen Approach for Project Appraisal (Part II: Spreadsheet Modeling)

Abstract

This paper employs the newly conceived accounting-and-finance engineering system (AFES) described in a previous paper (Magni 2023, “The Split-Screen Approach for Project Appraisal (Part I: The Theory)”), addressed to the analysis of capital asset investments. In this second part, we show how to implement this theoretical framework onto a spreadsheet software. We guide the analyst step by step, cell by cell, to the creation of the Split-Screen Matrices describing the project film. Because the AFES is based on two arithmetic relations (law of motion and law of conservation), we can use a minimal approach to modeling, with a frugal use of the most common spreadsheet functions (essentially INDEX and MATCH) and no use of the traditional financial functions, yet fulfilling the requisite of clearness, transparency, consistency, and ease of use. Starting from the informal description of the project, we build the model by breaking it down to 7 modules. The spreadsheet model is available online (see link provided in the paper).

Introduction

This paper is the second, applicative part of a work addressed to illustrate the accounting-and-finance engineering system (AFES) for project appraisal, developed in Magni (2023). The latter describes the theory underlying the AFES for project appraisal.1 The AFES is grounded on two principles. First, the Balance Sheet (BS), Income Statement (IS), and Cash Flow Statement (CFS) show, respectively, the capital invested, the income generated, and the cash flow extracted, which are dynamically interconnected by the law of motion $C_t=C_{t-1}+I_t-F_t$; where $C,I,$ and $F$ denote, capital, income, and cash flow, respectively. Second, the BS, IS, and CFS satisfy a law of conservation, according to which the capital in one period must be equal to the financial obligations of the firm towards some capital providers (equityholders and debtholders); the income generated by the investments is equal to the income accrued to the capital providers; the cash flow extracted from (or injected into) some assets is equal to the cash flow distributed to (or raised from) the capital providers. Blending these two laws, the BS, IS, and CFS may be graphically interlinked by a Split-Screen Matrix (SSM) as in

Table 1. The Split-Screen Matrix (SSM).

$+ {\mathbf{B}\mathbf{A}\mathbf{L}\mathbf{A}\mathbf{N}\mathbf{C}\mathbf{E} \mathbf{S}\mathbf{H}\mathbf{E}\mathbf{E}\mathbf{T}}_{\mathit{t}}$	$+ \mathbf{B}\mathbf{A}\mathbf{L}\mathbf{A}\mathbf{N}\mathbf{C}\mathbf{E} \mathbf{S}\mathbf{H}\mathbf{E}\mathbf{E}{\mathbf{T}}_{\mathit{t}-1}$	$+ {\mathbf{I}\mathbf{N}\mathbf{C}\mathbf{O}\mathbf{M}\mathbf{E} \mathbf{S}\mathbf{T}\mathbf{A}\mathbf{T}\mathbf{E}\mathbf{M}\mathbf{E}\mathbf{N}\mathbf{T}}_{\mathit{t}}$	$+ \mathbf{C}\mathbf{A}\mathbf{S}\mathbf{H} \mathbf{F}\mathbf{L}\mathbf{O}\mathbf{W} {\mathbf{S}\mathbf{T}\mathbf{A}\mathbf{T}\mathbf{E}\mathbf{M}\mathbf{E}\mathbf{N}\mathbf{T}}_{\mathit{t}}$
$+ {\mathrm{N}\mathrm{e}\mathrm{t} \mathrm{O}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{A}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_t$	$+ {\mathrm{N}\mathrm{e}\mathrm{t} \mathrm{O}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{A}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_{t-1}$	$+ \mathrm{O}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} {\mathrm{i}\mathrm{n}\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{e}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{f}\mathrm{r}\mathrm{o}\mathrm{m} {\mathrm{o}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}}_t$
$+ {\mathrm{L}\mathrm{i}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{d} \mathrm{A}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_t$	$+ {\mathrm{L}\mathrm{i}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{d} \mathrm{A}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_{t-1}$	$+ \mathrm{I}\mathrm{n}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{e}\mathrm{s}\mathrm{t} {\mathrm{i}\mathrm{n}\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{e}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{f}\mathrm{r}\mathrm{o}\mathrm{m} {\mathrm{l}\mathrm{i}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{d} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_t$
$+ {\mathrm{D}\mathrm{e}\mathrm{b}\mathrm{t}}_t$	$+ {\mathrm{D}\mathrm{e}\mathrm{b}\mathrm{t}}_{t-1}$	$+ \mathrm{I}\mathrm{n}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{e}\mathrm{s}\mathrm{t} {\mathrm{e}\mathrm{x}\mathrm{p}\mathrm{e}\mathrm{n}\mathrm{s}\mathrm{e}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{t}\mathrm{o} {\mathrm{d}\mathrm{e}\mathrm{b}\mathrm{t}}_t$
$+ {\mathrm{E}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{t}\mathrm{y}}_t$	$+ {\mathrm{E}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{t}\mathrm{y}}_{t-1}$	$+ {\mathrm{N}\mathrm{e}\mathrm{t} \mathrm{i}\mathrm{n}\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{e}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{t}\mathrm{o} {\mathrm{e}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{t}\mathrm{y}}_t$

.

where the red (horizontal and vertical) bars represent equality (see Magni 2023 for details). This tabular format enables reading the three financial statements jointly, both horizontally and vertically. Reading it horizontally (dynamic dimension), each component of the BS as of time $t$ is obtained from the same component of the BS as of time $t-1$ by adding the corresponding component of the IS and subtracting the corresponding component of the CFS (e.g., net operating assets at time $t$ are equal to net operating asset at time $t-1$ plus the operating income less the cash flow from operations). Reading it vertically (static dimension), the investment components equate the financing components for each financial statement: assets equate financings (BS), income from investments equates income to capital providers (IS), cash flow from investments equates cash flow to capital providers (CFS) (e.g., the sum of operating income and interest income is equal to the interest expense and the net income). The tabular format may be enriched by decomposing each one of the four areas in its constituent assets. For example, breaking down the net operating assets, the SSM is expanded as in

Table 2. The Full-Scale Split-Screen Matrix.

$+ {\mathbf{B}\mathbf{A}\mathbf{L}\mathbf{A}\mathbf{N}\mathbf{C}\mathbf{E} \mathbf{S}\mathbf{H}\mathbf{E}\mathbf{E}\mathbf{T}}_{\mathit{t}}$	$+ \mathbf{B}\mathbf{A}\mathbf{L}\mathbf{A}\mathbf{N}\mathbf{C}\mathbf{E} \mathbf{S}\mathbf{H}\mathbf{E}\mathbf{E}{\mathbf{T}}_{\mathit{t}-1}$	$+ {\mathbf{I}\mathbf{N}\mathbf{C}\mathbf{O}\mathbf{M}\mathbf{E} \mathbf{S}\mathbf{T}\mathbf{A}\mathbf{T}\mathbf{E}\mathbf{M}\mathbf{E}\mathbf{N}\mathbf{T}}_{\mathit{t}}$	$+ \mathbf{C}\mathbf{A}\mathbf{S}\mathbf{H} \mathbf{F}\mathbf{L}\mathbf{O}\mathbf{W} {\mathbf{S}\mathbf{T}\mathbf{A}\mathbf{T}\mathbf{E}\mathbf{M}\mathbf{E}\mathbf{N}\mathbf{T}}_{\mathit{t}}$
$+ {\mathrm{A}\mathrm{c}\mathrm{c}\mathrm{o}\mathrm{u}\mathrm{n}\mathrm{t}\mathrm{s} \mathrm{r}\mathrm{e}\mathrm{c}\mathrm{e}\mathrm{i}\mathrm{v}\mathrm{a}\mathrm{b}\mathrm{l}\mathrm{e}}_t$	$+ {\mathrm{A}\mathrm{c}\mathrm{c}\mathrm{o}\mathrm{u}\mathrm{n}\mathrm{t}\mathrm{s} \mathrm{r}\mathrm{e}\mathrm{c}\mathrm{e}\mathrm{i}\mathrm{v}\mathrm{a}\mathrm{b}\mathrm{l}\mathrm{e}}_{t-1}$	$+ {\mathrm{S}\mathrm{a}\mathrm{l}\mathrm{e}\mathrm{s}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{r}\mathrm{e}\mathrm{c}\mathrm{e}\mathrm{i}\mathrm{p}\mathrm{t}\mathrm{s} \mathrm{f}\mathrm{r}\mathrm{o}\mathrm{m} {\mathrm{c}\mathrm{u}\mathrm{s}\mathrm{t}\mathrm{o}\mathrm{m}\mathrm{e}\mathrm{r}\mathrm{s}}_t$
$+ {\mathrm{I}\mathrm{n}\mathrm{v}\mathrm{e}\mathrm{n}\mathrm{t}\mathrm{o}\mathrm{r}\mathrm{y}}_t$	$+ {\mathrm{I}\mathrm{n}\mathrm{v}\mathrm{e}\mathrm{n}\mathrm{t}\mathrm{o}\mathrm{r}\mathrm{y}}_{t-1}$	$+ {\Delta \mathrm{I}\mathrm{n}\mathrm{v}\mathrm{e}\mathrm{n}\mathrm{t}\mathrm{o}\mathrm{r}\mathrm{y}}_t$	$- 0$
$- {\mathrm{A}\mathrm{c}\mathrm{c}\mathrm{o}\mathrm{u}\mathrm{n}\mathrm{t}\mathrm{s} \mathrm{p}\mathrm{a}\mathrm{y}\mathrm{a}\mathrm{b}\mathrm{l}\mathrm{e}}_t$	$- {\mathrm{A}\mathrm{c}\mathrm{c}\mathrm{o}\mathrm{u}\mathrm{n}\mathrm{t}\mathrm{s} \mathrm{p}\mathrm{a}\mathrm{y}\mathrm{a}\mathrm{b}\mathrm{l}\mathrm{e}}_{t-1}$	$- {\mathrm{C}\mathrm{o}\mathrm{s}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{p}\mathrm{u}\mathrm{r}\mathrm{c}\mathrm{h}\mathrm{a}\mathrm{s}\mathrm{e}\mathrm{s}}_t$	$+ \mathrm{P}\mathrm{a}\mathrm{y}\mathrm{m}\mathrm{e}\mathrm{n}\mathrm{t}\mathrm{s} \mathrm{t}\mathrm{o} {\mathrm{s}\mathrm{u}\mathrm{p}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{e}\mathrm{r}\mathrm{s}}_t$
$- {\mathrm{S}\mathrm{a}\mathrm{l}\mathrm{a}\mathrm{r}\mathrm{i}\mathrm{e}\mathrm{s} \mathrm{a}\mathrm{n}\mathrm{d} \mathrm{w}\mathrm{a}\mathrm{g}\mathrm{e}\mathrm{s}}_t$	$- {\mathrm{S}\mathrm{a}\mathrm{l}\mathrm{a}\mathrm{r}\mathrm{i}\mathrm{e}\mathrm{s} \mathrm{a}\mathrm{n}\mathrm{d} \mathrm{w}\mathrm{a}\mathrm{g}\mathrm{e}\mathrm{s}}_{t-1}$	$- {\mathrm{L}\mathrm{a}\mathrm{b}\mathrm{o}\mathrm{r} \mathrm{c}\mathrm{o}\mathrm{s}\mathrm{t}}_t$	$+ \mathrm{P}\mathrm{a}\mathrm{y}\mathrm{m}\mathrm{e}\mathrm{n}\mathrm{t}\mathrm{s} \mathrm{t}\mathrm{o} {\mathrm{e}\mathrm{m}\mathrm{p}\mathrm{l}\mathrm{o}\mathrm{y}\mathrm{e}\mathrm{e}\mathrm{s}}_t$
$+ {\mathrm{N}\mathrm{e}\mathrm{t} \mathrm{f}\mathrm{i}\mathrm{x}\mathrm{e}\mathrm{d} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_t$	$+ {\mathrm{N}\mathrm{e}\mathrm{t} \mathrm{f}\mathrm{i}\mathrm{x}\mathrm{e}\mathrm{d} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_{t-1}$	$- {\mathrm{D}\mathrm{e}\mathrm{p}\mathrm{r}\mathrm{e}\mathrm{c}\mathrm{i}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}}_t$	$+ \mathrm{N}\mathrm{e}\mathrm{t} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t} {\mathrm{d}\mathrm{i}\mathrm{s}\mathrm{p}\mathrm{o}\mathrm{s}\mathrm{a}\mathrm{l}\mathrm{s}}_t$
$- {\mathrm{T}\mathrm{a}\mathrm{x}\mathrm{e}\mathrm{s} \mathrm{p}\mathrm{a}\mathrm{y}\mathrm{a}\mathrm{b}\mathrm{l}\mathrm{e}}_t$	$- {\mathrm{T}\mathrm{a}\mathrm{x}\mathrm{e}\mathrm{s} \mathrm{p}\mathrm{a}\mathrm{y}\mathrm{a}\mathrm{b}\mathrm{l}\mathrm{e}}_{t-1}$	$- {\mathrm{T}\mathrm{a}\mathrm{x}\mathrm{e}\mathrm{s}}_t$	$+ \mathrm{P}\mathrm{a}\mathrm{y}\mathrm{m}\mathrm{e}\mathrm{n}\mathrm{t}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r} {\mathrm{i}\mathrm{n}\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{e} \mathrm{t}\mathrm{a}\mathrm{x}\mathrm{e}\mathrm{s}}_t$
$+ {\mathrm{L}\mathrm{i}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{d} \mathrm{A}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_t$	$+{\mathrm{L}\mathrm{i}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{d} \mathrm{A}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_{t-1}$	$+ {\mathrm{I}\mathrm{n}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{e}\mathrm{s}\mathrm{t} \mathrm{i}\mathrm{n}\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{e}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{f}\mathrm{r}\mathrm{o}\mathrm{m} {\mathrm{l}\mathrm{i}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{d} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}}_t$
$+ {\mathrm{D}\mathrm{e}\mathrm{b}\mathrm{t}}_t$	$+ {\mathrm{D}\mathrm{e}\mathrm{b}\mathrm{t}}_{t-1}$	$+ \mathrm{I}\mathrm{n}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{e}\mathrm{s}\mathrm{t} {\mathrm{e}\mathrm{x}\mathrm{p}\mathrm{e}\mathrm{n}\mathrm{s}\mathrm{e}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{t}\mathrm{o} {\mathrm{d}\mathrm{e}\mathrm{b}\mathrm{t}}_t$
$+ {\mathrm{E}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{t}\mathrm{y}}_t$	$+ {\mathrm{E}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{t}\mathrm{y}}_{t-1}$	$+ {\mathrm{N}\mathrm{e}\mathrm{t} \mathrm{i}\mathrm{n}\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{e}}_t$	$- \mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{t}\mathrm{o} {\mathrm{e}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{t}\mathrm{y}}_t$

.

The first six rows represent the net operating assets (i.e., operating assets less operating liabilities). Given an $n$-year project project, there exists a sequence of $n$ such SSMs, which give rise to a strip of SSMs. This strip must be built by the modelers on the basis of the assumptions on the input parameters.

As shown in Part I, this very framework may be used for financial evaluation as well. To this end, one builds the strip of SSMs related to the benchmark portfolio. Opposing the two strips (the project’s and benchmark portfolio’s), the relevant financial metrics are directly computed: economic residual income, net present value, and project’s rate of return.

This approach is highly innovative and, while consistent with the traditional capital budgeting theory described in the traditional finance and engineering texts (e.g., Hartman 2007; Newnan et al. 2009; Brealey et al. 2011; Ross et al. 2019; Berk and DeMarzo 2019; Park 2019), it integrates the static and dynamic dimension of a project overturning the traditional models from three main points of view:

• the creation of isolated pro forma financial statements derived from the model’s assumptions is replaced by the so-called Split-Screen Matrix (SSM) and split-screen film (displayed in different formats);
• the new notion of cash flow from (or to) liquid assets is employed (in addition to the classical operating cash flow, cash flow to debt, and cash flow to equity);
• the role of the payout and financing decisions is made explicit;
• the project’s economic profitability, usually based on cash flows and measured by net present value and internal rate of return, is replaced by a set of new, reciprocally consistent measures, which provide deeper information about the project’s economic profitability; and
• the model is designed to act as a diagnostic tool for spotting internal inconsistencies.

These features make the AFES radically different from the approaches to project appraisal currently available in applied corporate finance or engineering economics. For this reason, the new approach requires that the association among accounting and financial magnitudes are remodeled; in other words, the AFES asks for a different-from-usual spreadsheet modeling. This paper aims at filling this gap. We provide a complete translation of the theory into spreadsheet modeling for practical applications and offer a detailed step-by-step guide to the AFES construction in spreadsheets, aiming at helping practitioners to employ it in real-life applications. Using a mockup project and following Part I’s concepts, notions, formalizations, and algebraic relationships, we build the whole spreadsheet model for the project, starting from the verbal description of it and explaining it in full, cell by cell.2

Several organizations propose guidelines in financial modeling with spreadsheets (especially with Microsoft Excel). Two of the most popular guidelines are the FAST Standard (FAST Standard Organization 2022), a set of rules on the building spreadsheet-based financial models, developed by a non-profit organization, and SSRB Best Practice Spreadsheet Modeling Standards (Spreadsheet Standards Review Board 2016). These standards contain some similar principles and rules, which we have implemented in the methodology used in our workbook (described in this paper); in particular, we use separate worksheets for inputs, calculations, and outputs, avoid circular references and use concise and simple formula. The result is a spreadsheet model that fulfils the requisite for clear and concise communication and transparency. Indeed, using the rigorous-yet-simple structure of the AFES and implementing the worksheet according to the best practices, we establish a “spreadsheet engineering” discipline that, put it in Thorne’s (2009) words, mitigates the problem of spreadsheet errors. Several criteria for spreadsheet accuracy are also applied (some suggestions are, for example, in Powell et al. 2008) and, more specifically, the AFES articulation is based on the law of motion and law of conservation, which enables the analyst to spot modeling errors (e.g., if numbers in rows and columns do not match according to the law of motion and law of conservation, there is some inconsistencies in the calculation). This zeroes out possible internal inconsistencies.

Traditional financial modeling is based on a vast number of functions (see Avon 2021, Ch. 11 for a list of the most used ones; see also Benninga 2014, Benninga and Mofkadi 2018). Owing to the AFES design, we propose a more frugal and user-friendly model, based as it is on very few spreadsheet functions, which are most common tools for modelers. Specifically, we use only four functions: the numeric function SUM(…), the reference functions INDEX(…) and MATCH(…), and the logical function IF(…); these functions are available in most spreadsheets (with small syntactic differences). We will also use the text string operator &3 (and, last but not least, the analyst will not need any of the usual financial functions).

We accompany this paper with its associated Microsoft Excel file, which is available at the following link: https://morespace.economia.unimore.it/carloalbertomagni/spreadsheet-modeling (accessed 18 February 2023). The spreadsheet file, according to best practices described in handbooks (e.g., Swan 2016) and in standard guidelines, has a structure based on separated sheets for inputs and assumptions (inputs), calculations (working sheets), and presentation of the results (outputs).

The analyst must be equipped with a spreadsheet software such as Microsoft Excel, Apple Numbers, Google Sheets, LibreOffice, OpenOffice Calc, or equivalents (tools that any financial analyst knows and daily employs in its work).

To present the model, we assume the role of a financial analyst who oversees the economic analysis of a given capital asset investment. We follow the entire process of the analyst, starting from the collection of the pieces of information from the various experts (CFO, CEO, CTO, etc.).

The analyst makes explicit all the assumptions obtained from interviews to the firm’s experts (CEO, CFO, CTO, marketing experts, etc.). For illustrative purposes, we model the project in Box 1, which has been inspired by an example presented in Magni (2020, Example 4.1).

Box 1. Description of CAD Inc. project.

 CAD Inc. project

CAD Inc. faces the opportunity of undertaking a five-year project consisting in the production of a manufactured good. The initial investment in fixed assets is equal to USD 20,000, to be fully paid in year 0. The investment in fixed assets is depreciated with straight-line depreciation.

The sales projection for the first period (year 1) is 6,000 units of product and the sales growth is equal to 10% yearly. The sale price of each unit of the product is USD 10 for each year.

The unit manufacturing costs incurred are

-

material used for production: USD 2.5,

-

direct and indirect labor: USD 4.

The production department requires that materials inventory at the end of each period should be 25% of the consumption estimated for the next period.

Selling, General & Administrative Expenses (SGA) are composed of

  -

purchases of non-manufacturing materials, estimated at 15% of sales

  -

incremental salaries for non-manufacturing activities, estimated at USD 6,000 each year.

Finished goods inventory will be zero (i.e., production will equal sales).

The average number of days to fully collect the payment after a sale is 90. All the purchases of materials (both manufacturing and non-manufacturing) will be cashed out in the year after the one in which they are incurred (except for the last period, when they will be cashed out in that year); salaries and wages will be paid monthly.

The tax rate is τ = 30% and taxes are paid within the year in which they are incurred.

The capital expenditure of USD 20,000 in t = 0 is sustained by

  (i)

an equity injection of USD 6,000,

  (ii)

a loan of USD 10,000 at an interest rate of $i^d$ = 2.00%; the loan is reimbursed with 4 principal repayments of equal amount,

  (iii)

a withdrawal from liquid assets for the residual amount of USD 4,000.

The interest rate $i^l$ on liquid asset is forecasted to be 3.80%.

CAD Inc. will pay out α = 20% of the net income to its shareholders in each period from 1 to 4.

 

The required returns on operating assets, on liquid assets, and on debt are respectively:

• $r^o$ = 15.00% (pre-tax WACC),
• $r^l$ = 1.00%,
• $r^d$ 3.00%.

We describe the development of the project model step by step, the creation of the benchmark obtained from the market input data, and the fabrication of the (absolute and relative) measures of economic profitability, which will lead to an accept or reject decision.

The model is divided into seven modules (see Figure 1 and

Table 3. Structure of the paper and of the spreadsheet file.

Module	Worksheet	Section Number	Section Title
1	Assumptions PrelCalc	1	Extracting the data of the project
2	SplitScreenStrip	2	Building the project split-screen strip
3	SplitScreenStrip	3	Building the strips of other matrix representations
4	SplitScreenStrip	4	Building the benchmark split-screen strip
5	SinglePeriod	5	Performing the single-period analysis
6	TransMatrix ConventionalFinStat	6	Reshaping the project split-screen strip
7	Value Creation	7	Assessing value creation and financial efficiency

):

• Module 1: Extracting the data of the project
• Module 2: Building the project split-screen strip
• Module 3: Building the strips of other matrix representations
• Module 4: Building the benchmark split-screen strip
• Module 5: Performing the single-period analysis
• Module 6: Reshaping the project split-screen strip
• Module 7: Assessing value creation and financial efficiency

Starting from the verbal description of the project presented above (see also “Description” worksheet), Module 1 translates it into formal inputs and assumptions (“Assumptions” worksheet) and carries out the preliminary calculations (“PreCalc” worksheet) that are necessary for Module 2 (“SplitScreenStrip” worksheet). The latter shows how to build the full-scale project film (split-screen strip). It first carves out the accounting magnitudes for period 0. The formulas for filling the cells in period 0 are in turn divided into: (i) cells whose values are provided by the preliminary calculations, (ii) cells whose values are calculated via the law of motion, (iii) cells whose values are calculated with suitable accounting formulas, and (iv) cells whose values are calculated with the law of conservation. Cells of periods 1 through $n$ are obtained by simply dragging the fill handle right. Module 3 starts from the full-scale strip and, in a top-down approach, builds other lower-scale Matrix representations which show different pieces of information and different classification of costs and cash flows, up to the germ strip, which condenses the capital invested in the project, $C_t$; the project’s return, $I_t$; and the project’s cash flow, $F_t$. Module 4 creates the benchmark split-screen strip (“SplitScreenStrip” worksheet), which is necessary for valuation and decision-making. In Module 5, we build a menu of SSMs for every period (“SinglePeriod” worksheet) which favors a period analysis. Module 6 shows how to pass from the split-screen film to the project Transposed Matrix and benchmark Transposed Matrix (“TransMatrix” worksheet). In the same module, we show how to pass from the project split-screen film to the standard pro forma financial statements (“ConventionalFinStat” worksheet). The object of Module 7 is to measure value creation (“ValueCreation” worksheet); in it, we derive the set of absolute measures (quantifying the economic value created by the project) and relative measures (quantifying the financial efficiency of the project) that are described in Magni (2023). Some conclusions follow, and a legend of symbols and abbreviations used in this paper is located at the end of the paper, for the benefit of the reader.

1. Module 1: Extracting the Data of the Project

1.1. Formalizing the Assumptions from the Project Description

In Module 1, the analyst identifies and formalizes the assumptions and, hence, makes some preliminary calculations, which are necessary for carrying out Module 2. The analyst retrieves all pieces of information regarding the project by the firm’s CEO, CFO, CTO, and the marketing experts, and then needs convert them from natural language to figures in order to use them in a financial model that fully describes the project and the relevant relationships. More precisely, the analyst must carve out the figures and the relationships among all the inputs and the resulting accounting and financial magnitudes. As testified by the description above (and explained in detail in Magni 2023),4 all pieces of information can be classified as capital, income, and cash flow. Furthermore, some of the inputs involve estimation regarding the project (costs, prices, quantities sold, etc.) or the market (required returns on assets, on debt, etc.), some other involve embedded decisions (e.g., the payout policy, the financing policy). The objective of the modeler is to derive, for each magnitude, the capital component, income component, and cash-flow component. Hence, the modeler must derive all the formal links among these basic variables for each area, build the project’s SSM for every period, and, therefore, the corresponding split-screen strip.

As a first step of Module 1, the analyst extracts the input variables of the model and the corresponding assumptions from the description conveyed in the natural language. We classify them in project inputs and market inputs:

Project inputs	(verbatim)
Assumption 01	a five-year project
Assumption 02	investment in fixed assets is equal to USD 20,000, to be fully paid in year 0
Assumption 03	the investment in fixed assets is depreciated with straight-line depreciation
Assumption 04	the sales projection for the first period (year 1) is 6,000 units of product
Assumption 05	the sales growth is equal to 10% yearly
Assumption 06	The sale price of each unit of the product is USD 10 for each year
Assumption 07	the unit manufacturing costs incurred are—material used for production: USD 2.5
Assumption 08	the unit manufacturing costs incurred are—direct and indirect labor: USD 4
Assumption 09	the production department requires that materials inventory at the end of each period should be 25% of the consumption estimated for the next period
Assumption 10	purchases of non-manufacturing materials, estimated at 15% of sales
Assumption 11	incremental salaries for non-manufacturing activities, estimated at USD 6,000 each year
Assumption 12	the average number of days to fully collect the payment after a sale is 90
Assumption 13	all the purchases of materials (both manufacturing and non-manufacturing) will be cashed out in the year after the one in which they are incurred (except for the last period, when they will be cashed out in that year)
Assumption 14	salaries and wages will be paid monthly
Assumption 15	the tax rate is $\tau$ = 30%
Assumption 16	taxes are paid within the year in which they are incurred
Assumption 17	the capital expenditure in $t=0$ is sustained by an equity injection of USD 6,000
Assumption 18	The capital expenditure in $t=0$ is sustained by a loan of USD 10,000
Assumption 19	at an interest rate of $i^d$ = 2.00%
Assumption 20	the loan is reimbursed with 4 principal repayments of equal amount
Assumption 21	the interest rate $i^l$ on liquid asset is forecasted to be 3.80%
Assumption 22	CAD Inc. will pay out $\alpha$ = 20% of the net income to its shareholders in each period from 1 to 4 and in the last period it will distribute the total amount of the equity
Market inputs
Assumption 23	$r^o$ = 15.00% (pre-tax WACC)
Assumption 24	$r^l$ = 1.00%
Assumption 25	$r^d$ = 3.00%

The project inputs are in turn divided into estimation variables (assumptions 1–11, 13–16, and 19) and decision variables (assumptions 12, 17, 18, 20, and 22).

The assumptions reported above provide the pieces of information needed for populating the SSM.

CODE INSTRUCTIONS: FORMALIZING ASSUMPTIONS

In a spreadsheet program, the analyst creates a workbook and adds a first sheet in it called “Assumptions”, where he collects the assumptions. The analyst can fill the cells of the Assumptions table with the figures extracted from the description of the project. Column G contain the inputs, column H (yellow background) contain the assumptions (Figure 2). In this sheet, each input value is contained only in one single cell: this means that every time an input is used in the calculations (“PreCalc” sheet), the formula captures the content of the input cell. Therefore, the analyst can simulate different scenarios by changing only the contents of the Assumptions sheet.

1.2. Making Preliminary Calculations

The objective of the analyst is to feed the Full-scale Matrix (Part I, Equation (33), right matrix) and build the whole strip of SSMs (see Part I, Equation (68)). However, to fill in the cells of each Matrix, preliminary calculations are needed, which make explicit, period by period, the figures already implicit in the assumptions.

More precisely, we highlight in yellow the cells in the full-scale matrix whose values may be obtained from the assumptions via some simple calculations (see Figure 3). We describe these calculations in this section. The remaining (not highlighted) cells contain amounts that will be calculated in Module 2, as we will see, by making use of the law of motion or the law of conservation or specific accounting formulas.

FIXED ASSETS. Assumption 02 regarding the NFA provides the following figures:

Period	0	1	2	3	4	5
	20,000	0	0	0	0	0

Assumption 03 enables calculating the (straight-line) depreciation:

$${\mathrm{D}\mathrm{e}\mathrm{p}}_t=\frac{\mathrm{N}\mathrm{F}{\mathrm{A}}_0}{n}=\frac{\mathrm{20,000}}{5}=4000$$

Period	0	1	2	3	4	5
	0	–4,000	–4,000	–4,000	–4,000	–4,000

SALES. Assumptions 04, 05, and 06 enable the analyst to calculate the sales for each period:

$$\begin{gathered} \mathrm{U}\mathrm{n}\mathrm{i}\mathrm{t}\mathrm{s} \mathrm{s}\mathrm{o}\mathrm{l}\mathrm{d} \mathrm{i}\mathrm{n} t=\left(1.1\right)\cdot \mathrm{U}\mathrm{n}\mathrm{i}\mathrm{t}\mathrm{s} \mathrm{s}\mathrm{o}\mathrm{l}\mathrm{d} \mathrm{i}\mathrm{n} t-1 \\ {\mathrm{S}}_t=\mathrm{U}\mathrm{n}\mathrm{i}\mathrm{t}\mathrm{s} \mathrm{s}\mathrm{o}\mathrm{l}\mathrm{d} \mathrm{i}\mathrm{n} t\cdot \mathrm{u}\mathrm{n}\mathrm{i}\mathrm{t} \mathrm{s}\mathrm{a}\mathrm{l}\mathrm{e} \mathrm{p}\mathrm{r}\mathrm{i}\mathrm{c}\mathrm{e} \end{gathered}$$

• Units (produced and) sold in the first year = 6,000.
• Annual sales growth = 10%.
• Unit sale price = USD 10.

Period	0	1	2	3	4	5
$\mathrm{Units} \mathrm{solid} \mathrm{in} t$	0	6,000	6,600	7,260	7,986	8,785
	0	60,000	66,000	72,600	79,860	87,850

MANUFACTURING COSTS. Assumption 07 enables calculating the material (direct material and overhead) used for production:

$$\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{a}\mathrm{l} \mathrm{u}\mathrm{s}\mathrm{e}\mathrm{d} \mathrm{i}\mathrm{n} t=\mathrm{u}\mathrm{n}\mathrm{i}\mathrm{t}\mathrm{s} \mathrm{s}\mathrm{o}\mathrm{l}\mathrm{d} \mathrm{i}\mathrm{n} t\cdot \mathrm{u}\mathrm{n}\mathrm{i}\mathrm{t} \mathrm{c}\mathrm{o}\mathrm{s}\mathrm{t}$$

• Unit cost = USD 2.5

Period	0	1	2	3	4	5
Units sold in $t$	0	6,000	6,600	7,260	7,986	8,785
$–\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{a}\mathrm{l}$ u$\mathrm{s}\mathrm{e}\mathrm{d} \mathrm{i}\mathrm{n} t$	0	–15,000	–16,500	–18,150	–19,965	–21,963

Assumption 09 enables calculating the raw materials inventory:

$${\mathrm{I}\mathrm{n}\mathrm{v}}_t=\left(\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{a}\mathrm{l} \mathrm{u}\mathrm{s}\mathrm{e}\mathrm{d} \mathrm{i}\mathrm{n} t+1\right)·25\mathrm{\%}$$

Period	0	1	2	3	4	5
$\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{a}\mathrm{l} \mathrm{u}\mathrm{s}\mathrm{e}\mathrm{d} \mathrm{i}\mathrm{n} t$	0	15,000	16,500	18,150	19,965	21,963
	3,750	4,125	4,538	4,991	5,491	0

The Cost of Purchases for the manufacturing activities (${\mathrm{C}\mathrm{O}\mathrm{P}}^m$) is the sum of the Material used for the production and the change in the raw materials inventory:5

$${\mathrm{C}\mathrm{O}\mathrm{P}}_t^m=\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{a}\mathrm{l} \mathrm{u}\mathrm{s}\mathrm{e}\mathrm{d} \mathrm{i}\mathrm{n} t+∆{\mathrm{I}\mathrm{n}\mathrm{v}}_t$$

Period	0	1	2	3	4	5
$-\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{a}\mathrm{l} \mathrm{u}\mathrm{s}\mathrm{e}\mathrm{d} \mathrm{i}\mathrm{n} t$	0	–15,000	–16,500	–18,150	–19,965	–21,963
$-\Delta {\mathrm{I}\mathrm{n}\mathrm{v}}_t$	–3,750	–375	–413	–454	–499	5,491
	–3,750	–15,375	–16,913	–18,604	–20,464	–16,472

Assumption 08 enables calculating the labor costs for manufacturing activities (${\mathrm{L}\mathrm{C}}^m$):

$${\mathrm{L}\mathrm{C}}^m=\mathrm{u}\mathrm{n}\mathrm{i}\mathrm{t}\mathrm{s} \mathrm{s}\mathrm{o}\mathrm{l}\mathrm{d}·\mathrm{u}\mathrm{n}\mathrm{i}\mathrm{t} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{o}\mathrm{r} \mathrm{c}\mathrm{o}\mathrm{s}\mathrm{t}$$

• Unit labor cost = USD 4.

Period	0	1	2	3	4	5
Units sold in $t$	0	6,000	6,600	7,260	7,986	8,785
	0	–24,000	–26,400	–29,040	–31,944	–35,140

NON-MANUFACTURING COSTS (SGA). Assumption 10 enables calculating the cost of purchases for the nonmanufacturing activities.

$${\mathrm{C}\mathrm{O}\mathrm{P}}_t^{nm}=S_t·15\mathrm{\%}$$

Period	0	1	2	3	4	5
$S_t$	0	60,000	66,000	72,600	79,860	87,850
	0	–9,000	–9,900	–10,890	–11,979	–13,178

Assumption 11 enables calculating the cost of labor for the nonmanufacturing activities:

Period	0	1	2	3	4	5
	0	–6,000	–6,000	–6,000	–6,000	–6,000

OPERATING CYCLE. The next set of information extracted from the interviews to the various firm’s experts provides information about the operating cycle of the project. Assumption 12 enables computing the Account Receivables:

$${\mathrm{A}\mathrm{R}}_t={\mathrm{S}}_t·\frac{90}{365}$$

Period	0	1	2	3	4	5
$+S_t$	0	60,000	66,000	72,600	79,860	87,850
	0	14,795	16,274	17,901	19,692	0

Assumption 13 enables calculating the Account Payables:

$${\mathrm{A}\mathrm{P}}_t^m={\mathrm{C}\mathrm{O}\mathrm{P}}_t^m\hspace{1em}\hspace{1em}\mathrm{and}\hspace{1em}\hspace{1em}{\mathrm{A}\mathrm{P}}_t^{nm}={\mathrm{C}\mathrm{O}\mathrm{P}}_t^{nm}$$

Period	0	1	2	3	4	5
${-\mathrm{C}\mathrm{O}\mathrm{P}}_t^m$	–3,750	–15,375	–16,913	–18,604	–20,464	–16,472
	–3,750	–15,375	–16,913	–18,604	–20,464	0
Period	0	1	2	3	4	5
$-{\mathrm{C}\mathrm{O}\mathrm{P}}_t^{nm}$	0	–9,000	–9,900	–10,890	–11,979	–13,178
	0	–9,000	–9,900	–10,890	–11,979	0

PAYROLL PERIOD. Assumption 14 enables calculating the payments to employees:

$${\mathrm{L}\mathrm{C}}_t^m=F_t^{swp,m}\hspace{1em}\hspace{1em}\mathrm{and}\hspace{1em}\hspace{1em}{\mathrm{L}\mathrm{C}}_t^{nm}=F_t^{swp,nm}$$

Period	0	1	2	3	4	5
${-\mathrm{L}\mathrm{C}}_t^m$	0	–24,000	–26,400	–29,040	–31,944	–35,140
	0	24,000	26,400	29,040	31,944	35,140
Period	0	1	2	3	4	5
${-\mathrm{L}\mathrm{C}}_t^{nm}$	0	–6,000	–6,000	–6,000	–6,000	–6,000
	0	6,000	6,000	6,000	6,000	6,000

TAXES. Assumption 16 enables calculating the Taxes Payable, which will be 0 in each period:

$${\mathrm{T}}_t=F_t^{tp}⟹{\mathrm{T}\mathrm{P}}_t=0$$

Period	0	1	2	3	4	5
	0	0	0	0	0	0

DEBT FINANCING. Assumptions 18 enables calculating the debt’s principal repayment and the principal outstanding:

$$\mathrm{p}\mathrm{r}\mathrm{i}\mathrm{n}\mathrm{c}\mathrm{i}\mathrm{p}\mathrm{a}\mathrm{l} \mathrm{r}\mathrm{e}\mathrm{p}\mathrm{a}\mathrm{y}\mathrm{m}\mathrm{e}\mathrm{n}\mathrm{t}=C_{t-1}^d-C_t^d=\frac{C_0^d}{4}=\frac{\mathrm{10,000}}{4}=2500$$

whence

$$C_t^d=C_{t-1}^d-2500$$

Period	0	1	2	3	4	5
–Principal repayments	0	–2,500	–2,500	–2,500	–2,500	0
	10,000	7,500	5,000	2,500	0	0

CODE INSTRUCTIONS: PRELIMINARY CALCULATIONS

The analyst adds, in his workbook, a new sheet, named “PreCalc”, where he calculates the figures obtained from the assumptions (see Figure 4).

Each column represents one period, and the rows contain the values taken on by the various variables. Columns A:G contain descriptions regarding the variables; column H contains the constant values (i.e., the values that are independent of the period); column I contains the unit of measurement of the variables; columns K to P contain the amounts of periods 0 through $n=5$ (last period of the project).

Column G contains the labels that describe the variables. Columns D, E, and F report the variables that are expected to be recorded (with proper sign) in the SSM, classified, respectively, as:

• capital ($\mathrm{A}\mathrm{R}$, $\mathrm{I}\mathrm{n}\mathrm{v}$, ${-\mathrm{A}\mathrm{P}}^m$, $-{\mathrm{A}\mathrm{P}}^{nm}$, $-\mathrm{T}\mathrm{P}$, $C^d$),
• income ($\mathrm{S}$, $-{\mathrm{C}\mathrm{O}\mathrm{P}}^m$, $-{\mathrm{C}\mathrm{O}\mathrm{P}}^{nm}$, $-{\mathrm{L}\mathrm{C}}^m$, $-{\mathrm{L}\mathrm{C}}^{nm}$, $-\mathrm{D}\mathrm{e}\mathrm{p}$),
• cash flow ($F^{swp,m}$, $F^{swp,nm}$, $-F^{nfa}$).

The amounts in the rows with a label in columns D, E, or F, are the ones that will be linked in the SSM in the next steps.

We use different font colors for the cells that are linked to other cells:

• blue for the rows that are linked to rows in the previous sheet, “Assumptions”;
• grey for the rows that are linked to other rows in the same sheet;
• red for the cells that will be used for building the SSM and will be linked to cells in the “SplitScreenStrip” sheet.

2. Module 2: Building the Project Split-Screen Strip

2.1. Structuring the Split-Screen Strip

As seen in Part I, four main areas are involved in a project: operating assets, non-operating (i.e., liquid) asset, debt capital, and equity capital. The law of motion holds for each of the four classes and may be represented with the four-area Matrix for each one of the $n$ periods, giving rise to a split-screen film or split-screen strip.

In Figure 5, the project film is displayed in a discrete format (sequence of matrices). Merging the last and first column of any two consecutive matrices, the project film may be reframed in a continuous format (Figure 6):6

This compact representation of the film of split-screen matrices in a continuous format is particularly convenient for spreadsheet modeling. The assumptions and the preliminary calculations enable building the project film in full scale—that is, the strip of the $n$ full-scale split-screen matrices (Figure 7).

CODE INSTRUCTIONS: STRUCTURE OF THE SPLIT-SCREEN STRIP

The analyst adds, in his workbook, a new sheet—named “SplitScreenStrip”—where he lays out a range of cells with the structure of the previous table (see Figure 8).

The table has a first group of columns representing a generic full-scale SSM: column D, E, and F report, respectively, the capital amounts, incomes, and cash flows.7 The following columns (from column G on) will contain the figures of the project, starting from period $t=-1$, before the beginning of the project. The total number of columns containing numerical values is $\left(3\cdot \left(n+1\right)+1\right)$. Each value is 0 in column G since the project has not started yet ($C_{-1}=0$).

Row 8 describes the kind of column (C, I or F) and row 9 contains the periods. Row 12 contains the labels of the columns, with specification of the category to which they belong ($C$, $I$, or $F$) and of the period ($-\mathrm{1,0},\dots ,n$). Each cell is identified by the labels. For example, cell K13 will contain the sales ($\mathrm{S}$) of period 1; cell M13 will contain the Accounts Receivable ($\mathrm{A}\mathrm{R}$) of period 1 (see Figure 9).

To build the whole split-screen film, one must follow two steps:

Step 1. The analyst fills in the cells of array H13:J23, which contain the values of all the 33 accounting and financial magnitudes that refer to period 0. These cells may be divided into four classes:

• Cells whose values are provided in “PreCalc” (step 1a)
• Cells whose values are calculated with the law of motion (step 1b)
• Cells whose values are calculated with suitable formulas (step 1c)
• Cells whose values are calculated with the law of conservation (step 1d)

We describe the calculations for all these cells in Section 2.2.

Step 2. The analyst fills the whole strip by copying the range H13:J23, which refer to the remaining periods (periods 1 to $n$). This is completed in few seconds by copying and pasting it in the adjacent ranges, or by dragging the fill handle (we show the procedure in Section 2.3).

2.2. Filling the Cells for Period 0 (Step 1)

2.2.1. Cells Whose Values Are Provided in “PreCalc” (Step 1a)

Preliminary calculations feed into the split-screen strip. To fill the cells of array H13:J23, the analyst should use nested functions INDEX and MATCH.8 This function is necessary because it makes the formulas parameterized and dependent on the cell where they are (using the references to the row and column labels): it will enable the analyst to drag and fill the whole strip, thereby completing the project film in few seconds (see Section 3.3).

In the code instructions below, we first provide the code for the general formula and then show how to use it for each one of the three economic elements: capital, income, and cash flow. For those who are not familiar with spreadsheet formulas, we suggest reading the online support pages of the software employed.

There are 15 cells to be filled (highlighted with yellow background in Figure 3): six of them are capital amounts, six are incomes, and three are cash flows (see

Table 4. Cells whose values are provided in “PreCalc” (yellow cells).

Cell	Symbol	Magnitude	Type
H13	${+ \mathrm{S}}_0$	Sales	Income
J13	${+ \mathrm{A}\mathrm{R}}_0$	Accounts Receivable	Capital
J14	${+ \mathrm{I}\mathrm{n}\mathrm{v}}_0$	Inventory	Capital
H15	$- {\mathrm{C}\mathrm{O}\mathrm{P}}_0^m$	$-$Cost of Purchases (manufacturing)	Income
J15	${- \mathrm{A}\mathrm{P}}_0^m$	$-$Accounts Payable (manufacturing)	Capital
H16	$- {\mathrm{C}\mathrm{O}\mathrm{P}}_0^{nm}$	$-$Cost of Purchases (nonmanufacturing)	Income
J16	$- {\mathrm{A}\mathrm{P}}_0^{mn}$	$-$Accounts Payable (nonmanufacturing)	Capital
H17	$- {\mathrm{L}\mathrm{C}}_0^m$	$-$Labor costs (manufacturing)	Income
I17	${+ F}_0^{swp,m}$	Payments to employees (manufacturing)	Cash flow
H18	${- \mathrm{L}\mathrm{C}}_0^{nm}$	Labor costs (nonmanufacturing)	Income
I18	${+ F}_0^{swp,nm}$	Payments to employees (nonmanufacturing)	Cash flow
H19	$- {\mathrm{D}\mathrm{e}\mathrm{p}}_0$	$-$Depreciation	Income
I19	$- F_0^{nfa}$	$-$Asset disposals (net of capital expenditures)	Cash flow
J20	${- \mathrm{T}\mathrm{P}}_0$	$-$Taxes Payable	Capital
J22	$+ C_0^d$	Debt	Capital

).

CODE INSTRUCTIONS: CELLS WHOSE VALUES ARE PROVIDED IN “PreCalc”

The analyst must pick the figures from the “PreCalc” sheet and insert them into the split-screen strip. For this operation, the analyst must use the nested formulas INDEX-MATCH.

The INDEX function

$$\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}\left(\mathrm{a}\mathrm{r}\mathrm{r}\mathrm{a}\mathrm{y},\mathrm{r}\mathrm{o}\mathrm{w}\_\mathrm{n}\mathrm{u}\mathrm{m},\mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\_\mathrm{n}\mathrm{u}\mathrm{m}\right)$$

returns the value in the cell of the array at the intersection of row_num and column_num.

The MATCH function

$$\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{l}\mathrm{o}\mathrm{o}\mathrm{k}\mathrm{u}\mathrm{p}\_\mathrm{v}\mathrm{a}\mathrm{l}\mathrm{u}\mathrm{e},\mathrm{l}\mathrm{o}\mathrm{o}\mathrm{k}\mathrm{u}\mathrm{p}\_\mathrm{a}\mathrm{r}\mathrm{r}\mathrm{a}\mathrm{y},\mathrm{m}\mathrm{a}\mathrm{t}\mathrm{c}\mathrm{h}\_\mathrm{t}\mathrm{y}\mathrm{p}\mathrm{e}\right)$$

returns the position of the matched value within lookup_array, finding the first value that is exactly equal to lookup_value, if the optional argument is 0.

Hence, the combined formula

$$\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}\left(\mathrm{P}\mathrm{r}\mathrm{e}\mathrm{C}\mathrm{a}\mathrm{l}\mathrm{c}!\mathrm{\$}\mathrm{K}:\mathrm{\$}\mathrm{P},\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{l}\mathrm{o}\mathrm{o}\mathrm{k}\mathrm{u}\mathrm{p}\_\mathrm{v}\mathrm{a}\mathrm{l}\mathrm{u}\mathrm{e},\mathrm{l}\mathrm{o}\mathrm{o}\mathrm{k}\mathrm{u}\mathrm{p}\_\mathrm{a}\mathrm{r}\mathrm{r}\mathrm{a}\mathrm{y},0\right),\mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\_\mathrm{n}\mathrm{u}\mathrm{m}\right)$$

returns a value within the range PreCalc!K:P, that is, the columns which contain the amounts preliminarily calculated in “PreCalc” sheet. The row of this value must be the one where the label in columns D, E, or F matches with the corresponding label in the split-screen strip. The column must be the $\left(t+1\right)$-th column, where $t$ is the period (i.e., first column for t = 0, second column for $t=1$, etc.). The formulas are similar for three types of components: capital, income, and cash flow. Following are some examples of spreadsheets codes.

For an income component: lookup_value is the label in column SplitScreenStrip!E and lookup_array is the column PreCalc!E. For example, the code for cell H13 is

$$\mathrm{H}13=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(\underset{\begin{array}{c}\mathrm{array}:\\ \mathrm{columns} \mathrm{group} \mathrm{of} \mathrm{the}\\ \mathrm{amounts} \mathrm{calculated}\\ \mathrm{in} \mathrm{worksheet} “\mathrm{PreCalc}”\end{array}}{\underbrace{\mathrm{P}\mathrm{r}\mathrm{e}\mathrm{C}\mathrm{a}\mathrm{l}\mathrm{c}!\mathrm{\$}\mathrm{K}:\mathrm{\$}\mathrm{P}}},\underset{\begin{array}{c}\mathrm{the} \mathrm{result} \mathrm{of} \mathrm{this} \mathrm{formula} \mathrm{is} \mathrm{the} \mathrm{number}\\ \mathrm{of} \mathrm{the} \mathrm{row} \mathrm{in} \mathrm{the} \mathrm{worksheet} “\mathrm{PreCalc}” \mathrm{with}\\ \mathrm{the} \mathrm{same} \mathrm{label} \mathrm{of} \mathrm{cell} \mathrm{E}13\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{E}13,\mathrm{P}\mathrm{r}\mathrm{e}\mathrm{C}\mathrm{a}\mathrm{l}\mathrm{c}!\mathrm{\$}\mathrm{E}:\mathrm{\$}\mathrm{E},0\right)}},\underset{\begin{array}{c}\mathrm{the} \left(\mathrm{t}+1\right)-\mathrm{th} \mathrm{column},\\ \mathrm{where} t \mathrm{is} \mathrm{the} \mathrm{period} \\ \left(\mathrm{first} \mathrm{column} \mathrm{for} \mathrm{period} 0\right)\\ \mathrm{in} \mathrm{the} \mathrm{columns} \mathrm{group} \mathrm{of} \mathrm{the}\\ \mathrm{worksheet} “\mathrm{PreCalc}”\end{array}}{\underbrace{\mathrm{H}\mathrm{\$}9+1}})$$

(see Figure 10 and Figure 11).

For a cash-flow component: lookup_value is the label in column SplitScreenStrip!F, and lookup_array is the column PreCalc!F. For example, the code for cell I17 is

$$\mathrm{I}17=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}\left(\mathrm{P}\mathrm{r}\mathrm{e}\mathrm{C}\mathrm{a}\mathrm{l}\mathrm{c}!\mathrm{\$}\mathrm{K}:\mathrm{\$}\mathrm{P},\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{F}17,\mathrm{P}\mathrm{r}\mathrm{e}\mathrm{C}\mathrm{a}\mathrm{l}\mathrm{c}!\mathrm{\$}\mathrm{F}:\mathrm{\$}\mathrm{F},0\right),\mathrm{I}\mathrm{\$}9+1\right)$$

For a capital component: lookup_value is the label in column SplitScreenStrip!D, and lookup_array is the column PreCalc!D. For example, the code for cell J15 is

$$\mathrm{J}15=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}\left(\mathrm{P}\mathrm{r}\mathrm{e}\mathrm{C}\mathrm{a}\mathrm{l}\mathrm{c}!\mathrm{\$}\mathrm{K}:\mathrm{\$}\mathrm{P},\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{D}15,\mathrm{P}\mathrm{r}\mathrm{e}\mathrm{C}\mathrm{a}\mathrm{l}\mathrm{c}!\mathrm{\$}\mathrm{D}:\mathrm{\$}\mathrm{D},0\right),\mathrm{J}\mathrm{\$}9+1\right)$$

(see also Figure 12).

Every cell in Table 4 should be filled using the formula INDEX-MATCH. Figure 12 shows the output of this step.

2.2.2. Cells Whose Values Are Calculated with the Law of Motion (Step 1b)

The second class of cells in array H13:J23 is the class of those cells which must be filled with the law of motion. To fill in these values, the analyst should use the law of motion for $t$ = 0 (see Part I, Equation (2)). Specifically, to find a capital amount, the law of motion is expressed as

$$C_0=C_{-1}+I_0-F_0.$$

To find an income amount, the law of motion becomes

$$I_0=C_0-C_{-1}+F_0.$$

To find a cash-flow component, the law of motion becomes

$$F_0=C_0-C_{-1}+I_0.$$

Applying these laws of motion, the cells of this class are calculated, with their own sign (see

Table 5. Cells whose values are calculated with the law of motion (green cells).

Cell	Symbol	Magnitude	Law of Motion	Type
H14	$+ \Delta {\mathrm{I}\mathrm{n}\mathrm{v}}_0$	Income from inventory	$+ \Delta {\mathrm{I}\mathrm{n}\mathrm{v}}_0={\mathrm{I}\mathrm{n}\mathrm{v}}_0-{\mathrm{I}\mathrm{n}\mathrm{v}}_{-1}+(-0)$	Income
I13	$- F_0^{ar}$	$-$Cash receipts from customers	$- F_0^{ar}={\mathrm{A}\mathrm{R}}_0-{\mathrm{A}\mathrm{R}}_{-1}-{\mathrm{S}}_0$	Cash flow
I15	$+ F_0^{ap,m}$	Payments to suppliers (manufacturing)	$+ F_0^{ap,m}=-{\mathrm{A}\mathrm{P}}_0^m+{\mathrm{A}\mathrm{P}}_{-1}^m+{\mathrm{C}\mathrm{O}\mathrm{P}}_t^m$	Cash flow
I16	$+ F_0^{ap,nm}$	Payments to suppliers (nonmanufacturing)	$+ F_0^{ap,nm}=-{\mathrm{A}\mathrm{P}}_0^{nm}+{\mathrm{A}\mathrm{P}}_{-1}^{nm}+{\mathrm{C}\mathrm{O}\mathrm{P}}_0^{nm}$	Cash flow
I20	$+ F_0^{tp}$	Tax payments	$+ F_0^{tp}=-{\mathrm{T}\mathrm{P}}_0+{\mathrm{T}\mathrm{P}}_{-1}+{\mathrm{T}}_0$	Cash flow
I22	$- F_0^d$	$-$Cash flow to debt	$- F_0^d=C_0^d-C_{-1}^d-I_0^d$	Cash flow
J17	$- {\mathrm{S}\mathrm{W}\mathrm{P}}_0^m$	$-$Salaries & Wages Payable (manufacturing)	$- {\mathrm{S}\mathrm{W}\mathrm{P}}_0^m=-{\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}^m-{\mathrm{L}\mathrm{C}}_0^m+F_0^{swp,m}$	Capital
J18	$- {\mathrm{S}\mathrm{W}\mathrm{P}}_0^{nm}$	$-$Salaries & Wages Payable (nonmanufacturing)	$- {\mathrm{S}\mathrm{W}\mathrm{P}}_0^{nm}=-{\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}^{nm}-{\mathrm{L}\mathrm{C}}_0^{nm}+F_0^{swp,nm}$	Capital
J19	${+ \mathrm{N}\mathrm{F}\mathrm{A}}_0$	Net Fixed Asset	${+ \mathrm{N}\mathrm{F}\mathrm{A}}_0={\mathrm{N}\mathrm{F}\mathrm{A}}_{-1}-{\mathrm{D}\mathrm{e}\mathrm{p}}_0+(-F_0^{nfa})$	Capital
J21	${+ C}_0^l$	Liquid assets	${+ C}_0^l=C_{-1}^l+I_0^l-F_0^l$	Capital
J23	${+ C}_0^e$	Equity	${+ C}_0^e=C_{-1}^e+I_0^e-F_0^e$	Capital

).

Note that the application of the law of motion for J21 supplies a value equal to zero, because $I_0^l$ and $F_0^l$ (respectively, cell H21 and I21) have not been calculated yet. However, the values of J21 will be automatically updated as soon as H21 and I21 will be calculated in the next steps. The same holds for J23, I20, and I22—cells that temporarily can exhibit an incorrect value—because one of the cells linked in the formula is not yet calculated. The output of this step is displayed in Figure 13 (the cells of the class are highlighted in green).

CODE INSTRUCTIONS: CELLS WHOSE VALUES ARE CALCULATED WITH THE LAW OF MOTION

EXAMPLE OF CAPITAL COMPONENT. The (changed-in-sign) salaries and wages payable at $t$ = 0 in cell J17 is obtained as follows:

$$\underset{-{\mathrm{S}\mathrm{W}\mathrm{P}}_0^m}{\underbrace{\mathrm{J}17}}=\underset{-{\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}^m}{\underbrace{\mathrm{G}17}}+\underset{-{\mathrm{L}\mathrm{C}}_0^m}{\underbrace{\mathrm{H}17}}+\underset{+F_0^{swp,m}}{\underbrace{\mathrm{I}17}}$$

(see Figure 13).

The analyst can copy cell J17 and paste it in cells J18, J19, J21, and J23 (the equations share the same structure).

EXAMPLE OF INCOME COMPONENT. The income from inventory in cell H14 is just equal to $\Delta {\mathrm{I}\mathrm{n}\mathrm{v}}_0$ (because the cash-flow component of inventory is zero):

$$\underset{+{∆\mathrm{I}\mathrm{n}\mathrm{v}}_0}{\underbrace{\mathrm{H}14}}=\underset{+{\mathrm{I}\mathrm{n}\mathrm{v}}_0}{\underbrace{\mathrm{J}14}}-\underset{+{\mathrm{I}\mathrm{n}\mathrm{v}}_{-1}}{\underbrace{\mathrm{G}14}}+\underset{-F_0^{Inv}}{\underbrace{\mathrm{I}14}}$$

with $F_0^{Inv}=0$ (see Figure 14).

EXAMPLE OF CASH-FLOW COMPONENT. The (changed-in-sign) cash receipts from customers $-F_0^{ar}$ in cell I13 are obtained as follows:

$$\underset{-F_0^{ar}}{\underbrace{\mathrm{I}13}}=\underset{+{\mathrm{A}\mathrm{R}}_0}{\underbrace{\mathrm{J}13}}-\underset{+{\mathrm{A}\mathrm{R}}_{-1}}{\underbrace{\mathrm{G}13}}-\underset{+\mathrm{S}}{\underbrace{\mathrm{H}13}}$$

(see Figure 15).

The analyst can copy cell I13 and paste it in cells I15, I16, I20, and I22 (the equations share the same structure).

2.2.3. Cells Whose Values Are Calculated with Suitable Formulas (Step 1c)

The third class of cells consists of those cells that can be calculated via suitable, specific formulas. These are the taxes, interest income, interest expenses, and CFE (see

Table 6. Cells whose values are calculated via suitable formulas (blue cells).

Cell	Symbol	Magnitude	Formula	Type
H20	${\mathrm{T}}_t$	Taxes	${\mathrm{T}}_t={\mathrm{E}\mathrm{B}\mathrm{T}}_t\cdot \tau$	Income
H21	$I_t^l$	Interest income	$I_t^l=C_{t-1}^l·i^l$	Income
H22	$I_t^d$	Interest expenses	$I_t^d=C_{t-1}^d·i^d$	Income
I23	$F_t^e$	Cash flow to equity	depends on the period	Cash flow

and description below).

TAXES. The amount of Taxes (${\mathrm{T}}_t)$ is not known, but the assumptions provide the necessary information for calculating it. The tax amount is computed applying the tax rate to earnings before taxes (EBT):

$${\mathrm{T}}_t={\mathrm{E}\mathrm{B}\mathrm{T}}_t\cdot \tau$$

where $\tau$ is the tax rate, equal to $30\mathrm{\%}$ (by Assumption 15) and EBT is equal to $\mathrm{E}\mathrm{B}{\mathrm{T}}_t={\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}}_t+I_t^l-I_t^d$, so that

$${\mathrm{T}}_t=\left({\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}}_t+I_t^l-I_t^d\right)\cdot \tau$$

where ${\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}}_t$ is obtained as ${\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}}_t={\mathrm{S}}_t-{\mathrm{C}\mathrm{O}\mathrm{G}\mathrm{S}}_t-{\mathrm{S}\mathrm{G}\mathrm{A}}_t-{\mathrm{D}\mathrm{e}\mathrm{p}}_t$ (Part I, Equation (47)). Hence,

$${\mathrm{T}}_t=({\mathrm{S}}_t\stackrel{{\mathrm{C}\mathrm{O}\mathrm{G}\mathrm{S}}_t}{-(\overbrace{{\mathrm{C}\mathrm{O}\mathrm{P}}_t^m+{\mathrm{L}\mathrm{C}}_t^m-{∆\mathrm{I}\mathrm{n}\mathrm{v}}_t})}-(\stackrel{{\mathrm{S}\mathrm{G}\mathrm{A}}_t}{\overbrace{{\mathrm{C}\mathrm{O}\mathrm{P}}_t^{nm}+{\mathrm{L}\mathrm{C}}_t^{nm}}})-{\mathrm{D}\mathrm{e}\mathrm{p}}_t+I_t^l-I_t^d)·\tau$$

(see also Part I, footnote 7). All the values in this formula have already been computed or will be computed in the next steps (i.e., $I_t^l$ and $I_t^d$).

CODE INSTRUCTIONS: TAXES

The tax rate $\tau$ is recorded in “Assumption” sheet: the analyst can show it in a cell above the Matrix in the “SplitScreenStrip” sheet (e.g., in cell D3) and then use it in the formulas (see Figure 16).

Cell D3 is linked to the cell that contains the value of $\tau$ with the formula

$$\mathrm{D}3=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}19.$$

The (changed-in-sign) taxes, $-{\mathrm{T}}_t$, are calculated in cell H20:

$$\underset{-{\mathrm{T}}_0}{\underbrace{\mathrm{H}20}}=\left(-1\right) \ast \mathrm{S}\mathrm{U}\mathrm{M}(\underset{\begin{array}{c}+{\mathrm{S}}_0+{∆\mathrm{I}\mathrm{n}\mathrm{v}}_0\\ -{\mathrm{C}\mathrm{O}\mathrm{P}}_0^m-{\mathrm{C}\mathrm{O}\mathrm{P}}_0^{nm}\\ -{\mathrm{L}\mathrm{C}}_0^m-{\mathrm{L}\mathrm{C}}_0^{nm}\\ -{\mathrm{D}\mathrm{e}\mathrm{p}}_0\end{array}}{\underbrace{\mathrm{H}13:\mathrm{H}19}},\underset{+I_0^l}{\underbrace{\mathrm{H}21}},\underset{-\left(+I_0^d\right)}{\underbrace{-\mathrm{H}22}}) \ast \underset{\tau }{\underbrace{\mathrm{\$}\mathrm{D}\mathrm{\$}3}}.$$

while the equation is correct, the value obtained in H20 is not yet the correct one, because cells H21 ($+I_0^l$) and H22 ($-I_0^d$) have not yet been occupied by formulas; however, they will be filled with the amounts of interest income and expenses in the next step, and the value taken on by cell H20 will be automatically updated (see Figure 16).

INTEREST INCOME. The amounts of interest expenses ($I_t^l$) are not known, but the assumptions provide sufficient information for calculating it by applying the interest rate to the beginning-of-period balance of liquid assets:

$$I_t^l=C_{t-1}^l·i^l$$

where $i^l$ is the interest rate on liquid assets, equal to 3.80% (by assumption 21).

CODE INSTRUCTIONS: INTEREST INCOME

The interest rate $i^l$ is recorded in the “Assumption” sheet. The analyst can show it in a cell above the Matrix in the “SplitScreenStrip” sheet (e.g., in cell D4) and then use it in the formulas (see Figure 15). Cell D4 is linked to the cell that contains the value of interest rate $i^l$ with the formula

$$\mathrm{D}4=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}25$$

The interest income $I_t^l$ is calculated in cell H22:

$$\underset{+I_1^l}{\underbrace{\mathrm{H}21}}=\underset{+C_0^l}{\underbrace{\mathrm{G}21}} \ast \underset{i^l}{\underbrace{\mathrm{\$}\mathrm{D}\mathrm{\$}4}}$$

(see Figure 17).

INTEREST EXPENSES. The amounts of interest expenses ($I_t^d$) are not known, but the assumptions provide sufficient information for calculating it by applying the interest rate to the beginning-of-period principal outstanding:

$$I_t^d=C_{t-1}^d·i^d$$

where $i^d$ is the interest rate of the loan, equal to 2% (by assumption 19).

CODE INSTRUCTIONS: INTEREST EXPENSES

The interest rate $i^d$ is recorded in the “Assumption” sheet: the analyst can show it in a cell above the Matrix in the “SplitScreenStrip” sheet (e.g., in cell D5) and then use it in the formulas (see Figure 18). Cell D5 is linked to the cell that contains the value of interest rate $i^d$ with the formula

$$\mathrm{D}5=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}23.$$

The interest expenses $I_t^d$ is calculated in cell H22:

$$\underset{+I_1^d}{\underbrace{\mathrm{H}22}}=\underset{+C_0^d}{\underbrace{\mathrm{G}22}} \ast \underset{i^d}{\underbrace{\mathrm{\$}\mathrm{D}\mathrm{\$}5}}$$

(see Figure 18).

CASH FLOW TO EQUITY. For simplifying the spreadsheet calculation for the CFE ($F_t^e$), it is convenient to break down the CFE into three categories (equity financing, interim payout, and liquidation dividend) and, hence, build the stream of cash flows for each such category. Equity financing is equal to USD 6,000 for $t=0$ (i.e., the cash flow initially contributed by shareholders is $F_0^e=-\mathrm{6,000}$) and equal to 0 for $t>0$ (the firm’s shareholders will not be required to make any other contribution) (assumption 17). The interim payout distributions are equal to $\alpha =$ 20% of the project’s net income for $1<t<5$ (assumption 22). As shown in Part I, the liquidation dividend, $F_5^e$, may be obtained as the sum of the equity at time $t=4$ and the net income at time $t=5$ (Part I, Equation (62)), as displayed in

Table 7. Breakdown of CFE (payout ratio = 20% of net income).

	TIME	0	1	2	3	4	5
CASH FLOW TO EQUITY ($F_t^e$)	Shareholders’ investment	$-\mathrm{6,000}$	0	0	0	0	0
	Interim payout (20% of net income)	0	$0.2\cdot I_1^e$	$0.2\cdot I_2^e$	$0.2\cdot I_3^e$	$0.2\cdot I_4^e$	0
	Liquidation dividend	0	0	0	0	0	$C_4^e+I_5^e$

. Alternatively, one can calculate it as the sum of FCFE and the last non-operating cash flow (see Part I, Equation (61) and Section 4.1 below).

It is worth noting here that the interim payout is a decision variable which affects the project’s value and, as such, the project’s economic profitability. Such a variable is often neglected in traditional financial modeling, where the project’s value is not calculated by discounting the actual project CFEs but calculated by discounting the project FCFEs. However, this boils down to assuming that the payout ratio is 100% and that such a ratio is applied to FCFE. In real life, the payout ratio may be different (even 0%) and may be applied, commonly, to the net income, the FCFE, or other bases (see the Appendix A; see also Part I, Section 9).

CODE INSTRUCTIONS: CASH FLOW TO EQUITY

The analyst must fill cell I23 with a single formula that should consider that, depending on time $t$, either of the three categories of CFE applies. The project length and the payout ratio are recorded in the “Assumption” sheet: the analyst can show it in a cell above the Matrix in the “SplitScreenStrip” sheet (e.g., cell D2 and cell D6) and then use it in the formulas (see Figure 19). Cells D2 and D6 are linked to the cells that contain the values of project length and payout ratio with the respective formulas $=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}5$ and $=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}26$.

The spreadsheet code for I23 is:

$$\stackrel{-F_0^e}{\overbrace{\mathrm{I}23}}=\mathrm{I}\mathrm{F}(\stackrel{\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{o}\mathrm{d}}{\overbrace{\mathrm{I}9}}=0,\underset{\mathrm{c}\mathrm{a}\mathrm{s}\mathrm{e} t=0}{\underbrace{\stackrel{\mathrm{e}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{t}\mathrm{y} \mathrm{f}\mathrm{i}\mathrm{n}\mathrm{a}\mathrm{n}\mathrm{c}\mathrm{i}\mathrm{n}\mathrm{g}}{\overbrace{\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{H}\mathrm{\$}21}}}},\underset{\mathrm{c}\mathrm{a}\mathrm{s}\mathrm{e} t\ne 0}{\underbrace{\mathrm{I}\mathrm{F}(\stackrel{\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{o}\mathrm{d}}{\overbrace{\mathrm{I}9}}<\stackrel{n}{\overbrace{\mathrm{\$}\mathrm{D}\mathrm{\$}2}},\underset{\mathrm{c}\mathrm{a}\mathrm{s}\mathrm{e} 0<t<n}{\underbrace{\stackrel{\mathrm{i}\mathrm{n}\mathrm{t}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{m} \mathrm{p}\mathrm{a}\mathrm{y}\mathrm{o}\mathrm{u}\mathrm{t}}{\overbrace{-\mathrm{H}23 \ast \mathrm{\$}\mathrm{D}\mathrm{\$}6}}}},\stackrel{\mathrm{l}\mathrm{i}\mathrm{q}\mathrm{u}\mathrm{i}\mathrm{d}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n} \mathrm{d}\mathrm{i}\mathrm{v}\mathrm{i}\mathrm{d}\mathrm{e}\mathrm{n}\mathrm{d}}{\overbrace{\underset{\mathrm{c}\mathrm{a}\mathrm{s}\mathrm{e} t=n}{\underbrace{–(\mathrm{G}23+\mathrm{H}23)}}}})}})$$

The payout is calculated as a function of the net income. However, as shown in Part I, other schemes are possible. For example, the payout may be a fraction of the FCFE or a fraction of the minimum between net income and FCFE. In Appendix A of this paper, we show how to cope with these alternative schemes.

2.2.4. Cells Whose Values Are Calculated with the Law of Conservation (Step 1d)

The fourth class of cells in array H13:J23 consists of those cells which can be calculated by means of the law of conservation. These are the net income and the cash flow from non-operating (liquid) assets (see

Table 8. Cells whose values are calculated with the law of conservation (pink orange cells).

Cell	Symbol	Magnitude	Law of Conservation	Type
H20	$I_t^e$	Net Income	$I_t^e=I_t^o+I_t^l-I_t^d$	Income
H21	$- F_t^l$	$-$Cash flow from liquid assets	${- F}_t^l=F_t^o-F_t^d-F_t^e$	Cash flow

).

NET INCOME

Owing to the law of conservation, $I_t^o+I_t^l=I_t^d+I_t^e$ (see Part I, Equation (10)) or, breaking down the after-tax operating profit,

$$\stackrel{I_t^o}{\overbrace{{\mathrm{S}}_t+{∆\mathrm{I}\mathrm{n}\mathrm{v}}_t-{\mathrm{C}\mathrm{O}\mathrm{P}}_t^m-{\mathrm{C}\mathrm{O}\mathrm{P}}_t^{nm}-{\mathrm{L}\mathrm{C}}_t^m-{\mathrm{L}\mathrm{C}}_t^{nm}-{\mathrm{D}\mathrm{e}\mathrm{p}}_t-{\mathrm{T}}_t}}+I_t^l=+I_t^d+I_t^e$$

(see also Part I, Equation (15)), whence

$$I_t^e=+{\mathrm{S}}_t+{∆\mathrm{I}\mathrm{n}\mathrm{v}}_t-{\mathrm{C}\mathrm{O}\mathrm{P}}_t^m-{\mathrm{C}\mathrm{O}\mathrm{P}}_t^{nm}-{\mathrm{L}\mathrm{C}}_t^m-{\mathrm{L}\mathrm{C}}_t^{nm}-{\mathrm{D}\mathrm{e}\mathrm{p}}_t-{\mathrm{T}}_t+I_t^l-I_t^d$$

CODE INSTRUCTIONS: NET INCOME

Net Income $I_t^e$ is calculated in cell H23:

$$\underset{I_0^e}{\underbrace{\mathrm{H}23}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{\begin{array}{c}+{\mathrm{S}}_0+{∆\mathrm{I}\mathrm{n}\mathrm{v}}_0\\ -{\mathrm{C}\mathrm{O}\mathrm{P}}_0^m-{\mathrm{C}\mathrm{O}\mathrm{P}}_0^{nm}\\ -{\mathrm{L}\mathrm{C}}_0^m-{\mathrm{L}\mathrm{C}}_0^{nm}\\ -{\mathrm{D}\mathrm{e}\mathrm{p}}_0-T_0+I_0^l\end{array}}{\underbrace{\mathrm{H}13:\mathrm{H}21}},-\underset{I_0^d}{\underbrace{\mathrm{H}22}})$$

(see Figure 20).

CASH FLOW FROM LIQUID ASSETS. Owing to the law of conservation (see Part I, Equation (11)), $F_t^o+F_t^l=F_t^d+F_t^e$ or, breaking down the operating cash flow,

$$\stackrel{-F_t^o}{\overbrace{-F_t^{ar}-0+F_t^{ap,m}+F_t^{ap,nm}+F_t^{swp,m}+F_t^{swp,nm}-F_t^{nfa}+F_t^{tp}}}-F_t^l=-F_t^d-F_t^e$$

(see also Part I, Equation (16)), whence

$$-F_t^l=(F_t^{ar}-F_t^{ap,m}-F_t^{ap,nm}-F_t^{swp,m}-F_t^{swp,nm}+F_t^{nfa}-F_t^{tp})-F_t^d-F_t^e.$$

CODE INSTRUCTIONS: CASH FLOW FROM LIQUID ASSETS

Here is an example of how to calculate the cash flow from/to liquid assets $F_t^l$ in cell I21:

$$\underset{{-F}_0^l}{\underbrace{\mathrm{I}21}}=-\mathrm{S}\mathrm{U}\mathrm{M}(\underset{\begin{array}{c}-F_0^{ar}+F_0^{ap,m}\\ +F_0^{ap,nm}+F_0^{swp,m}\\ +F_0^{swp,nm}-F_0^{nfa}\\ +F_0^{tp}\end{array}}{\underbrace{\mathrm{I}13:\mathrm{I}20}})\underset{-F_0^d}{+\underbrace{\mathrm{I}22}}\underset{-F_0^e}{+\underbrace{\mathrm{I}23}}$$

(see Figure 21).

2.3. Filling the Cells for Periods 1 through $n$ (Step 2)

The law of motion and the law of conservation hold for all $t=\mathrm{0,1},\dots ,n:$ the structure of all the SSMs is the same. Hence, once the cells for period 0 have been filled, it suffices to extend the same formulas for periods 1 through $n$ to complete the project split-screen film.

CODE INSTRUCTIONS: FROM THE MATRIX TO THE STRIP

The analyst can copy the range H13:J23 and paste it in the ranges K13:Y23. Alternatively, one can copy the formulas into adjacent cells by using the fill handle. This technique works properly because the formulas do not contain embedded data or constants (the formulas must contain the links to the cells in the “Assumptions” or in “PreCalc” sheet, and they must be parameterized using the labels of row and of column). Operationally, the analyst can proceed as follows:

• Select the range of cells H13:J23
• Hover your cursor over the lower-right corner so that it turns into a plus sign (+) (the “fill handle”)
• Drag the fill handle right to cover all the cells up to column Y
• Let it go and the formulas are automatically filled in the other columns of the split-screen strip

(see Figure 22).

The result of this step is the whole strip of the project, populated with the correct figures (Figure 23).

3. Module 3: Building the Strips of Other Matrix Representations

Once constructed the full-scale strip, more compact representations of the strip can be drawn from it to act as summarized statements highlighting specific accounting and/or financial magnitudes that may be of interest to the analyst. It suffices to use the split-screen technique described in Part I, Section 4. Among the many framings that the SSM can take, we only focus on the SSM with costs classified by nature, SSM with costs classified by function, SSM showing the net operating liabilities, and SSM showing the net operating working capital (see also Part I for a detailed description). These framings are easily obtained via consolidation of rows (Rule 5 of the split-screen technique).

3.1. Expanded Matrices

If the analyst wants to build an expanded matrix and the related strip, it suffices to consolidate the relevant items of the full-scale matrix and related strip. Alternatively, the analyst may start from some other (higher-scale) matrix representation to obtain the same result.

THE EXPANDED MATRIX BY NATURE. Suppose the analyst wants to build the expanded matrix by nature and the associated strip (see Part I, Equation (33)). To do so, it suffices to consolidate, respectively, the rows pertaining to the firm’s suppliers and the rows pertaining to the firm’s employees (see Figure 24).

CODE INSTRUCTIONS: FROM FULL-SCALE MATRIX TO EXPANDED MATRIX (BY NATURE)

The analyst lays out a range of cells in the “SplitScreenStrip” sheet (rows 25:35) with the structure of the full-scale matrix (rows 11:23), but with fewer rows. More precisely, on one hand, the analyst merges the rows pertaining to the suppliers (rows 15:16) into one row (row 29); on the other hand, the analyst merges the rows pertaining to the employees (rows 17:18) into one row (row 30) by means of a simple sum of the relevant items.

Operationally, one first fills column G (cells G27:G35). To do so, one consolidates the manufacturing and nonmanufacturing AP:

$$\underset{-{\mathrm{A}\mathrm{P}}_{-1}}{\underbrace{\mathrm{G}29}}=\underset{-{\mathrm{A}\mathrm{P}}_{-1}^m}{\underbrace{\mathrm{G}15}}+\underset{-{\mathrm{A}\mathrm{P}}_{-1}^{nm}}{\underbrace{\mathrm{G}16}}$$

and the manufacturing and nonmanufacturing SWP:

$$\underset{-{\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}}{\underbrace{\mathrm{G}30}}=\underset{{-\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}^m}{\underbrace{\mathrm{G}17}}+\underset{{-\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}^{nm}}{\underbrace{\mathrm{G}18}}$$

The remaining cells of column G remain unvaried, so one just need to add the proper link. For example, as regards the AR,

$$\underset{{\mathrm{A}\mathrm{R}}_{-1}}{\underbrace{\mathrm{G}27}}=\underset{{\mathrm{A}\mathrm{R}}_{-1}}{\underbrace{\mathrm{G}13}}.$$

Once column G has been filled, the analyst needs only copy and paste (or drag the fill handle) to fill the cells of all periods (see Figure 25).

THE EXPANDED MATRIX BY FUNCTION. Suppose the analyst wants to build the expanded matrix and the associated strip by function (see Part I, Equation (36)). To do so, it suffices to consolidate the rows pertaining to the manufacturing items and the rows pertaining to the nonmanufacturing items (see Figure 26).

CODE INSTRUCTIONS: FROM FULL-SCALE MATRIX TO EXPANDED MATRIX (BY FUNCTION)

The analyst prepares a range of cells in the “SplitScreenStrip” sheet (rows 37:46) with the structure of the full-scale Matrix (rows 11:23), but with fewer rows. More precisely, on one hand, the analyst merges the inventory and the manufacturing costs (rows 14, 15, and 17) into one row (row 40); on the other hand, the analyst merges the nonmanufacturing costs (rows 16 and 18) into one row (row 41). Consolidation is obtained by means of a simple sum of the relevant items. Operationally, one first fills column G (cells G39:G46). To do so, one consolidates the manufacturing book values,

$$\underset{-{\mathrm{N}\mathrm{O}\mathrm{L}}_{-1}^m}{\underbrace{\mathrm{G}40}}=\underset{{\mathrm{I}\mathrm{n}\mathrm{v}}_{-1}}{\underbrace{\mathrm{G}14}}+\underset{{-\mathrm{A}\mathrm{P}}_{-1}^m}{\underbrace{\mathrm{G}15}}+\underset{{-\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}^m}{\underbrace{\mathrm{G}17}}$$

and the nonmanufacturing book values,

$$\underset{-{\mathrm{N}\mathrm{O}\mathrm{L}}_{-1}^{nm}}{\underbrace{\mathrm{G}41}}=\underset{{-\mathrm{A}\mathrm{P}}_{-1}^{nm}}{\underbrace{\mathrm{G}16}}+\underset{{-\mathrm{S}\mathrm{W}\mathrm{P}}_{-1}^{nm}}{\underbrace{\mathrm{G}18}}$$

The remaining cells of column G remain unvaried, so one just needs to add the proper link. For example, as regards the AR,

$$\underset{{\mathrm{A}\mathrm{R}}_{-1}}{\underbrace{\mathrm{G}39}}=\underset{{\mathrm{A}\mathrm{R}}_{-1}}{\underbrace{\mathrm{G}13}}$$

Once columns G is filled, the analyst needs only copy and paste (or drag the fill handle) to fill the cells of all periods (see Figure 27).

3.2. NOL-Framed and NOWC-Framed Matrices

THE NOL-FRAMED MATRIX. If one wants to build a NOL-framed matrix and associated strip, one may simply aggregate the items of the manufacturing and nonmanufacturing NOL obtained in the expanded matrix (Figure 27, rows 40 and 41; also see Part I, Equation (39)). Alternatively, starting from the full-scale matrix, one consolidates the rows pertaining to the net operating liabilities.

CODE INSTRUCTIONS: FROM EXPANDED MATRIX (BY FUNCTION) TO NOL-FRAMED MATRIX

The analyst lays out a range of cells in the “SplitScreenStrip” sheet (rows 48:56) with the structure of the expanded matrix (by function) (rows 37:46), but with fewer rows. Specifically, the analyst merges the rows pertaining to the net operating liabilities (rows 40:41) into one single row (row 51). Consolidation is obtained by means of a simple sum of the relevant items. Operationally, one proceeds as made for the previous matrix representations by first filling column G (cells G50:G56). To do so, one consolidates the rows of the expanded matrix (by function) that form the group of net operating liabilities:

$$\underset{-{\mathrm{N}\mathrm{O}\mathrm{L}}_{-1}}{\underbrace{\mathrm{G}51}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{-{\mathrm{N}\mathrm{O}\mathrm{L}}_{-1}^m-{\mathrm{N}\mathrm{O}\mathrm{L}}_{-1}^{nm}}{\underbrace{\mathrm{G}41:\mathrm{G}42}})$$

The remaining cells of column G remain unvaried, so one just needs to add the proper links. For example, as regards the AR,

$$\underset{{\mathrm{A}\mathrm{R}}_{-1}}{\underbrace{\mathrm{G}50}}=\underset{{\mathrm{A}\mathrm{R}}_{-1}}{\underbrace{\mathrm{G}39}}$$

Once column G has been filled, the analyst needs only copy and paste (or drag the fill handle) to fill the cells of all periods (see Figure 28).

THE NOWC-FRAMED MATRIX. If one wants to build a NOWC-framed matrix and the corresponding strip, one may simply aggregate the relevant rows starting from the NOL-framed matrix. This result in a five-area matrix (see Part I, Equation (50)).

CODE INSTRUCTIONS: FROM NOL-FRAMED MATRIX TO NOWC-FRAMED MATRIX

The analyst lays out a range of cells structure of the NOL-framed matrix (rows 48:56), but with fewer rows. Specifically, the analyst merges the rows pertaining to net operating working capital (rows 50:51 and 53) into one single row (row 60). Consolidation is obtained by means of a simple sum of the relevant items. Operationally, one proceeds as made for the previous matrix representations by first filling column G (cells G60:G64). To do so, one consolidates the rows in the full-scale matrix which form the group net operating working capital:

$$\underset{+{\mathrm{N}\mathrm{O}\mathrm{W}\mathrm{C}}_{-1}}{\underbrace{\mathrm{G}60}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{{{\mathrm{A}\mathrm{R}}_{-1}-\mathrm{N}\mathrm{O}\mathrm{L}}_{-1}-{\mathrm{T}\mathrm{P}}_{-1}}{\underbrace{\mathrm{G}50:\mathrm{G}51,\mathrm{G}53}})$$

The remaining cells of column G remain unvaried, so one just needs to add the proper links. For example, as regards NFA,

$$\underset{{\mathrm{N}\mathrm{F}\mathrm{A}}_{-1}}{\underbrace{\mathrm{G}61}}=\underset{{\mathrm{N}\mathrm{F}\mathrm{A}}_{-1}}{\underbrace{\mathrm{G}52}}$$

Once column G is filled, the analyst needs only copy and paste (or drag the fill handle) to fill the cells of all periods (see Figure 29).

3.3. Four-Area and Germ Matrices

It is possible to further shrink the strip and have more compact forms. For example, to favor a synthetic analysis of the project, the analyst may be willing to build a four-area matrix and corresponding strip, where one only the elements related to operating assets, non-operating assets, debt, and equity are reported (Part I, right matrix in Equation (12)). Furthermore, merging the two classes of assets and the two classes of financings, one may even build the germ strip, where all the accounting and financial items of the project are aggregated (Part I, right matrix in Equation (8)).

THE FOUR-AREA MATRIX. To build the four-area matrix and corresponding strip, it suffices to sum the relevant items from some higher-scale matrix (NOWC-framed strip, NOL-framed matrix, expanded matrix, full-scale matrix).

CODE INSTRUCTIONS: FROM NOWC-FRAMED MATRIX TO FOUR-AREA MATRIX

The analyst lays out in the “SplitScreenStrip” sheet a range of cells (rows 66:71) with the structure of the NOWC-framed matrix (rows 58:64), but with only four rows, one for each area: operations, liquidity, debt, and equity. The analyst proceeds as in the previous matrices by filling the cells of column G and then copy and paste for the whole strip. The operating capital is obtained as

$$\underset{+C_{-1}^o}{\underbrace{\mathrm{G}68}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{{\mathrm{N}\mathrm{O}\mathrm{W}\mathrm{C}}_{-1}+{\mathrm{N}\mathrm{F}\mathrm{A}}_{-1}}{\underbrace{\mathrm{G}60:\mathrm{G}61}})$$

For the three remaining cells of column G, it suffices to provide the proper links (see Figure 30).

THE GERM MATRIX. The germ matrix is easily built, as usual, by consolidating the rows from some higher-scale matrix.

CODE INSTRUCTIONS: FROM FOUR-AREA MATRIX TO GERM MATRIX

The analyst sets up a range of cells (rows 73:76) in the “SplitScreenStrip” sheet with the same structure of the four-area matrix (rows 66:71), but with only two rows, one for each area: investment and financing. The analyst proceeds as in the previous matrices and fill the cells of column G by aggregation. Specifically, the two cells of column G of the germ matrix are calculated as follows:

$$\underset{C_{-1}^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{G}75}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{C_{-1}^o+C_{-1}^l}{\underbrace{\mathrm{G}68:\mathrm{G}69}})\hspace{1em}\hspace{1em}\hspace{1em}\underset{C_{-1}^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{G}76}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{C_{-1}^d+C_{-1}^e}{\underbrace{\mathrm{G}70:\mathrm{G}71}})$$

Then, one copies and paste to fill the whole strip (see Figure 31).

4. Module 4: Building the Benchmark Split-Screen Strip

Project appraisal requires the comparison of the project with a benchmark which collects the values, the profits, and the cash flows of the benchmark portfolio. This module is precisely devoted to building the benchmark film (or strip) from the market input data (see Part I, Equation (88)). The mathematical relations are equivalent to the ones used for the project four-area matrix and strip, but, in this case, we need to flesh out the benchmark values from the prospective cash flows (which are just equal to the project’s cash flows), so we use backward induction (see Part I).

4.1. Benchmark Four-Area Matrix

To design the benchmark Matrix, the analyst requires the MARRs as well as the cash flows. Specifically, the economic value of each area may be calculated with the backward formula

$$V_{t-1}^j=\frac{V_t^j+F_t^{Vj}}{1+r_t^j},\hspace{1em}\hspace{1em}j=o,l,d,\hspace{1em}\hspace{1em}t=1,\dots ,n$$

with $V_n^o=V_n^l=V_n^d=0$ and where $F_0^{Vj}=-V_0^j$ and $F_0^{Vj}=F_t^j$ for $t\ge 1$ (see Part I, Equation (77)). The economic value of the equity can be calculated via the law of conservation: $V_t^e=V_t^o+V_t^l-V_t^d$. The cash flows in the benchmark Matrix are the same as in the project matrix, except for period 0. The column of benchmark profits is calculated as

$$I_t^{Vj}=r^j·V_{t-1}^j,\hspace{1em}\hspace{1em}j=o,l,d$$

for the of operating assets, the non-operating assets, and the debt (see Part I, Equation (80)). The benchmark profit for the equity can be calculated with the law of conservation as a residual amount: $I_t^{Ve}=I_t^{Vo}+I_t^{Vl}-I_t^{Vd}$. The operating cash flows can be obtained as sum of the cash-flow components of the operating area in some higher-scale matrix and the other cash flows are merely linked from some other higher-scale matrix.

CODE INSTRUCTIONS: BENCHMARK MATRIX SCHEME

The analyst prepares a table in a range of cells (rows 81:86) in the “SplitScreenStrip” sheet having the structure of the project four-area matrix (rows 66:71). The rates $r^o$, $r^l$, and $r^d$ are recorded in the “Assumption” sheet. The analyst can position them in a space above the full-scale matrix (cells F3:F5) in the “SplitScreenStrip” sheet and use them in the relevant formulas. Cells F3:F5 are linked to the cells that contain the value of $r^o$, $r^l$, and $r^d$ with the formulas $=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}27$, $=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}28$, and $=\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{F}\mathrm{\$}29$.

CODE INSTRUCTIONS: ECONOMIC VALUES IN THE BENCHMARK MATRIX

For the operating class, the analyst starts by inserting the value 0 for $V_5^o,V_5^l,V_5^d$, and $V_5^e$; then, the analyst calculates the economic value of AR as of period $t=n-1=4$ with the backward formula:

$$\underset{V_4^o}{\underbrace{\mathrm{V}83}}=(\underset{V_5^o}{\underbrace{\mathrm{Y}83}}-\underset{-F_5^{Vo}\left(=-F_5^o\right)}{\underbrace{\mathrm{X}83}})/(1+\underset{r^o}{\underbrace{\mathrm{\$}\mathrm{F}\mathrm{\$}3}})$$

(see Figure 32). For the liquid assets and the debt, the analogous backward formula applies:

$$\begin{gathered} \underset{V_4^l}{\underbrace{\mathrm{V}84}}=(\underset{V_5^l}{\underbrace{\mathrm{Y}84}}-\underset{-F_5^{Vl}\left(=-F_5^l\right)}{\underbrace{\mathrm{X}84}})/(1+\underset{r^l}{\underbrace{\mathrm{\$}\mathrm{F}\mathrm{\$}4}}) \\ \underset{V_4^d}{\underbrace{\mathrm{V}85}}=(\underset{V_5^d}{\underbrace{\mathrm{Y}85}}-\underset{-F_5^{Vd}\left(=-F_5^d\right)}{\underbrace{\mathrm{X}85}})/(1+\underset{r^d}{\underbrace{\mathrm{\$}\mathrm{F}\mathrm{\$}5}}) \end{gathered}$$

The equity value is calculated with the law of conservation:

$$\underset{V_4^e}{\underbrace{\mathrm{V}86}}=\underset{V_4^o}{\underbrace{\mathrm{V}83}}+\underset{V_4^l}{\underbrace{\mathrm{V}84}}-\underset{V_4^d}{\underbrace{\mathrm{V}85}}$$

while the equations are correct, the values obtained in the cell range V83:V86 are not yet the correct ones, because cells X83:X86 have not yet been occupied by formulas; however, they will be filled with the amounts of cash flows in the next steps, and the values taken on by cells V83:V86 will be automatically updated.

CODE INSTRUCTIONS: INCOMES IN THE BENCHMARK MATRIX.

The analyst may start from the operating area and, in particular, from cell W83:

$$\underset{I_4^{Vo}}{\underbrace{\mathrm{W}83}}=\underset{V_4^o}{\underbrace{\mathrm{V}83}} \ast \underset{r^o}{\underbrace{\mathrm{\$}\mathrm{F}\mathrm{\$}3}}$$

(see Figure 33).

For the non-operating area and the debt area, respectively, one uses

$$\underset{I_4^{Vl}}{\underbrace{\mathrm{W}84}}=\underset{V_4^l}{\underbrace{\mathrm{V}84}} \ast \underset{r^l}{\underbrace{\mathrm{\$}\mathrm{F}\mathrm{\$}4}}\hspace{1em}\hspace{1em}\mathrm{and}\hspace{1em}\hspace{1em}\underset{I_4^{Vd}}{\underbrace{\mathrm{W}85}}=\underset{V_4^d}{\underbrace{\mathrm{V}85}} \ast \underset{r^d}{\underbrace{\mathrm{\$}\mathrm{F}\mathrm{\$}5}}$$

The cash flow of the equity area can be calculated via the law of conservation:

$$\underset{I_4^{Ve}}{\underbrace{\mathrm{W}86}}=\underset{I_4^{Vo}}{\underbrace{\mathrm{W}83}}+\underset{I_4^{Vl}}{\underbrace{\mathrm{W}84}}-\underset{I_4^{Vd}}{\underbrace{\mathrm{W}85}}$$

while the equations are correct, the values obtained in the cell range W84:W86 are not yet the correct ones, because cells X83:X86 have not yet been occupied by formulas; however, they will be filled with the amounts of cash flows in the next steps, and the values taken on by cells W84:W86 will be automatically updated.

CODE INSTRUCTIONS: CASH FLOWS IN THE BENCHMARK MATRIX

The (changed-in-sign) cash flow may be grabbed by the split-screen strip for $t>0$, whereas it is equal to the economic value for $t=0$. Therefore, the analyst may use the following formula for cell X83:

$$\underset{-F_4^{Vo}}{\underbrace{\mathrm{X}83}}=\mathrm{I}\mathrm{F}(\underset{\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{o}\mathrm{d}}{\underbrace{\mathrm{X}\mathrm{\$}9}}=0,\hspace{1em}\underset{V_5^o}{\underbrace{\mathrm{Y}83}},\hspace{1em}\underset{-F_4^o}{\underbrace{\mathrm{X}68}})$$

(in such a way, the analyst may later copy and paste the formula to complete the whole strip).

The analyst then copies the formula in X83 and paste it in cell X84 (the non-operating area) and in cell X85 (the debt area):

$$\begin{gathered} \underset{-F_4^{Vl}}{\underbrace{\mathrm{X}84}}=\mathrm{I}\mathrm{F}(\underset{\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{o}\mathrm{d}}{\underbrace{\mathrm{X}\mathrm{\$}9}}=0,\hspace{1em}\underset{V_5^l}{\underbrace{\mathrm{Y}84}};\hspace{1em}\underset{-F_4^l}{\underbrace{\mathrm{X}69}}) \\ \mathrm{and} \\ \underset{-F_4^{Vd}}{\underbrace{\mathrm{X}85}}=\mathrm{I}\mathrm{F}(\underset{\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{i}\mathrm{o}\mathrm{d}}{\underbrace{\mathrm{X}\mathrm{\$}9}}=0,\hspace{1em}\underset{V_5^d}{\underbrace{\mathrm{Y}85}},\hspace{1em}\underset{-F_4^d}{\underbrace{\mathrm{X}70}}) \end{gathered}$$

The (changed-in-sign) CFE can be calculated with the same formula or via the law of conservation:

$$\underset{-F_4^{Ve}}{\underbrace{\mathrm{X}86}}=\underset{-F_4^{Vo}}{\underbrace{\mathrm{X}83}}+\underset{-F_4^{Vl}}{\underbrace{\mathrm{X}84}}-\underset{-F_4^{Vd}}{\underbrace{\mathrm{X}85}}$$

(see Figure 34).

The analyst can now copy the cell range $\mathrm{V}83:\mathrm{X}86$ and paste it in the cell range $\mathrm{G}83:\mathrm{U}86$ (or, more simply, use the fill handle backwards).

4.2. Benchmark Germ Matrix

Just as with the project matrix, it is possible to construct the benchmark germ matrix.

CODE INSTRUCTIONS: FROM BENCHMARK FOUR-AREA MATRIX TO BENCHMARK GERM MATRIX

The analyst lays out a range of cells (rows 88:91) in the “SplitScreenStrip” sheet with the structure of the project germ matrix (rows 73:76). The formulas for producing the benchmark germ matrix are trivial:

$$\underset{V_{-1}^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{G}90}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{V_{-1}^o+V_{-1}^l}{\underbrace{\mathrm{G}83:\mathrm{G}84}})\hspace{1em}\hspace{1em}\underset{V_{-1}^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{G}91}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{V_{-1}^d+V_{-1}^e}{\underbrace{\mathrm{G}85:\mathrm{G}86}})$$

whence the whole strip is obtained by copying and pasting (or, more simply, use the fill handle).

4.3. Market Value Added Matrix

The analyst can build the Market Value Added (MVA) matrix and the related strip by computing the differences between the profits of the project and the benchmark profits (see Part I, Equations (91) and (92)).

CODE INSTRUCTIONS: MVA FOUR-AREA MATRIX

The analyst prepares a cell range (rows 96:101) in the “SplitScreenStrip” sheet with the structure of the four-area matrices and fills each cell of it with the differences between the corresponding cells of the benchmark four-area matrix (rows 83:86) and the project four-area matrix (rows 68:71). For example, starting from the first cell of the table,

$$\begin{gathered} \mathrm{G}98=\underset{V_{-1}^o}{\underbrace{\mathrm{G}83}}-\underset{C_{-1}^o}{\underbrace{\mathrm{G}68}}, \mathrm{G}99=\underset{V_{-1}^l}{\underbrace{\mathrm{G}84}}-\underset{C_{-1}^l}{\underbrace{\mathrm{G}69}} \\ \mathrm{G}100=\underset{V_{-1}^d}{\underbrace{\mathrm{G}85}}-\underset{C_{-1}^d}{\underbrace{\mathrm{G}70}}, \mathrm{G}101=\underset{V_{-1}^e}{\underbrace{\mathrm{G}86}}-\underset{C_{-1}^e}{\underbrace{\mathrm{G}71}} \end{gathered}$$

Then, it suffices to copy G98:G101 and paste for the whole strip H98:Y101 (or, more simply, use the fill handle).

As the reader can see, the range I98:I101 shows the NPV for the various areas: ${\mathrm{N}\mathrm{P}\mathrm{V}}^o=\mathrm{U}\mathrm{S}\mathrm{D}\mathrm{5,622}$, ${\mathrm{N}\mathrm{P}\mathrm{V}}^l=\mathrm{U}\mathrm{S}\mathrm{D}\mathrm{1,025}$, ${\mathrm{N}\mathrm{P}\mathrm{V}}^d=-\mathrm{U}\mathrm{S}\mathrm{D}236$, and ${\mathrm{N}\mathrm{P}\mathrm{V}}^e=\mathrm{U}\mathrm{S}\mathrm{D}\mathrm{6,882}$.

All the cells reporting the difference $I_t^{Vj}-I_t^j$ (H98:H101, K98:K101, N98:N101, Q98:Q101, T98:T101, W98:W101) give voice to the economic residual income (ERI) changed in sign (see next section for details).

CODE INSTRUCTIONS: MVA GERM MATRIX

The analyst prepares a range of cells (rows 103:106) in the “SplitScreenStrip” sheet with the structure of the germ matrices. To obtain the two values, one merely turns to the MVA four-area matrix and sums:

$$\begin{gathered} \underset{{\mathrm{M}\mathrm{V}\mathrm{A}}_{-1}^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{G}105}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{{\mathrm{M}\mathrm{V}\mathrm{A}}_{-1}^o+{\mathrm{M}\mathrm{V}\mathrm{A}}_{-1}^l}{\underbrace{\mathrm{G}98:\mathrm{G}99}}) \\ \underset{{\mathrm{M}\mathrm{V}\mathrm{A}}_{-1}^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{G}106}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{{\mathrm{M}\mathrm{V}\mathrm{A}}_{-1}^d+{\mathrm{M}\mathrm{V}\mathrm{A}}_{-1}^e}{\underbrace{\mathrm{G}100:\mathrm{G}101}}) \end{gathered}$$

Then, it suffices to copy G105:G106 and paste for the whole strip H105:Y106 (or, more simply, use the fill handle) (see Figure 35).

5. Module 5: Performing the Single-Period Analysis

The whole project is described in detail in the split-screen film. However, a convenient representation of it is possible which favors the detailed analysis of a single period. One creates a worksheet named “SinglePeriod” where the matrix will be located. In it, one can use the preferred framing of the SSM. For example, we propose a framing where the BS at a given date is illustrated and one where the CFS is illustrated. The former facilitates the comprehension of how the project’s book value is apportioned across the various areas (customers, suppliers, employees, etc.) and how it is affected diachronically by the income components and the cash-flow components; the latter facilitates the comprehension of how the project’s cash flow is apportioned across the various areas and how it is diachronically determined by income and change of book values. These configurations turn the project’s split-screen strip into a sequence of snapshots describing the accounting and financial magnitudes and their relationships for one period.

CODE INSTRUCTIONS: BUILDING THE SET OF MATRICES FOR SINGLE-PERIOD ANALYSIS

The analyst builds the structure of two split-screen matrices, in the configuration of full-scale matrix and in the form of BS and CFS (see Figure 36). Cell E2 indicates the period. The analyst can apply the Data Validation to restrict the values to the periods of the project, allowing only the values that are in the list “0, 1, 2, 3, 4, 5” or creating a drop-down list with these values. Some spreadsheets software allow to use a range as source list, even if it is in another worksheet: in this case, it is possible to use the range “=PreCalc!$K$2:$P$2”. Three columns on the left (columns D, E, and F, same as in the sheets “PreCalc” and “SplitScreenStrip”) contain the labels of the rows (“+AR”, “+Inv”, etc.). Below the structure of the matrix, a row (row 4) contains the descriptor of the column content. This content is C for Capital, I for Income, and F for Cash Flow. It is variable and depends on the value of cell E2: “C-1”, “+I0”, “–F0”, and “C0” if number 0 is selected for cell E2; “C0”, “+I1”, “–F1”, and “C1” if number 1 is selected for cell E2, and so on.

$$\begin{gathered} \mathrm{I}4=“\mathrm{C}”\&(\mathrm{\$}\mathrm{E}\mathrm{\$}2-1) \\ \mathrm{J}4=“+\mathrm{I}”\&\mathrm{\$}\mathrm{E}\mathrm{\$}2 \\ \mathrm{K}4=“–\mathrm{F}”\&\mathrm{\$}\mathrm{E}\mathrm{\$}2 \\ \mathrm{L}4=“\mathrm{C}”\&\mathrm{\$}\mathrm{E}\mathrm{\$}2 \end{gathered}$$

(see Figure 36).

To fill the cells of the matrix, we again employ the INDEX and MATCH combination. The technique is like the one described in Section 2.2, used in the split-screen strip for picking the amounts from the “PreCalc” worksheet.

The more external formula is:

$$\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}\left(\mathrm{F}\mathrm{u}\mathrm{l}\mathrm{l}\mathrm{S}\mathrm{c}\mathrm{a}\mathrm{l}\mathrm{e}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p},\mathrm{r}\mathrm{o}\mathrm{w}\_\mathrm{n}\mathrm{u}\mathrm{m},\mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\_\mathrm{n}\mathrm{u}\mathrm{m}\right)$$

where

“FullScaleStrip” is the range of cells where the full-scale strip is located (i.e. the range SplitScreenStrip!$13:$23);

“row_num” is the row-number of the range “FullScaleStrip” where the item that contains the sought value is located: it can be selected by matching the label of the row in the Matrix and the label in the split-screen strip, using the function MATCH;

“column_num” is the column-number of the range “FullScaleStrip” where the category (capital, income, or cash flow) and the period that contains the sought value are indicated: it can be selected by matching the label of the column in the matrix and the labels in the split-screen strip, using the function MATCH.

One starts from cell I7:

$$\begin{array}{cc}\hfill \mathrm{I}7=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \underset{\begin{array}{c}\mathrm{the} \mathrm{result} \mathrm{of} \mathrm{this} \mathrm{formula} \mathrm{is} \mathrm{the} \mathrm{nnmber} \mathrm{of} \mathrm{the} \mathrm{row} \mathrm{in} \mathrm{the}\\ \mathrm{four}\text{-}\mathrm{area} \mathrm{strip} \mathrm{that} \mathrm{has} \mathrm{the} \mathrm{same} \mathrm{label} \mathrm{as} \mathrm{cell} \$\mathrm{D}7 \left(“+\mathrm{AR}”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{D}7,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{D}\mathrm{\$}13:\mathrm{\$}\mathrm{D}\mathrm{\$}23,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{the} \mathrm{result} \mathrm{of} \mathrm{this} \mathrm{formula} \mathrm{is} \mathrm{the} \mathrm{number} \mathrm{of} \mathrm{the} \mathrm{column} \mathrm{in}\\ \mathrm{the} \mathrm{four}\text{-}\mathrm{area} \mathrm{strip} \mathrm{that} \mathrm{has} \mathrm{the} \mathrm{same} \mathrm{label} \mathrm{as} \mathrm{cell} \mathrm{I}\$4 \left(“\mathrm{C}0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{I}\mathrm{\$}4,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)}}\hfill \\ & )\hfill \end{array}$$

(see Figure 37 and Figure 38).

The formula written for cell I7 can then be used for cells J7 and K7 as well:

$$\begin{array}{cc}\hfill \mathrm{J}7=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{D}7,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{D}\mathrm{\$}13:\mathrm{\$}\mathrm{D}\mathrm{\$}23,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{J}\mathrm{\$}4,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)\hfill \\ & )\hfill \end{array}$$

The analyst can then copy cell I7 and paste it in cells J7 and K7. Cell L7 contains the formula that applies the law of motion:

$$\begin{gathered} C_t=C_{t-1}+I_t-F_t \\ \underset{{\mathrm{A}\mathrm{R}}_t}{\underbrace{\mathrm{L}7}}=\underset{{\mathrm{A}\mathrm{R}}_{t-1}}{\underbrace{\mathrm{I}7}}+\underset{{\mathrm{S}}_t}{\underbrace{\mathrm{J}7}}+\underset{-F_t^{ar}}{\underbrace{\mathrm{K}7}} \end{gathered}$$

The analyst can copy range I7:L7 and paste it in range I8:L17 (or drag the fill handle).

The realization of the matrix where CFS statement is illustrated is analogous to the one described above. The labels of the rows are in column F (column of cash-flow labels) and the labels of the columns are in row 19. The figures of the matrix are grabbed from the full-scale strip with the formulas:

$$\begin{array}{cc}\hfill \mathrm{J}22=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{F}22,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{F}\mathrm{\$}13:\mathrm{\$}\mathrm{F}\mathrm{\$}23,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{J}\mathrm{\$}19,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)\hfill \\ & )\hfill \end{array}$$

$$\begin{array}{cc}\hfill \mathrm{K}22=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{F}22,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{F}\mathrm{\$}13:\mathrm{\$}\mathrm{F}\mathrm{\$}23,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{K}\mathrm{\$}19,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)\hfill \\ & )\hfill \end{array}$$

$$\begin{array}{cc}\hfill \mathrm{L}22=\left(-1\right) \ast \mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{F}22,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{F}\mathrm{\$}13:\mathrm{\$}\mathrm{F}\mathrm{\$}23,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{K}\mathrm{\$}19,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)\hfill \\ & )\hfill \end{array}$$

Cell I22 contains the formula that applies the law of motion:

$$\begin{gathered} F_t=I_t+C_{t-1}-C_t \\ \underset{F_t^{ar}}{\underbrace{\mathrm{L}22}}=\underset{{\mathrm{S}}_t}{\underbrace{\mathrm{J}22}}+\underset{{\mathrm{A}\mathrm{R}}_{t-1}}{\underbrace{\mathrm{K}22}}+\underset{{-\mathrm{A}\mathrm{R}}_t}{\underbrace{\mathrm{L}22}} \end{gathered}$$

The analyst can copy range I22:L22 and paste it in range I23:L32 (or drag the fill handle).

The analyst can build in the same way any preferred framing of the SSM, with higher or lower scale, depending on the objective of the analysis.

6. Module 6: Reshaping the Project Split-Screen Strip

The model’s main purpose is to communicate the result of the numeric analysis: the recipients of the reports can include readers that are not familiar with the compact structure of the split-screen strip. The Transposed Matrix (Part I, Section 10.2) and a set of standard financial statements may be easily extracted from the split-screen strip.

6.1. Reshaping the Strip as a Transposed Matrix

The Transposed Matrix, presented in Part I, Section 10.2, is a compact form of the project strip which provides an intuitive picture of the whole project in terms of three submatrices, respectively reporting the book values ($C_t$), the incomes ($I_t$), and the cash flows ($F_t$), each one divided into the main four areas (operating, non-operating, debt, and equity) (see also Figure 39):

CODE INSTRUCTIONS: STRUCTURE OF TRANSPOSED MATRIX

The analyst adds, in his workbook, a new worksheet—“TransMatrix”—and creates three ranges of cells representing the three submatrices presented in Part I, Equation (70) (see also Figure 40 below).

Each row of the Transposed Matrix contains the label of the corresponding row in the four-area matrix (SplitScreenStrip!D68:F71): columns D, E, and F report the labels that pertains, respectively, to capital, income and cash flow.

CODE INSTRUCTIONS: FILLING THE CAPITAL SUBMATRIX OF TRANSPOSED MATRIX

We employ again the INDEX and MATCH combination. The technique is like the one described in Section 2.2, used in the split-screen strip for picking the amounts from the “PreCalc” worksheet.

The more external formula is:

$$\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}\left(\mathrm{F}\mathrm{o}\mathrm{u}\mathrm{r}\mathrm{A}\mathrm{r}\mathrm{e}\mathrm{a}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p},\mathrm{r}\mathrm{o}\mathrm{w}\_\mathrm{n}\mathrm{u}\mathrm{m},\mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\_\mathrm{n}\mathrm{u}\mathrm{m}\right)$$

where

“FourAreaStrip” is the range of cells where the four-area Matrix is located (i.e. the range SplitScreenStrip!$G$68:$Y$71);

“row_num” is the row-number of the range “FourAreaStrip” where the item that contains the sought value is located: it can be selected by matching the label of the row in the Transposed Matrix and the label in the split-screen strip, using the function MATCH;

“column_num” is the column-number of the range “FourAreaStrip” where the category (capital, income, or cash flow) and the period that contains the sought value are indicated: it can be selected by matching the label of the column in the Transposed Matrix and the labels in the split-screen strip, using the function MATCH. One starts from cell I6:

$$\begin{array}{cc}\hfill \mathrm{I}6=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}68:\mathrm{\$}\mathrm{Y}\mathrm{\$}71,\hfill \\ & \underset{\begin{array}{c}\mathrm{the} \mathrm{result} \mathrm{of} \mathrm{this} \mathrm{formula} \mathrm{is} \mathrm{the} \mathrm{number} \mathrm{of} \mathrm{the} \mathrm{row} \mathrm{in} \mathrm{the}\\ \mathrm{four}\text{-}\mathrm{area} \mathrm{strip} \mathrm{that} \mathrm{has} \mathrm{the} \mathrm{same} \mathrm{label} \mathrm{as} \mathrm{cell} \$\mathrm{D}6 \left(“+C^o”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{D}6,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{D}\mathrm{\$}68:\mathrm{\$}\mathrm{D}\mathrm{\$}71,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{the} \mathrm{result} \mathrm{of} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\\ \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{I}\$4 \left(“C_0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{I}\mathrm{\$}4,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}67:\mathrm{\$}\mathrm{Y}\mathrm{\$}67,0\right)}}\hfill \\ & )\hfill \end{array}$$

The formula written for cell I6 can then be used for cells I7, I10, and I11 as well.

Rows 8 and 12 contain the sums of the previous rows:

$$\underset{C_0^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{I}8}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{C_0^o+C_0^l}{\underbrace{\mathrm{I}6:\mathrm{I}7}})\hspace{1em}\hspace{1em}\hspace{1em}\hspace{1em}\underset{C_0^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{I}12}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{C_0^d+C_0^e}{\underbrace{\mathrm{I}10:\mathrm{I}11}})$$

Hence, the analyst can copy the range of cells $\mathrm{I}6:\mathrm{I}12$ and paste it in the range $\mathrm{J}6:\mathrm{N}12$ (or, more simply, use the fill handle), see Figure 41 and Figure 42.

CODE INSTRUCTIONS: FILLING THE INCOME SUBMATRIX OF TRANSPOSED MATRIX

The technique useful for filling the cells of the income submatrix is the same employed for the capital submatrix. The only difference in the formula is in the column where the row-labels are reported: the column of incomes is E. Cell I16 is calculated as follows:

$$\begin{array}{cc}\hfill \mathrm{I}16=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}68:\mathrm{\$}\mathrm{Y}\mathrm{\$}71,\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{o}\mathrm{w} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}-\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{\$}\mathrm{E}11\left(“+I^o”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{E}16,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{E}\mathrm{\$}68:\mathrm{\$}\mathrm{E}\mathrm{\$}71,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\\ \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}-\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l}\\ \left(\mathrm{p}\mathrm{r}\mathrm{e}\mathrm{c}\mathrm{e}\mathrm{d}\mathrm{e}\mathrm{d} \mathrm{b}\mathrm{y}“+”\right) \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{I}\mathrm{\$}14 \left(“{+I}_0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("+"\&\mathrm{I}\mathrm{\$}14,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}67:\mathrm{\$}\mathrm{Y}\mathrm{\$}67,0\right)}}\hfill \\ & )\hfill \end{array}$$

(see Figure 43).

The operator “&” joins the text strings, so the formula “+”$\&\mathrm{I}\mathrm{\$}16$ returns the symbol “+” alongside the content of cell I16, that is, it returns the value $+I_0$. This operation is necessary, because the labels in the “TransMatrix” sheet are “$I_0$”, “$I_1$”, “$I_2$”, “$I_3$”, “$I_4$”, and “$I_5$”, but the labels in the “SplitScreenStrip” sheet are “$+I_0$”, “$+I_1$”, “${+I}_2$”, “${+I}_3$”, “$+I_4$”, and “$+I_5$”.9

The formula written for cell I16 can be used for I17, I20, and I21 as well. Rows 18 and 22 contain the sums of the previous rows:

$$\underset{I_0^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{I}18}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{I_0^o+I_0^l}{\underbrace{\mathrm{I}16:\mathrm{I}17}})\hspace{1em}\hspace{1em}\hspace{1em}\hspace{1em}\underset{I_0^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{I}22}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{I_0^d+I_0^e}{\underbrace{\mathrm{I}20:\mathrm{I}21}})$$

The analyst can then copy the cell range of cells $\mathrm{I}16:\mathrm{I}22$ and paste it in the range $\mathrm{J}16:\mathrm{N}22$ (or, more simply, use the fill handle. See Figure 43).

CODE INSTRUCTIONS: FILLING THE CASH-FLOW SUBMATRIX OF TRANSPOSED MATRIX

The technique useful for filling the cells of the cash-flow submatrix is the same employed for the capital submatrix and the income submatrix. The column where the row-labels are is column F. In the “TransMatrix” sheet, the rows of cash flows have positive sign; whereas, in the four-area matrix in the “SplitScreenStrip” sheet, they have negative sign. For this reason, when we pick the value from the four-area Matrix, we must change it in sign (i.e., one multiplies by $-1$). One starts from the CFO, in cell I26:

$$\begin{array}{cc}\hfill \mathrm{I}26=\left(-1\right) \ast \mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}68:\mathrm{\$}\mathrm{Y}\mathrm{\$}71,\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{o}\mathrm{w} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{\$}\mathrm{F}26 \left(“-F^{\mathrm{o}}”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{F}26,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{F}\mathrm{\$}68:\mathrm{\$}\mathrm{F}\mathrm{\$}71,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\\ \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l}\\ (\mathrm{p}\mathrm{r}\mathrm{e}\mathrm{c}\mathrm{e}\mathrm{d}\mathrm{e}\mathrm{d} \mathrm{b}\mathrm{y} “-”) \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{I}\mathrm{\$}24 \left(“{-F}_0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("-"\&\mathrm{I}\mathrm{\$}24,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}67:\mathrm{\$}\mathrm{Y}\mathrm{\$}67,0\right)}}\hfill \\ & )\hfill \end{array}$$

(see Figure 44).

The operator “&” joins the text strings, so the formula “$-”\&\mathrm{I}\mathrm{\$}24$ returns the symbol $“-”$ alongside the content of the cell I24, i.e., it returns the value $-F_0$: this operation is necessary, because the labels in the “TransMatrix” sheet are “$F_0$”, “$F_1$”, “$F_2$”, “$F_3$”, “$F_4$”, “$F_5$”, but in the “SplitScreenStrip” sheet the labels are “$-F_0$”, “$-F_1$”, “$-F_2$”, “$-F_3$”, “$-F_4$”, “$-F_5$”.

The formula written for the cell I26 can be used for I27, I30, and I31 as well.

Rows 28 and 32 contain the sums of the previous rows:

$$\underset{F_0^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{I}28}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{F_0^o+F_0^l}{\underbrace{\mathrm{I}26:\mathrm{I}27}})\hspace{1em}\hspace{1em}\hspace{1em}\hspace{1em}\underset{F_0^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{I}32}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{F_0^d+F_0^e}{\underbrace{\mathrm{I}30:\mathrm{I}31}})$$

The analyst can then copy the cell range $\mathrm{I}26:\mathrm{I}32$ and paste it in the range $\mathrm{J}26:\mathrm{N}32$ (or, more simply, use the fill handle).

FCFE AND CFL. The CFS in the Transposed Matrix enables the analyst to disclose an important piece of information: the Free Cash Flow to (or from) Equity (FCFE). It is obtained by consolidating the CFO and CFD row (i.e., ${\mathrm{F}\mathrm{C}\mathrm{F}\mathrm{E}}_t=F_t^o-F_t^d$). A positive FCFE indicates that the operations sustain the debt and leave some extra cash that may be distributed to shareholders or reinvested in liquid assets; a negative FCFE indicates that the operations cannot service the debt, so a financial deficit exists which must be covered either by equity or by liquid assets or by new borrowing (see Figure 45 below. See also Part I, Section 9). In both cases, it is necessary to make a decision on the CFL: if FCFE is positive, it is necessary to make a payout decision (how much will be retained in the firm and how much will be distributed to equityholders?); if FCFE is negative, it is necessary to make a financing decisions (how much will be financed by cash withdrawals, by issuance of new shares, or by new borrowing?). In other words, CFE is a decision variable from $t=0$ to $t=n-1$ (at $t=n$, the project ends and the CFE is equal to the sum of the last FCFE and the end-of-period balance of liquid assets (see Part I, Equations (61) and (62)).

CODE INSTRUCTIONS: FILLING THE CFL STRIP

The FCFE matrix is simply a different representation of the data in the cash-flow submatrix of Transposed Matrix (see Part I, right matrix in Equation (63)). It can be placed in the “TransMatrix” sheet, rows 37:40, below the Transposed Matrix:

$$\begin{gathered} \underset{{\mathrm{F}\mathrm{C}\mathrm{F}\mathrm{E}}_0}{\underbrace{\mathrm{I}38}}=\underset{F_0^o}{\underbrace{\mathrm{I}26}}-\underset{F_0^d}{\underbrace{\mathrm{I}30}} \\ \underset{-F_0^e}{\underbrace{\mathrm{I}39}}=\left(-1\right) \ast \underset{F_0^e}{\underbrace{\mathrm{I}31}} \\ \underset{-F_0^l}{\underbrace{\mathrm{I}40}}=\underset{{\mathrm{F}\mathrm{C}\mathrm{F}\mathrm{E}}_0}{\underbrace{\mathrm{I}38}}+\underset{-F_0^e}{\underbrace{\mathrm{I}39}} \end{gathered}$$

(see Figure 45).

THE BENCHMARK TRANSPOSED MATRIX. It is convenient to build the Transposed Matrix for the benchmark as well (see Part I, Equation (87)).

It will turn out to be computationally helpful when appraising the project.

CODE INSTRUCTIONS: BUILDING THE BENCHMARK TRANSPOSED MATRIX

The analyst creates, in rows 45:73 of the “TransMatrix” sheet, a structure like the one created for the Transposed Matrix in rows 4:32. Each cell of this new structure contains the same formula as the corresponding cell of transposed matrix, but different in the strip where the amounts are picked up: the source of data of the project Transposed Matrix is the project strip (rows 68:71 of the “SplitScreenStrip” sheet), whereas the source of data of the benchmark Transposed Matrix is the benchmark strip (rows 83:86 of “SplitScreenStrip” sheet). For example, the value $V_0^o$ in cell I47:

$$\begin{array}{cc}\hfill \mathrm{I}47=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}83:\mathrm{\$}\mathrm{Y}\mathrm{\$}86,\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{o}\mathrm{w}\\ \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{b}\mathrm{e}\mathrm{n}\mathrm{c}\mathrm{h}\mathrm{m}\mathrm{a}\mathrm{r}\mathrm{k} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s}\\ \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{\$}\mathrm{D}47 \left(“+V^o”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{D}47,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{D}\mathrm{\$}83:\mathrm{\$}\mathrm{D}\mathrm{\$}86,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\\ \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{b}\mathrm{e}\mathrm{n}\mathrm{c}\mathrm{h}\mathrm{m}\mathrm{a}\mathrm{r}\mathrm{k} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s}\\ \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{I}\mathrm{\$}45 \left(“V_0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{I}\mathrm{\$}45,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}82:\mathrm{\$}\mathrm{Y}\mathrm{\$}82,0\right)}}\hfill \\ & )\hfill \end{array}$$

(see Figure 46).

The formula written for the cell I47 can be used for I48, I51, and I52 as well. Rows 49 and 53 contain the sums of the previous rows:

$$\underset{V_0^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{I}49}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{V_0^o+V_0^l}{\underbrace{\mathrm{I}47:\mathrm{I}48}})\hspace{1em}\hspace{1em}\hspace{1em}\hspace{1em}\underset{V_0^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{I}53}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{V_0^d+V_0^e}{\underbrace{\mathrm{I}51:\mathrm{I}52}})$$

The value $I_0^{Vo}$ in cell I57 is obtained as

$$\begin{array}{cc}\hfill \mathrm{I}57=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}83:\mathrm{\$}\mathrm{Y}\mathrm{\$}86,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{E}57,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{D}\mathrm{\$}83:\mathrm{\$}\mathrm{D}\mathrm{\$}86,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("+"\&\mathrm{I}\mathrm{\$}55,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}82:\mathrm{\$}\mathrm{Y}\mathrm{\$}82,0\right)\hfill \\ & )\hfill \end{array}$$

The formula written for cell I57 can be used for I58, I61, and I62 as well. Rows 59 and 63 contain the sums of the previous rows:

$$\underset{I_0^{V\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{I}59}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{I_0^{Vo}+I_0^{Vl}}{\underbrace{\mathrm{I}57:\mathrm{I}58}})\hspace{1em}\hspace{1em}\hspace{1em}\hspace{1em}\underset{I_0^{V\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{I}63}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{I_0^{Vd}+I_0^{Ve}}{\underbrace{\mathrm{I}61:\mathrm{I}62}})$$

The value $F_0^{Vo}$ in the cell I67 is obtained as

$$\begin{array}{cc}\hfill \mathrm{I}67=(-1) \ast \mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}83:\mathrm{\$}\mathrm{Y}\mathrm{\$}86,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{F}67,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{D}\mathrm{\$}83:\mathrm{\$}\mathrm{D}\mathrm{\$}86,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("-"\&\mathrm{I}\mathrm{\$}65,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{G}\mathrm{\$}82:\mathrm{\$}\mathrm{Y}\mathrm{\$}82,0\right)\hfill \\ & )\hfill \end{array}$$

The formula written for cell I67 can be used for I68, I71, and I72 as well. Rows 69 and 73 contain the sums of the previous rows:

$$\underset{F_0^{V\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{I}69}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{F_0^{Vo}+F_0^{Vl}}{\underbrace{\mathrm{I}67:\mathrm{I}68}})\hspace{1em}\hspace{1em}\hspace{1em}\hspace{1em}\underset{F_0^{V\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{I}73}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{F_0^{Vd}+F_0^{Ve}}{\underbrace{\mathrm{I}71:\mathrm{I}72}})$$

The Transposed Matrix makes it easy to calculate the sums of all the rows, which will be employed for computing the measures of worth in Module 7. The analyst uses column P in the “TransMatrix” sheet for calculating the sums of the rows. For example, one can calculate the sum of book values, $C$ ($={\sum }_{t=0}^n{C}_t$) in cell P6:

$$\mathrm{P}6=\underset{{\sum }_{t=0}^n{C}_t^o}{\underbrace{\mathrm{S}\mathrm{U}\mathrm{M}(\underset{C_0^o,C_1^o,\dots ,C_n^o}{\underbrace{\mathrm{I}6:\mathrm{N}6}})}}$$

(see Figure 47).

The formula written for cell P6 can be used for P7, P8, P10, P11, and P12 as well. In the same way, the calculation of the sum of incomes and cash flows is possible in the other submatrices of the project (cells P16:P18, P20:P22, P26:P28, and P30:P32) and of the benchmark (cells P57:P59, P61:P63, P67:P69, and P71:P73).

6.2. Reshaping the Strip as a Set of Conventional Financial Statements

A different representation of the output is in the form of standard financial statements. Using report forms, the BS, IS, and CFS are laid out in report form (see also Part I, Figure 3).

CODE INSTRUCTIONS: STRUCTURE OF FINANCIAL STATEMENTS

The analyst creates, in his workbook, a new sheet — “ConventionalFinStat” — and sets up some ranges of cells to host the pro forma BSs, ISs, and CFs (Figure 48, Figure 49 and Figure 50).

Each row contains a label in column D, E, or F: they are references of the values in the split-screen strip and report the capital amounts, incomes, and cash flows.

The standard pro forma financial statements are just a different way to show the results of the project. The analyst picks up the values for period 0 from the “SplitScreenStrip” sheet and feeds them into the conventional BS, IS, and CFS. Then, it suffices to copy and paste (or drag the fill handle) to fill the strip of all the project’s BSs.

CODE INSTRUCTIONS: FILLING THE BALANCE SHEET

An effective technique for catching the figures from the split-screen strip is the use of the combinate functions INDEX and MATCH. The technique is the same described in the previous sections. For example, the AR are obtained as follows:

$$\begin{array}{cc}\hfill \mathrm{L}11=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{o}\mathrm{w} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{s}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\text{-}\mathrm{s}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{\$}\mathrm{D}11 \left(“+\mathrm{AR}”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{D}11,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{D}\mathrm{\$}13:\mathrm{\$}\mathrm{D}\mathrm{\$}23,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n}\\ \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\text{-}\mathrm{s}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{L}\$7 \left(“{\mathrm{C}}_0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{L}\mathrm{\$}7,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)}}\hfill \\ & )\hfill \end{array}$$

(see Figure 51).

Every cell of the BS that contains a value calculated in the split-screen strip can be filled using the function INDEX-MATCH: the analyst can copy cell L11 and paste it in cells L12:L19 and L22:L23, as shown in Figure 51.

Other cells are partial sums of the values in the split-screen strip. An example is cell L10, which contains the value of the net operating working capital, $\mathrm{N}\mathrm{O}\mathrm{W}\mathrm{C}$:

$$\underset{+{\mathrm{N}\mathrm{O}\mathrm{W}\mathrm{C}}_0}{\underbrace{\mathrm{L}10}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{+{\mathrm{A}\mathrm{R}}_0+{\mathrm{I}\mathrm{n}\mathrm{v}}_0-{\mathrm{A}\mathrm{P}}_0^m-{\mathrm{A}\mathrm{P}}_0^{nm}-{\mathrm{S}\mathrm{W}\mathrm{P}}_0^m-{\mathrm{S}\mathrm{W}\mathrm{P}}_0^{nm}-{\mathrm{T}\mathrm{P}}_0}{\underbrace{\mathrm{L}11:\mathrm{L}17}})$$

or cells L9, L20, and L24:

$$\begin{gathered} \underset{C_0^o}{\underbrace{\mathrm{L}9}}=\underset{+{\mathrm{N}\mathrm{O}\mathrm{W}\mathrm{C}}_0}{\underbrace{\mathrm{L}10}}+\underset{+{\mathrm{N}\mathrm{F}\mathrm{A}}_0}{\underbrace{\mathrm{L}18}} \\ \underset{C_0^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{L}20}}=\underset{C_0^o}{\underbrace{\mathrm{L}9}}+\underset{C_0^l}{\underbrace{\mathrm{L}19}} \\ \underset{C_0^{\mathrm{f}\mathrm{i}\mathrm{n}}}{\underbrace{\mathrm{L}24}}=\underset{C_0^d}{\underbrace{\mathrm{L}22}}+\underset{C_0^e}{\underbrace{\mathrm{L}23}} \end{gathered}$$

as represented in Figure 52.

Once the BS for $t=0$ has been completed, the analyst can copy the cell range $\mathrm{L}9:\mathrm{L}24$ and paste it in the cell range $\mathrm{M}9:\mathrm{Q}24$ (or, more simply, use the fill handle).

CODE INSTRUCTIONS: FILLING THE INCOME STATEMENT

The structure of the formula used for filling the BS can be used for filling the IS. An example of the formula is

$$\begin{array}{cc}\hfill \mathrm{L}30=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{o}\mathrm{w} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{s}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\text{-}\mathrm{s}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{\$}\mathrm{E}30 \left(“+\text{∆}\mathrm{Inv}”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{E}30,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{E}\mathrm{\$}13:\mathrm{\$}\mathrm{E}\mathrm{\$}23,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{s}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\text{-}\mathrm{s}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \left(\mathrm{p}\mathrm{r}\mathrm{e}\mathrm{c}\mathrm{e}\mathrm{d}\mathrm{e}\mathrm{d} \mathrm{b}\mathrm{y}+\right) \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{L}\mathrm{\$}26 \left(“I_0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("+"\&\mathrm{L}\mathrm{\$}26,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)}}\hfill \\ & )\hfill \end{array}$$

(see Figure 53).

$I^d$ is a special case, in the sense that, while ${+I}^d$ is available in the split-screen strip, the value ${-I}^d=$ $\left(-1\right)·I^d$ is actually required in the IS scheme:

$$\begin{array}{cc}\hfill \mathrm{L}41=& \left(-1\right) \ast \mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{E}41,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{E}\mathrm{\$}13:\mathrm{\$}\mathrm{E}\mathrm{\$}23,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("+"\&\mathrm{L}\mathrm{\$}26,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)\hfill \\ & )\hfill \end{array}$$

The intermediate components of the income statements are not in the split-screen strip, so the analyst must calculate them in this layout:

$$\begin{gathered} \underset{-{\mathrm{C}\mathrm{O}\mathrm{G}\mathrm{S}}_0}{\underbrace{\mathrm{L}29}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{+∆{\mathrm{I}\mathrm{n}\mathrm{v}}_0-{\mathrm{C}\mathrm{O}\mathrm{P}}_0^m-{\mathrm{L}\mathrm{C}}_0^m}{\underbrace{\mathrm{L}30:\mathrm{L}32}}) \\ \underset{\mathrm{G}\mathrm{r}\mathrm{o}\mathrm{s}\mathrm{s}{\mathrm{P}\mathrm{r}\mathrm{o}\mathrm{f}\mathrm{i}\mathrm{t}}_0}{\underbrace{\mathrm{L}33}}=\underset{+{\mathrm{S}\mathrm{a}\mathrm{l}\mathrm{e}\mathrm{s}}_0}{\underbrace{\mathrm{L}28}}+\underset{-{\mathrm{C}\mathrm{O}\mathrm{G}\mathrm{S}}_0}{\underbrace{\mathrm{L}29}} \\ \underset{-{\mathrm{S}\mathrm{G}\mathrm{A}}_0}{\underbrace{\mathrm{L}34}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{-{\mathrm{C}\mathrm{O}\mathrm{P}}_0^{nm}-{\mathrm{L}\mathrm{C}}_0^{nm}}{\underbrace{\mathrm{L}35:\mathrm{L}36}}) \\ \underset{{\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}\mathrm{D}\mathrm{A}}_0}{\underbrace{\mathrm{L}37}}=\underset{\mathrm{G}\mathrm{r}\mathrm{o}\mathrm{s}\mathrm{s}{\mathrm{P}\mathrm{r}\mathrm{o}\mathrm{f}\mathrm{i}\mathrm{t}}_0}{\underbrace{\mathrm{L}33}}+\underset{-{\mathrm{S}\mathrm{G}\mathrm{A}}_0}{\underbrace{\mathrm{L}34}} \\ \underset{{\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}}_0}{\underbrace{\mathrm{L}39}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{+{\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}\mathrm{D}\mathrm{A}}_0-{\mathrm{D}\mathrm{e}\mathrm{p}}_0}{\underbrace{\mathrm{L}37:\mathrm{L}38}}) \\ \underset{{\mathrm{E}\mathrm{B}\mathrm{T}}_0}{\underbrace{\mathrm{L}42}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{+{\mathrm{E}\mathrm{B}\mathrm{I}\mathrm{T}}_0+I_0^l-I_0^d}{\underbrace{\mathrm{L}39:\mathrm{L}41}}) \end{gathered}$$

(see also Part I, Figure 5). The net income, $I_t^e$, is available in the split-screen strip as well. Of course, the analyst can pick it up and feed it into the conventional scheme (with the combined function INDEX-MATCH) or calculate it as

$$\underset{I_0^e}{\underbrace{\mathrm{L}44}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{+{\mathrm{E}\mathrm{B}\mathrm{T}}_0-{\mathrm{T}}_0}{\underbrace{\mathrm{L}42:\mathrm{L}43}}).$$

The analyst can now copy the range $\mathrm{L}28:\mathrm{L}44$ and paste it in the cell range $\mathrm{M}28:\mathrm{Q}44$ (or, more simply, use the fill handle).

CODE INSTRUCTIONS: FILLING THE CASH FLOW STATEMENT

The structure of the formula used for filling the BS and for the IS can be used for filling the Cash Flow Statement. An example of the formula is

$$\begin{array}{cc}\hfill \mathrm{L}48=\left(-1\right) \ast \mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{o}\mathrm{w} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{s}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\text{-}\mathrm{s}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{\$}\mathrm{F}48 \left(“-F^{ar}”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{F}48,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{F}\mathrm{\$}13:\mathrm{\$}\mathrm{F}\mathrm{\$}23,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{s}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\text{-}\mathrm{s}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n} \mathrm{s}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} (\mathrm{p}\mathrm{r}\mathrm{e}\mathrm{c}\mathrm{e}\mathrm{d}\mathrm{e}\mathrm{d} \mathrm{b}\mathrm{y}-) \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{L}\mathrm{\$}46 \left(“-F_0”\right)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("–"\&\mathrm{L}\mathrm{\$}46,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)}}\hfill \\ & )\hfill \end{array}$$

(see Figure 54).

The statement of cash flows contains values that the analyst can find in the cash-flow column of the split-screen strip (e.g., $F^{ar}$, $F^{ap,m}$, $F^{ap,nm}$), but it also contains values available in the income columns ($I^l$, $I^d$). For these figures, the analyst can use the same formula employed for the IS. An example of the formula is

$$\begin{array}{cc}\hfill \mathrm{L}49=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{E}49,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{F}\mathrm{\$}13:\mathrm{\$}\mathrm{F}\mathrm{\$}23,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left("–"\&\mathrm{L}\mathrm{\$}26,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)\hfill \\ & )\hfill \end{array}$$

$∆C^d$ is a special case because it is not available in the split-screen strip. However, the value $C^d$ does appear in the strip, so the analyst can pick it up with the formula already used for the BS:

$$\begin{array}{cc}\hfill \mathrm{L}61=\mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}13:\mathrm{\$}23,\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{D}61,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{F}\mathrm{\$}13:\mathrm{\$}\mathrm{F}\mathrm{\$}23,0\right),\hfill \\ & \mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{L}\mathrm{\$}7,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}12:\mathrm{\$}12,0\right)\hfill \\ & )\hfill \end{array}$$

The variation $∆C_t^d$ is $∆C_t^d=C_t^d-C_{t-1}^d$; in spreadsheet formula, this means, for period 0,

$$\underset{∆C_0^d}{\underbrace{\mathrm{L}62}}=\underset{C_0^d}{\underbrace{\mathrm{L}61}}-\underset{C_{-1}^d}{\underbrace{\mathrm{K}61}}$$

(see Figure 55).

The three classes of cash flows of the standard CFS are obtained by summing the individual items:

$$\begin{gathered} \underset{\mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{f}\mathrm{r}\mathrm{o}\mathrm{m} \mathrm{o}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{a}\mathrm{c}\mathrm{t}\mathrm{i}\mathrm{v}\mathrm{i}\mathrm{t}\mathrm{i}\mathrm{e}\mathrm{s}}{\underbrace{\mathrm{L}56}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{\begin{array}{c}+F_0^{ar}{+I}_0^l-F_0^{ap,m}-F_0^{ap,nm}-F_0^{swp,m}\\ -F_0^{swp,nm}-I_0^d-F_0^{tp}\end{array}}{\underbrace{\mathrm{L}48:\mathrm{L}55}}) \\ \underset{\mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{f}\mathrm{r}\mathrm{o}\mathrm{m} \mathrm{i}\mathrm{n}\mathrm{v}\mathrm{e}\mathrm{s}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{a}\mathrm{c}\mathrm{t}\mathrm{i}\mathrm{v}\mathrm{i}\mathrm{t}\mathrm{i}\mathrm{e}\mathrm{s}}{\underbrace{\mathrm{L}59}}=\underset{+F_0^{nfa}}{\underbrace{\mathrm{L}58}} \\ \underset{\mathrm{C}\mathrm{a}\mathrm{s}\mathrm{h} \mathrm{f}\mathrm{l}\mathrm{o}\mathrm{w} \mathrm{f}\mathrm{r}\mathrm{o}\mathrm{m} \mathrm{f}\mathrm{i}\mathrm{n}\mathrm{a}\mathrm{n}\mathrm{c}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{a}\mathrm{c}\mathrm{t}\mathrm{i}\mathrm{v}\mathrm{i}\mathrm{t}\mathrm{i}\mathrm{e}\mathrm{s}}{\underbrace{\mathrm{L}64}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{+\Delta C_t^d-F_t^e}{\underbrace{\mathrm{L}62:\mathrm{L}63}}). \end{gathered}$$

The sum of the three components above is the change in cash and cash equivalents ($∆C^l$):

$$\mathrm{L}66=\mathrm{L}56+\mathrm{L}59+\mathrm{L}64$$

The analyst can copy the range $\mathrm{L}48:\mathrm{L}66$ and paste it in range $\mathrm{M}48:\mathrm{Q}66$ (or, more simply, use the fill handle).

7. Module 7: Assessing Value Creation and Financial Efficiency

As we have seen, the NPVs can be found in the MVA matrix. This section is addressed to calculating some measures of economic profitability that provide different pieces of information from the NPV but are NPV-consistent and, therefore, reciprocally consistent. They offer an alternative, but logically equivalent, view on the project’s value creation and financial efficiency (see Part I, Section 13). A new sheet for the presentation of the absolute and relative measures of worth must be created, called “ValueCreation”. In it, we report the NPV and the economic residual income (ERI), which are absolute measures of worth, and, in addition, the rate of return ($i$) and the cash-flow return on capital (CFROC), along with the corresponding benchmark rates of return (respectively, $\rho$ and ${\mathrm{C}\mathrm{F}\mathrm{R}\mathrm{O}\mathrm{C}}^V$), which are relative measures of worth.

7.1. Economic Residual Income (ERI)

The ERI informs about how value creation may be apportioned across the various periods, that is, how the project has contributed to increase shareholders’ wealth in the various periods. The total of such ERIs provide the overall value created (see Part I, Sections 12 and 13).

CODE INSTRUCTIONS: ERI TABLE

The analyst creates, in his workbook, a new sheet called “ValueCreation”, where he prepares a range of cells that can contain the table of the ERIs (see Figure 56). Columns A and B contain the labels of the area (o, l, d, e, inv, and fin), and a header row (row 6) which contains the numbers of the periods. Each cell of this range contains the ERI of one area, $I_t^j–I_t^{Vj},j=o,l,d,e,\mathrm{i}\mathrm{n}\mathrm{v},\mathrm{f}\mathrm{i}\mathrm{n}$. These values are already available (changed in sign) in the MVA Matrix (SplitScreenStrip!98:101), so the analyst can pick them up with the following formulas:

$$\begin{array}{cc}\hfill \mathrm{C}9=(-1)\ast \mathrm{I}\mathrm{N}\mathrm{D}\mathrm{E}\mathrm{X}(& \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{M}\mathrm{V}\mathrm{A} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x}\\ \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{c}\mathrm{o}\mathrm{n}\mathrm{t}\mathrm{a}\mathrm{i}\mathrm{n}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{w}\mathrm{a}\mathrm{n}\mathrm{t}\mathrm{e}\mathrm{d} \mathrm{v}\mathrm{a}\mathrm{l}\mathrm{u}\mathrm{e}\mathrm{s}\end{array}}{\underbrace{\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}98\text{:}\mathrm{\$}101}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{o}\mathrm{w} \mathrm{i}\mathrm{n}\\ \mathrm{M}\mathrm{V}\mathrm{A} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{\$}\mathrm{B}9(“\mathrm{o}”)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{\$}\mathrm{B}9,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}\mathrm{B}\mathrm{\$}98\text{:}\mathrm{\$}\mathrm{B}\mathrm{\$}101,0\right)}},\hfill \\ & \underset{\begin{array}{c}\mathrm{t}\mathrm{h}\mathrm{e} \mathrm{r}\mathrm{e}\mathrm{s}\mathrm{u}\mathrm{l}\mathrm{t} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{i}\mathrm{s} \mathrm{f}\mathrm{o}\mathrm{r}\mathrm{m}\mathrm{u}\mathrm{l}\mathrm{a} \mathrm{i}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{n}\mathrm{u}\mathrm{m}\mathrm{b}\mathrm{e}\mathrm{r} \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{f}\mathrm{i}\mathrm{r}\mathrm{s}\mathrm{t} \mathrm{c}\mathrm{o}\mathrm{l}\mathrm{u}\mathrm{m}\mathrm{n} \mathrm{i}\mathrm{n} \mathrm{t}\mathrm{h}\mathrm{e}\\ \mathrm{M}\mathrm{V}\mathrm{A} \mathrm{f}\mathrm{o}\mathrm{u}\mathrm{r}\text{-}\mathrm{a}\mathrm{r}\mathrm{e}\mathrm{a} \mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x} \mathrm{t}\mathrm{h}\mathrm{a}\mathrm{t} \mathrm{h}\mathrm{a}\mathrm{s} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{s}\mathrm{a}\mathrm{m}\mathrm{e} \mathrm{l}\mathrm{a}\mathrm{b}\mathrm{e}\mathrm{l} \mathrm{a}\mathrm{s} \mathrm{c}\mathrm{e}\mathrm{l}\mathrm{l} \mathrm{C}\mathrm{\$}6(“0”)\end{array}}{\underbrace{\mathrm{M}\mathrm{A}\mathrm{T}\mathrm{C}\mathrm{H}\left(\mathrm{C}\mathrm{\$}6,\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{\$}9\text{:}\mathrm{\$}9,0\right)}}\hfill \\ & )\hfill \end{array}$$

Then, the analyst copies and pastes the same formula for the whole strip C9:H12. The same table can also be framed in terms of investments vs. financings. For this purpose, it suffices to use the previous ERI strip. For example, for the ERI generated by the investments is calculated as

$$\underset{{\mathrm{E}\mathrm{R}\mathrm{I}}^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{C}14}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{{\mathrm{E}\mathrm{R}\mathrm{I}}^o+{\mathrm{E}\mathrm{R}\mathrm{I}}^l}{\underbrace{\mathrm{C}9:\mathrm{C}10}})$$

The ERI informs that the project subtracts value for the firm’s shareholders in the first two periods ($-$2367 and $-$367) but adds value in the remaining three periods (1185, 3074, and 5357). Overall, the Total ERI is positive and equal to 6882. It is calculated in column I as $\mathrm{E}\mathrm{R}\mathrm{I}=\sum _{t=0}^n(I_t-I_t^V)$. In the spreadsheet,

$$\mathrm{I}9=\mathrm{S}\mathrm{U}\mathrm{M}\left(\mathrm{C}9\text{:}\mathrm{H}9\right).$$

As shown in Part I, Equations (96) and (102), the Total ERI is equal to the NPV. Therefore, calculating the ERIs represent a way for decomposing the NPV by periods.

7.2. Rate of Return, Average ERI, and Cash-Flow Return on Capital

Beside (the NPV and the) Total ERI, which are absolute measures of worth, some useful information is provided by the relative measures of worth, which can be extracted with simple formulas (see Part I, Section 13). The formulas of the measures employ either the sum of project book values or the sum of benchmark amounts. It is convenient to pick up such figures from the “TransMatrix” sheet, where they have already been calculated.

CODE INSTRUCTIONS: SUMS OF ROWS OF THE TRANSPOSED MATRICES

The analyst builds a table in the “ValueCreation” sheet and reports all the sums calculated in the “TransMatrix” sheet (see Figure 57). For example:

$$\begin{gathered} \mathrm{E}20=\mathrm{T}\mathrm{r}\mathrm{a}\mathrm{n}\mathrm{s}\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x}!\mathrm{P}6 \\ \mathrm{G}20=\mathrm{T}\mathrm{r}\mathrm{a}\mathrm{n}\mathrm{s}\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x}!\mathrm{P}16 \\ \mathrm{I}20=\mathrm{T}\mathrm{r}\mathrm{a}\mathrm{n}\mathrm{s}\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x}!\mathrm{P}26 \\ \mathrm{K}20=\mathrm{T}\mathrm{r}\mathrm{a}\mathrm{n}\mathrm{s}\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x}!\mathrm{P}57 \\ \mathrm{M}20=\mathrm{T}\mathrm{r}\mathrm{a}\mathrm{n}\mathrm{s}\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x}!\mathrm{P}67 \end{gathered}$$

These values are used for assessing the project’s economic profitability (see the code instructions below).

CODE INSTRUCTIONS: ABSOLUTE AND RELATIVE MEASURES OF ECONOMIC PROFITABILITY

The analyst sets up a range of cells that can host the values of the (absolute and relative) measures of worth, and the benchmark rate of return (see Figure 58).

NPV (COLUMN D). As noted in Section 7, the NPV is equal to the MVA at $t=0$, which can be picked up from the MVA Matrix (rows 98:101 of the “SplitScreenStrip” sheet). For example, the NPV generated by the operations is obtained as

$$\mathrm{D}31=\mathrm{S}\mathrm{p}\mathrm{l}\mathrm{i}\mathrm{t}\mathrm{S}\mathrm{c}\mathrm{r}\mathrm{e}\mathrm{e}\mathrm{n}\mathrm{S}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{p}!\mathrm{J}98$$

(as we already know, this output is also equal to the operating Total ERI in cell I9). The analyst can then copy cell D31 and paste it in cells D32, D35, and D36. The NPV of the investment and financing areas are the sums of the previous rows. For example, the investment NPV is calculated as

$$\underset{{\mathrm{N}\mathrm{P}\mathrm{V}}^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{D}33}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{{\mathrm{N}\mathrm{P}\mathrm{V}}^o+{\mathrm{N}\mathrm{P}\mathrm{V}}^l}{\underbrace{\mathrm{D}31:\mathrm{D}32}})$$

AERI (COLUMN F). The Average Economic Residual Income (AERI) is the ratio of Total ERI and the project’s lifespan:

$$\underset{{\mathrm{A}\mathrm{E}\mathrm{R}\mathrm{I}}^o}{\underbrace{\mathrm{F}31}}=\underset{{\mathrm{t}\mathrm{o}\mathrm{t}\mathrm{a}\mathrm{l}\mathrm{E}\mathrm{R}\mathrm{I}}^o}{\underbrace{\mathrm{I}9}}/\underset{n}{\underbrace{\mathrm{A}\mathrm{s}\mathrm{s}\mathrm{u}\mathrm{m}\mathrm{p}\mathrm{t}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}!\mathrm{\$}\mathrm{H}\mathrm{\$}5}}$$

The analyst can copy cell F31 and paste it in cells F32, F35, and F36. The AERI of the investment and financing areas are the sums of the previous rows. For example, the investment AERI is:

$$\underset{{\mathrm{A}\mathrm{E}\mathrm{R}\mathrm{I}}^{\mathrm{i}\mathrm{n}\mathrm{v}}}{\underbrace{\mathrm{F}33}}=\mathrm{S}\mathrm{U}\mathrm{M}(\underset{{\mathrm{A}\mathrm{E}\mathrm{R}\mathrm{I}}^o+{\mathrm{A}\mathrm{E}\mathrm{R}\mathrm{I}}^l}{\underbrace{\mathrm{F}31:\mathrm{F}32}})$$

CFROC (COLUMN H). The Cash-flow return on capital (CFROC) is the ratio of the sum of cash-flows, $F$ ($={\sum }_{t=0}^n{F}_t$), and the sum of book values, $C$ ($={\sum }_{t=0}^n{C}_t$):

$$\mathrm{C}\mathrm{F}\mathrm{R}\mathrm{O}\mathrm{C}=\frac{{\sum }_{t=0}^n{F}_t}{{\sum }_{t=0}^n{C}_t}$$

The analyst can pick up the values of $F$ and $C$ from the table in rows 18:26:

$$\underset{{\mathrm{C}\mathrm{F}\mathrm{R}\mathrm{O}\mathrm{C}}^o}{\underbrace{\mathrm{H}31}}=\underset{{\sum }_{t=0}^n{F}_t^o}{\underbrace{\mathrm{I}20}}/\underset{{\sum }_{t=0}^n{C}_t^o}{\underbrace{\mathrm{E}20}}$$

The analyst can then copy cell H31 and paste it in ranges H32:H33 and H35:H37.

RATE OF RETURN (COLUMN J). The rate of return of the project, $i$, is the ratio of the sum of project incomes, $I$ ($={\sum }_{t=0}^n{I}_t$) and the sum of the project book values, $C$:

$$i=\frac{{\sum }_{t=0}^n{I}_t}{{\sum }_{t=0}^n{C}_t}.$$

The analyst can grab the values of $I$ and $C$ from the table in rows 18:26:

$$\underset{i^o}{\underbrace{\mathrm{J}31}}=\underset{{\sum }_{t=0}^n{I}_t^o}{\underbrace{\mathrm{G}20}}/\underset{{\sum }_{t=0}^n{C}_t^o}{\underbrace{\mathrm{E}20}}.$$

As explained in Part I, Section 13, $i$ and CFROC must coincide, so J31 = H31, as we expect. The analyst can then copy cell J31 and paste it in ranges J32:J33 and J35:J37. These rates of return refer to all the areas of the project. They are, respectively, the average ROI, average ROL, average ROA, average ROD, and average ROE, while cells J33 and J37 contain the average ROA (see Table 4 in Part I).

CFROC^V (COLUMN L). The cash-flow return on capital of the benchmark, ${\mathrm{C}\mathrm{F}\mathrm{R}\mathrm{O}\mathrm{C}}^V$, is the cutoff rate associated with CFROC which signal value creation (i.e., project acceptance) or value destruction (i.e., project rejection): it is the ratio of the sum of the cash flows generated by the benchmark$\left({\sum }_{t=0}^n{F}_t^V\right)$ and the sum of the book values, $C$.

$${\mathrm{C}\mathrm{F}\mathrm{R}\mathrm{O}\mathrm{C}}^V=\frac{{\sum }_{t=0}^n{F}_t^V}{{\sum }_{t=0}^n{C}_t}$$

The analyst can grab the values of $F^V$ and $C$ from the table in rows 18:26:

$$\underset{{\mathrm{C}\mathrm{F}\mathrm{R}\mathrm{O}\mathrm{C}}^{Vo}}{\underbrace{\mathrm{L}31}}=\underset{{\sum }_{t=0}^n{F}_t^{Vo}}{\underbrace{\mathrm{M}20}}/\underset{{\sum }_{t=0}^n{C}_t^o}{\underbrace{\mathrm{E}20}}$$

The analyst can then copy the cell L31 and paste it in ranges L32:L33 and L35:L37.

BENCHMARK RATE OF RETURN (COLUMN N). The benchmark rate of return $\rho$, associated with $i$, is the ratio of the sum of benchmark profits $I^V(=\sum _{t=1}^n{I}_t^V)$, and the sum of book values, $C:$

$$\rho =\frac{{\sum }_{t=0}^n{I}_t^V}{{\sum }_{t=0}^n{C}_t}$$

The analyst can grab the values of $I^V$ and $C$ from the table in rows 18:26:

$$\underset{{\rho }^o}{\underbrace{\mathrm{N}31}}=\underset{{\sum }_{t=0}^n{I}_t^{Vo}}{\underbrace{\mathrm{K}20}}/\underset{{\sum }_{t=0}^n{C}_t^o}{\underbrace{\mathrm{E}20}}$$

The analyst can then copy the cell N31 and paste it in ranges N32:N33 and N35:N37. As explained in Part I, Section 13, ${\mathrm{C}\mathrm{F}\mathrm{R}\mathrm{O}\mathrm{C}}^V$ must coincide with $\rho$, so $\mathrm{L}31=\mathrm{N}31$ (same for the other ones), as we expect.

As the reader can see, areas D31:D32 and D35:D36 of the “ValueCreation” sheet show the NPV for the various areas: ${\mathrm{N}\mathrm{P}\mathrm{V}}^o=\mathrm{5,622}$ (value created by the project’s operations), ${\mathrm{N}\mathrm{P}\mathrm{V}}^l=\mathrm{1,025}$ (value created by the reinvestment in liquid assets), ${\mathrm{N}\mathrm{P}\mathrm{V}}^d=-236$ (value lost by the debtholders at the expense of the firm’s shareholders), and ${\mathrm{N}\mathrm{P}\mathrm{V}}^e=\mathrm{6,882}$ (value created for the firm’s shareholders). Cells D33 and D37 report the project NPV: the value created by the project is $\mathrm{N}\mathrm{P}\mathrm{V}=\mathrm{6,647}$, part of which is generated by the liquid assets (because assumptions 21 and 24 imply that $i^l>r^l$, that is, the interest rate on liquid assets is greater than the required return on them). However, the equity NPV (USD 6,882) is greater than USD 6,647 because part of it (USD 236) is lost by the debtholders. This occurs because assumptions 19 and 25 imply $i^d<r^d$, that is, the interest rate on debt is smaller than the required return on debt (i.e., the maximum attractive financing rate for the firm).

7.3. The Product Structure

An alternative way of calculating the NPV consists in computing the product of the total invested capital ($C$) and the financial efficiency of the investment, which is equal to the difference between the rate of return and the benchmark rate of return ($i-\rho$). This represents an explicit link between the capital, the rate of return and the MARR (see Part I, Equation (103), and the code instructions below).

CODE INSTRUCTIONS: ALTERNATIVE WAY OF CALCULATING THE NPV

The analyst sets up a range of cells that can host the alternative way of calculating the NPV.

TOTAL CAPITAL. Cells D42:D48 contain the total capital of the project (${\sum }_{t=0}^n{C}_t$), grabbed from the Transposed Matrix. The analyst starts from

$$\underset{\begin{array}{c}\mathrm{t}\mathrm{o}\mathrm{t}\mathrm{a}\mathrm{l} \mathrm{i}\mathrm{n}\mathrm{v}\mathrm{e}\mathrm{s}\mathrm{t}\mathrm{m}\mathrm{e}\mathrm{n}\mathrm{t}\\ \mathrm{i}\mathrm{n} \mathrm{o}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}\end{array}}{\underbrace{\mathrm{D}42}}=\mathrm{T}\mathrm{r}\mathrm{a}\mathrm{n}\mathrm{s}\mathrm{M}\mathrm{a}\mathrm{t}\mathrm{r}\mathrm{i}\mathrm{x}!\mathrm{P}6$$

and then copies cell D42 and pastes it in ranges D43:D44 and D46:D48.

FINANCIAL EFFICIENCY. Cells F42:F48 contain the financial efficiency of the investment ($i-\rho$), obtained from the values already calculated in the table above. The analyst starts from

$$\underset{\begin{array}{c}\mathrm{f}\mathrm{i}\mathrm{n}\mathrm{a}\mathrm{n}\mathrm{c}\mathrm{i}\mathrm{a}\mathrm{l} \mathrm{e}\mathrm{f}\mathrm{f}\mathrm{i}\mathrm{c}\mathrm{i}\mathrm{e}\mathrm{n}\mathrm{c}\mathrm{y}\\ \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{o}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}s\end{array}}{\underbrace{\mathrm{F}42}}=\underset{i^o}{\underbrace{\mathrm{J}31}}-\underset{{\rho }^o}{\underbrace{\mathrm{N}31}}$$

and then copies the cell F42 and pastes it in ranges F43:F44 and F46:F48.

NPV. Cells H42:H48 contains the NPV as products of investment scale and financial efficiency. Starting from

$$\underset{{\mathrm{N}\mathrm{P}\mathrm{V}}^o}{\underbrace{\mathrm{H}42}}=\underset{\begin{array}{c}\mathrm{t}\mathrm{o}\mathrm{t}\mathrm{a}\mathrm{l} \mathrm{i}\mathrm{n}\mathrm{v}\mathrm{e}\mathrm{s}\mathrm{t}\mathrm{m}\mathrm{e}\mathrm{n}\mathrm{t}\\ \mathrm{i}\mathrm{n} \mathrm{o}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}\end{array}}{\underbrace{\mathrm{D}42}}\ast \underset{\begin{array}{c}\mathrm{f}\mathrm{i}\mathrm{n}\mathrm{a}\mathrm{n}\mathrm{c}\mathrm{i}\mathrm{a}\mathrm{l} \mathrm{e}\mathrm{f}\mathrm{f}\mathrm{i}\mathrm{c}\mathrm{i}\mathrm{e}\mathrm{n}\mathrm{c}\mathrm{y}\\ \mathrm{o}\mathrm{f} \mathrm{t}\mathrm{h}\mathrm{e} \mathrm{o}\mathrm{p}\mathrm{e}\mathrm{r}\mathrm{a}\mathrm{t}\mathrm{i}\mathrm{n}\mathrm{g} \mathrm{a}\mathrm{s}\mathrm{s}\mathrm{e}\mathrm{t}\mathrm{s}\end{array}}{\underbrace{\mathrm{F}42}}$$

the analyst can then copy the cell H42 and paste it in the ranges H43:H44 and H46:H48 (see Figure 59).

In particular, the total capital invested in the project is $C=\mathrm{U}\mathrm{S}\mathrm{D}$ 72,923 at an above-normal rate of return of $i-\rho =$ 9.1%, which makes a USD 6,647 gain. The total capital borrowed is $C^d=\mathrm{U}\mathrm{S}\mathrm{D}$ 25,000 at a rate of return which is lower than the benchmark rate of return by $|i^d-{\rho }^d|=$ 0.9%, so the debt NPV is slightly negative and equal to ${\mathrm{N}\mathrm{P}\mathrm{V}}^d=-\mathrm{U}\mathrm{S}\mathrm{D}236$. The total capital invested by shareholders is $C^e=\mathrm{U}\mathrm{S}\mathrm{D}$ 47,923, invested at an above-normal rate of return of $i^e-{\rho }^e=$ 14.4%, which makes a USD 6,882 increase in wealth (see also Part I, Equation (103)).

8. Conclusions

This paper provides practitioners with a guide to innovative financial modeling. Specifically, it implements the accounting-and-finance engineering system (AFES) described in Magni (2020, 2023) on spreadsheet software. The AFES is based on the split-screen approach, and we guide the modeler step by step, showing how to build a frugal, transparent, and easy-to-use model for performing a detailed financial analysis of a capital asset project. The AFES articulation is such that it acts as a diagnostic tool, spotting modeling errors and internal inconsistencies. In particular, if numbers in rows and columns do not match, there is some inconsistencies in the calculation. Furthermore, if the absolute measures of worth are not reciprocally consistent, then some problems arise in some parts of the model (either in the evaluation process or, earlier, in processing the accounting and financial magnitudes).

We start from the verbal description of the project, then carry out preliminary calculation and use them to obtain the split-screen strip, whereby the pro forma financial statements are chronologically chained. We divide the model into seven modules with seven associated worksheets and make the corresponding file available for downloading at the following address: https://morespace.economia.unimore.it/carloalbertomagni/spreadsheet-modeling/ (accessed 18 February 2023) (the file can also be requested at magni@unimo.it or d.baschieri@grafspa.it)