Accounting Information systems and Financial Modelling
Week 1
Planning – need to plan based on who the data is for, who will be using it, capacity/level of
excel skill of intended user, data that will be input,
Formula flexibility – no formulas within a spreadsheet should contain numbers. All the
number variables should be in an input area of the spreadsheet and all formulas should only
contain cell references.
Relative cell reference ( eg. =C2) – if this formula was written in cell C4, it would mean this
cell C4 should contain the same information as the cell 2 rows above, within the same
column. If this formula was copied across from C4 to E4, then it would remain relative to
those cells, meaning the data in cells D4 and E4 should equal the data in the cells 2 rows
above them.
Absolute cell reference (eg. $C$4) – this means the cell reference is locked to column C, row
4 no matter where the formula is copied on the spreadsheet.
Mixed cell references (eg. =B9 * $C$2) – if this formula was written in a cell B11. The
relative reference (B9)
means copy the value of the cell in the same column two rows up the
spreadsheet, and multiply it by the absolute reference in C2. No matter where this formula is
copied, it will always use the value in C2 as the multiplying factor, and the cell reference is
locked (absolute reference).
Reporting Area Design – this should closely replicate an accounting balance sheet layout and
include appropriate titles, dates, underlines, double underlines. If all values in a column are $
or ,000 measures, then put the $ and ,000 in the title cell, and list all data points below as
plain number to increase simplicity.
Auditing – spread sheets can be audited to check for errors. These are under the formula tab
in excel. Trace precedents, trace dependents, error checking.
Week 2
Future Value and Present Value and Ordinary Annuity – need to know formulas in excel.
PMT formula – need to commit to memory with all inputs.
IF Function – there will be 1 or 2 formulas in the final exam where we will be asked to use
the IF function!
Internal rate of Return – the rate of return when NPV is equal to 0. Cannot be used with cash
flows that do not occur in the first year, or with negative cash flows (paying rent etc)
Spreadsheet Decision Logic – Not required to read, but contains IF and VLOOKUP
functions.
IF Function – IF(logic/condition, value if true, value if false)