Index
Financial Modeling for Decision Making: Using MS-Excel in Accounting and Finance
ISBN: 978-1-78973-414-0, eISBN: 978-1-78973-413-3
Publication date: 1 September 2020
This content is currently only available as a PDF
Citation
Messer, R. (2020), "Index", Financial Modeling for Decision Making: Using MS-Excel in Accounting and Finance, Emerald Publishing Limited, Leeds, pp. 327-330. https://doi.org/10.1108/978-1-78973-413-320201021
Publisher
:Emerald Publishing Limited
Copyright © 2020 Emerald Publishing Limited
INDEX
Activity-based budgeting
, 243
Alpha Corp.
, 8
Amortization table decisions
bank borrowing
, 260
corporate bonds
, 260
demonstration exercise
, 261–273
equipment
, 260–261
financial management techniques
, 261
MS-Excel functionality
, 261
plant
, 260–261
property
, 260–261
Balanced scorecard budgeting
, 243
Break even decisions
break-even volumes
, 22
cost functions
, 22
cost-volume-profit analysis
, 22
demonstration exercise24–36
financial management techniques
, 22–23
MS-Excel functionality
, 23
Break-even volumes
, 23
Budget management decisions
analysis of variances
, 245–256
budget development
, 242–243
case facts
, 244
cash flow budgets
, 243, 245
CHILL-DUDE income statement budget
, 251, 253–256
definition
, 243
demonstration exercise
, 244–256
financial management techniques
, 243
income statement budget
, 244–245
monthly income statement budget
, 245, 247
relevant excel functionality
, 244
theory
, 242–243
Business intelligence
, 200–201
Business valuations
, 128–129
cost of capital
, 128
demonstration exercise
, 129–147
Excel functionality
, 129
techniques
, 129
Capital asset pricing model (CAPM)
, 63, 128
Capital budgeting
cash flows
, 62
demonstration exercise
, 63–76
discount rate
, 62–63
Excel functionality
, 63
internal rate of return (IRR)
, 62
net present value (NPV)
, 62
payback period
, 62
Capstone demonstration exercise
, 277–319
Case facts
activate Solver
, 113–118
base case scenario
, 65–68
color coding and comments
, 9, 16
dependent and independent variables
, 85
discount rate
, 64–65
Excel Solver dialog box
, 109–113, 117, 122
Excel worksheet model
, 109–111
expected fixed costs
, 24
exponential smoothing dampening coefficient
, 54, 55–56
exponential smoothing forecast
, 47–53
financial information
, 8
financial ratios
, 133
forecast income statement and balance sheet
, 134
High-As-A-Kite (HAAK)
, 132
incremental net cash flows
, 65
initial public offering (IPO)
, 129
interaction variables
, 100–102
internal rate of return (IRR)
, 68–76
Junior Achievement (JA)
, 6, 8
Mean Absolute Percentage Error (MAPE)
, 54–59
measuring correlations
, 84–88
model optimization
, 95
moving average forecast
, 43, 46, 48
multivariable regression
, 85, 95
named ranges
, 9, 12, 14–15
net present value (NPV)
, 68–76
nonnumeric variables
, 95–97
packages
, 25
Park & Wash (See Park & Wash) payback period
, 68–76
present value of cash flows
, 140, 143
reasonable forecast assumptions
, 133–134
relationships
, 81–82
scatterplots
, 43–44, 82–85
Screw-It case facts
, 107–109
sensitivity report
, 113–118, 123
share value and test sensitivities
, 140–147
Shoot-It unit production costs and sales volume
, 119
single-variable regression
, 85
variable costs
, 24
WTF Builders (WTF)
, 64
Cash flow budgets
, 243, 245
Cash flow metric
, 228–233
Causation
, 78
CHILL-DUDE income statement budget
, 251–256
Confidence interval
, 79
Contribution margin
, 22
Control decisions
, 291–306
Correlations
, 78
Cost functions
, 22–23
Cost of capital
, 62
forecasting assumptions
, 128
pro-forma financial statements
, 128
Cost of debt
, 63
Cost of equity
, 63
Cost-volume-profit (CVP) analysis
, 22
break-even model two-way data table
, 31, 34–36
break-even units and sales
, 31, 29
formulas
, 25, 27
goal seek
, 25, 28
one-way data table
, 25, 30–35
Cyclical sales pattern
, 41–42
Dashboard
, 195
Data access
, 150
Data analytic models
, 150–151, 152
data access
, 150
data analysis
, 151
data visualization
, 151
descriptive analytics
, 152
diagnostic analytics
, 152
predictive analytics
, 152
types
, 151–152
Data cleaning
, 156–159
Data engine
, 192, 195
Data tables
, 153–156
Data visualizations
, 151, 213–222
Data warehouses
, 150
Demonstration exercise
, 159–186
amortization table decisions
, 261–273
budget management decisions
, 244–252
case facts
, 159
definition
, 159
descriptive analytics
, 159–167
diagnostic analytics
, 172
financial dashboard decisions
, 191–200
food-stuff demographic data
, 161
give-it-to-me Entertainment (GIT-me)
, 201–239
numeric format
, 167, 172
Power Pivot
, 172–186
values function
, 167
Dependent variable
, 78
Descriptive analytics
, 152, 159–167
Diagnostic analytics
, 152, 172
Discounted cash flows (DCF)
, 62
Discount rate
, 62–63
Exponential smoothing dampening coefficient
, 54, 55–56
Exponential smoothing forecast
, 47–53
Feedback decisions
, 306–319
Financial dashboard decisions
common financial measures
, 190
dashboard principles
, 190
demonstration exercise
, 191–200
financial management techniques
, 191
give-it-to-me Entertainment (GIT-me)
, 191
internet-based content providers
, 191
relevant excel functionality
, 191
Financial management techniques
, 152–153
amortization table decisions
, 261
budget management decisions
, 243–244
data analytic models
, 152
financial dashboard decisions
, 191
interpretation
, 152
pivot tables
, 152
Financial model
, 4–5
communicate
, 4
data
, 4
demonstration exercises
, 5–6
development
, 4–5
Excel terminology
, 5
optimization
, 4
practices
, 5
problem definition
, 4
test
, 5
Food-stuff demographic data
, 161
Forecasting models
, 40
Functional redundancy
, 4
Give-it-to-me Entertainment (GIT-me)
business intelligence
, 200–201
dashboard
, 195, 200
database
, 192
data engine
, 192, 195
decisions
, 191–195
demonstration exercise
, 201–239
power business intelligence
, 201–205
references
, 195
High-As-A-Kite (HAAK)
, 132
Hurdle rate
, 62
Income statement budget
, 245, 247
Incremental cash flows
, 62
Independent variable
, 78
Initial public offering (IPO)
, 129
Interaction variables
, 97, 100–101
Internal rate of return (IRR)
, 62
Junior Achievement (JA)
, 8
Learning financial modeling
, 2–3
Linear programming
applications
, 106
demonstration exercise
, 107–118, 118–125
Excel functionality
, 107
history
, 106
machine hours (MH)
, 107
production scheduling
, 106
product mix
, 106
unit contribution margin (UCM)
, 106–107
Line of best fit
, 79
Machine hours (MH)
, 107
Mean Absolute Percentage Error (MAPE)
, 41, 54–59
Monte Carlo simulations
, 1
Monthly income statement budget
, 245, 247
Moving average forecast
, 43, 46–47, 49
MS-Excel
, 2, 261
applications
, 2–3
business valuations
, 129
capital budgeting
, 63
financial model
, 5
functionality
, 5, 23, 43
internal rate of return (IRR)
, 63
linear programming
, 107–108
net present value (NPV)
, 63
regression analysis
, 80
Solver
, 107–108
Multicollinearity
, 83
Multivariable regression
, 85, 95
Net present value (NPV)
, 62
Nonnumeric variables
, 95, 97
Numeric format
, 167, 172
Omega capital budgeting decision model
, 285–286
Park & Wash
, 24
break-even model
, 24, 26
cost-volume-profit (CVP) model
, 25, 27
expected fixed costs
, 24
variable costs
, 24–25
Participative budgeting
, 243
Payback period
, 61–62
Pearson moment correlation coefficient
, 78
Pivot tables decisions
data analytics
, 150–151
data warehouses
, 150
demonstration exercise
, 159–186
financial management techniques
, 152
relevant excel functionality (See Relevant excel functionality)
Planning decisions
, 279–291
Power business intelligence
cash flow metric
, 228–233
data visualizations
, 213–222
power query
, 206
profitability metric
, 228
ratio metric
, 222–228
return on equity metric
, 233
sales and income trends
, 233–239
Power Pivot
, 172–186
Power query
, 206
Predictive analytics
, 152
Profitability metric
, 228
Quality assurance (QA)
, 4
Ratio metric
, 222–228
Regression analysis
causation
, 78
confidence interval
, 79
correlations
, 78
demonstration exercise
, 80–102
Excel functionality
, 80
Pearson moment correlation coefficient
, 78
predictive model
, 79–80
p-values
, 78
regression equation
, 79
residuals plot
, 79
standard error
, 79
strength of relationships
, 78
Relevant excel functionality
budget management decisions
, 244
data cleaning
, 156–159
data tables
, 153–156
financial dashboard decisions
, 191
Residuals plot
, 79
Response variable
, 78
Return on equity metric
, 233
Screw-It
, 107–109
Seasonal sales pattern
, 41–42
Single-variable regression
labor hours
, 85, 92
machine hours
, 85–89
output
, 85, 94–95
production runs
, 85, 93
Standard error
, 79
Strategic budgeting
, 243
Target operating income (TOI)
, 23
Teaching financial modeling
, 3–4
Time series patterns
cyclicity
, 40
seasonal patterns
, 40
trending
, 40
Times series forecasting
, 39–41
cyclical sales pattern
, 41–42
demonstration exercise
, 43–59
Excel functionality
, 43
Mean Absolute Percentage Error (MAPE)
, 41
seasonal sales pattern
, 41–42
steps
, 42
trending sales pattern
, 41
Top-down budgeting
, 242
Trending sales pattern
, 41
Unit contribution margin (UCM)
, 23, 106
Unit variable cost (UVC)
, 23
Values function
, 167
Weighted average cost of capital (WACC)
, 3, 62
West Texas Intermediate (WTI)
, 40
WTF Builders (WTF)
, 64
best case outcome
, 68, 73
investment base case outcome
, 68, 72
scenario summary
, 68, 75
worst case outcome
, 68, 74
Zero-based budgeting
, 242
- Prelims
- Chapter 1: Better Learning Decisions
- Part 1: Decisions Made about the Future
- Chapter 2: Break Even Decisions
- Chapter 3: Times Series Forecasting Decisions
- Chapter 4: Capital Budgeting Decisions
- Chapter 5: Regression Analysis Decisions
- Part 2: Decisions Made about the Present
- Chapter 6: Linear Programming Decisions
- Chapter 7: Business Valuation Decisions
- Chapter 8: Pivot Tables Decisions
- Part 3: Decisions Made about the Past
- Chapter 9: Financial Dashboard Decisions
- Chapter 10: Budget Management Decisions
- Chapter 11: Amortization Table Decisions
- Part 4: Decisions Made in Time
- Chapter 12: Making a Lot of Decisions
- Appendix 1: Excel Shortcut Keys
- Appendix 2: Common Financial Ratios
- Index