Modeling excel

what is modeling

creating a simplified version of reality and working with it to understand and control some aspect of the situation

primary goal of modeling

to use data to generate insight or improved understanding to make better decisions

mathematical models

a strucutured way to represent the relationships between variables, used to break complex business scenarios down to quantifiable terms

excel as a modeling tool will let us

quickly set up formulas that reference inputs

real time updates: change inputs, instantly see new outputs

components of an excel model

inputs (cells contain numbers that can be changed)

outputs and calculations (these cells contain values calculated from inputs using formulas and cell references to the inputs)

cell references

use the column letter and row number (A1) to tell excel which cells value to use in a formula

breakeven

when profits= costs

formula auditing tools

trace dependents

trace precedents

show formulas

these are excel features that show errors

one variable data table

change value for 1 input to see how it affects one or more outputs (formulas in model)

two variable data table

change 2 inputs to see effects on 1 input (formula in model)

goal seek

allows you to find the value of an input that causes an input to be value you specify

how do you reference an output cell in a data table

the output cell must be referenced as an absolute reference cell ($) for 1 value table set the column input cell

for 2 value table set both row input and column input

charts

represents numeric data visually, allows you to see trends and patterns

conditional formatting

when formatting is applied to cells only when they meet a condition you specify

what is a function

built in formula that performs a specific calculation for you (takes input values, performs calculations, returns a result)