Operational Modeling

Question 1

Define:

Model Alerts

Question 2

List:

The (3) Steps for the Revenue Schedule

Answer 2

1. Enter (Input) days in period, plant capacity, volume growth and pricing increases
2. Calculate revenue by multiplying price and volume
3. Exit (Output) - this will arrive at revenue estimates which will flow into the income statement

Question 3

Define:

Fixed Costs

Answer 3

As output increases, costs remain constant - not based on unit volume.
For example: labor, insurance, utilities, etc.

Question 4

Define:

Variable Costs

Answer 4

As output increase, costs increase - based on unit volume.
For example: chemicals, packaging, transportation, etc.

Question 5

Define:

Operational Leverage

Answer 5

The mix of fixed and variable costs in a business.
A business with higher proportion of fixed cost has more operational leverage which also means higher return and risk.

Question 6

Define:

Corkscrew

Answer 6

A schedule which tracks an account changing over time.

Question 7

List:

The (3) Steps for the Cost Schedule

Answer 7

1. Enter (Input) an estimate of sales volume and cost inflation
2. Calculate the forecasted variable and fixed costs
3. Exit (Output) - this will arrive at COGS which will flow into the income statement

Question 8

List:

The (2) Steps to Forecast Costs

Answer 8

1. Variable Costs - Start with per unit amounts then calculate total amounts.
   Total Variable Costs = Cost per Unit x Total Units
2. FIxed Costs - Start with total amounts then calculate per unit amounts.
   Per Unit’s Fixed Cost = Total Fixed Costs / Sales Volume

Question 9

Answer the following:

Why is working capital important?

Answer 9

It impacts (operating) cash flow heavily. Movements in working capital items can either consume or produce cash.

Question 10

List formula:

Accounts Receivable Total (assuming # of A/R day is available)

Answer 10

A/R Days / Days in Period x Revenues

Forecasting purposes

Question 11

List formula:

Inventory Total
(assuming # of inventory day is available)

Answer 11

Inventory Days / Days in Period x COGS

Forecasting purposes

Question 12

List formula:

Accounts Payable
(assuming # of A/P day is available)

Answer 12

A/P Days / Days in Period x COGS

Forecasting purposes

Question 13

List:

The (3) Steps for the Working Capital Schedule

Answer 13

1. Enter (Input) days in period, revenue and COGS
2. Calculate the historical metrics in number of days then convert forecasts from number of days to total dollar amounts
3. Exit (Output) - this is the cash from working capital items required for cash flow analysis or/and can be used as future account balances required for the balance sheet

Question 14

List:

The (3) Steps for the Depreciation Schedule

Answer 14

1. Enter (Input) opening PP&E balance, CAPEX forecast and first year allocation
2. Calculate depreciation for both existing and new assets within the schedule
3. Exit (Output) - this will produce the company’s total depreciation expense, which will flow to the company’s income statement

Question 15

List:

The (3) Steps for the Asset Schedule

Answer 15

1. Enter (Input) opening PP&E balance, CAPEX addition and accounting depreciation
2. Calculate future balances for PP&E using the corkscrew formation
3. Exit (Output) - this will produce the company’s ending PP&E amounts for each period, which will flow into the balance sheet

Question 16

List:

The (3) Steps for the Asset Schedule [Tax Basis]

Answer 16

1. Enter (Input) opening tax basis, capex additions and tax depreciation rate. Also will need first-year tax depreciation assumption
2. Calculate the future balances for the tax basis using the corkscrew formation
3. Exit (Output) - this will produce the company’s future tax depreciation. Need the ending tax basis amounts for each period

Question 17

Define:

Accelerated Depreciation

See more tax terms from Accounting Principle course

Answer 17

A government concession that allows for higher depreciation expenses early in the asset life which lead to lowering taxable income.

Question 18

Define:

Loss Carryforward

See more tax terms from Accounting Principle course

Answer 18

A government concession that allows the accumulation of losses over time and act like credits to lower taxable income for the company in the future.

Question 19

List formula:

Taxable Income

Answer 19

Taxable Income = **Accounting Income (EBT) + Accounting Depreciation - Tax Depreciation - Use of Tax Losses **

Question 20

List:

The (3) Steps for the Tax Schedule

Answer 20

1. Enter (Input) earning before taxes (EBT), accounting depreciation, tax depreciation and tax loss pool
2. Calculate taxable income by beginning with EBT
3. Exit (Output) - this will produce the company’s tax expenses, which are needed for the income statement and cash flow statement. Also, current taxes are needed for FCF and DCF valuation.

Question 21

List / define:

The (3) Steps to Locate Inputs on Excel

Model Review

Answer 21

1. Select the “Go To” dialogue box or press F5
2. Select “Special”
3. Select “Constants” and uncheck “Text” so labels are not highlighted

Purpose: this will highlight all inputs unless they are preceded by an equal sign.

*Note: can change fill color using the shortcut “Alt + H + H” and then select the desired color

Question 22

List / define:

The (3) Steps to Check One Column or Row in Detail on Excel

Model Review

Answer 22

1. Select the “Go To” dialogue box or press F5
2. Select “Special”
3. Select “Row Differences” or “Column Differences”

Purpose: this will check across entire sections of the model at a time.

*Note: there are keyboard shortcuts to get to these quicker: CTRL + \ (row differences) & CTRL + SHIFT +
\ (column differences)