Financial Modelling and Data Analytics cheat sheet

Key terms

Workbook

One Excel file (.xlsx). It holds worksheets and may hold chart sheets. In this paper all your answers sit in one workbook, one worksheet per question entity.

Worksheet

One grid of 1,048,576 rows by 16,384 columns (A to XFD). Each answer sheet is named after the entity in the question, for example Makupa Cement or Herbert Rono.

Cell reference

The address of a cell, column letter then row number (B7). A reference in a formula can be relative (B7), absolute ($B$7) or mixed ($B7, B$7), which decides how it changes when the formula is copied.

Named range

A name such as Loan or Rate given to a cell or range through the Name Box or Name Manager, so formulas read =PMT(Rate/12,Term*12,-Loan) instead of =PMT(B4/12,B5*12,-B3).

Input cell

A cell holding a hard-coded assumption: a tax rate, a price, a discount rate. Convention colours it blue so a reviewer knows it may be changed.

Excel Table

A range converted with Ctrl+T into a named object that expands automatically, copies formulas down a whole column and allows structured references such as Bonds[Coupon].

PivotTable

An interactive summary of a list: drag fields into Rows, Columns, Values and Filters and Excel sums, counts or averages the data without a single formula. It must be refreshed when the source changes.

Custom number format

A format code entered in Format Cells (Ctrl+1) that changes how a value is displayed, not the value stored: #,##0,, shows 12,345,678 as 12.

Relative reference (B7)

Both column and row move when copied. =C14*$B$7 copied one column right becomes =D14*$B$7: the volume moves to the next year, the price stays put.

Absolute reference ($B$7)

Neither moves. Use it for a single input that every period reads: the price, the tax rate, the discount rate.

Mixed reference, row fixed (B$4)

The column moves, the row stays. Use it for a header row of rates that a grid reads across.

Mixed reference, column fixed ($A5)

The row moves, the column stays. Use it for a column of years that a grid reads down.

Bonds[Face]

The whole Face column of the Table named Bonds, excluding the header and total row. =SUM(Bonds[Face]) totals it however many rows are added.

[@Face]

The Face value in the same row as the formula. =[@Face]*[@Coupon] is the row's annual coupon.

Bonds[#Totals]

The total row, switched on with Ctrl+Shift+T or the Total Row box. Its cells use SUBTOTAL(109,...), which ignores filtered-out rows.

Bonds[#All]

The whole Table, headers and totals included, as one range.