Financial Modelling in Excel Short Courses
|Start Date||Start Time||Duration||Cost||Course Code||Apply|
|Wednesday 3 October 2012||18:30 - 20:30||10 weekly classes||£460.00||CE2564||Course Full|
|Wednesday 23 January 2013||18:30 - 20:30||10 weekly classes||£460.00||CE2564||Enrollment Closed|
|Wednesday 1 May 2013||18:30 - 20:30||10 weekly classes||£460.00||CE2564||Course Full|
Tutor InfoTony Pamphilon has many years' experience of teaching and managing in colleges and universities, in the UK and overseas. His particular interest is in developing and delivering computer based accounting courses. He has managed large departments and taught both undergraduate and post graduate programmes. He currently works as a freelance lecturer and consultant and teaches a wide range of accounting subjects at London Metropolitan and Birkbeck Universities.
EligibilityThis course assumes a good working knowledge of Excel up to using Excel functions. It also assumes a good working knowledge of the structure and composition of accounting statements, specifically the Profit & Loss statement, Balance Sheet & Cash Flow Statements. As a gauge of suitability for the course prospective students should be able to answer the following questions:
- How would a change in depreciation charge affect Net Assets and cash flow?
- How would a change in Debtor days impact the cash flow?
- If cell B3 contains a figure that can either be positive or negative how would you write an IF statement that picks up the value of cell B3 if it is negative, but not otherwise?
- Can you describe what the HLOOKUP function does?
You are expected to be able to answer these questions before enrolling onto the course.
English RequirementsApplicants must be proficient in spoken and written English.
What will I learn?
• Course Structure: Excel hints & Tips; Keyboard shortcuts; Using Styles; Customising the Quick Access Toolbar
• Modelling Best Practice: Design consistency, keeping unique formulae to a minimum; Limiting formulae complexity; Separating inputs, calculations outputs; Naming, access, version control; Error checks, completeness checks, check of checks whose result is visible on each sheet; Using hyperlinks to navigate through a sheet
• Modelling the P&L: Different approaches for forecasting key P&L Lines: % Growth, % to Sales, Other drivers (volume & price)
• Modelling the Balance Sheet: Working Capital Days; Fixed assets, depreciation, asset life etc; Cash as a balancing items (advantages and disadvantages); Using "if" statements to ensure that cash/overdraft & other items that could be in either assets or liabilities; Resolving the issue of interest circularity
• Modelling the Cash flow: Modelling the cash flow separately from the BS cash and reconciling back. Reconciling movements in cash and cash equivalent with movements in Net Debt/Cash
• Modelling Complex Debt Structures: The debt hierarchy. Creating debt rules; Valuing an LBO
• Sensitivity Analysis including Monte Carlo Simulation, scenario manager; Creating scenario analysis using user forms to illustrate the outputs of the model under different scenarios; Using; Using Data tables to illustrate the impact of incremental changes to key inputs on the outputs of the model. Monte Carlo simulation to show range of possible outcomes and likelihood
• Advanced modelling techniques including macros, solver; Using a Macro to solve interest circularity issues; Using Solver for Capital Budgeting; Using Goal Seek to calculate input drivers and the dangers with such an approach
• Wrap up including auditing other people's financial models: Checking for errors; suitability of outputs; sources of data for input assumptions.
By the end of the course, you will be able to:
- Best practice principles of model design
- Establishing suitable driver models for forecasting the Profit & Loss and the Balance Sheet for both mature businesses and start ups
- Using "IF" statements to model complex debt structures
- Sensitivity Analysis including Monte Carlo simulation
- Advanced modelling techniques including use of Macros or Solver
- Error checking and debugging models
Guide to Business Modelling (John Tennent & Graham Friend)
Practical Financial Modelling (Jonathan Swan)
Excel 2007 Bible (John Walkenbach)