Course Content
1. Spreadsheet: design and structure
Mastering key steps with practical exercises to practically design and structure your spreadsheet layout in order to achieve a well-constructed and error-free financial model
In order to determine the best financing structure and address all the risks for your company’s financial processes, you need to design and construct a financial model that will assist you in effectively managing your company’s financial affairs. By applying this model, you will be able to use tried and tested spreadsheet techniques for tracking company cash flow and monitoring profitability.
Master the key steps in successfully building a comprehensive and accurate finance model as a decision-making tool by understanding:
- What is financial modelling and its objectives?
- The power of spreadsheets:
- Forecast future cash flows
- Ability to service debt repayments
- Coordinate various revenue and expenditure budgets
- How to avoid the pitfalls of a poorly designed spreadsheet:
- Key variables and rules
- End result of the model
- Formatting of the spreadsheet
- The layout in designing an error-free financial model
- Data validation
Financial Modelling With Excel /
2. How to apply the practical features of excel databases in your financial model Practical Exercises
Data is of no practical use if you don’t use it to improve your business decisions. You also need to test the validity of the data in your financial model by identifying its accuracy, reliability and relevance to your worksheets. Learn the types of data that need to be analysed when applying excel.
Discover how data analysis can improve business decisions by examining:
- What is a database?
- The application of a single table database
- The use of a two-table database
- The steps for working with a database in Excel
- The features of filters
- The appropriate use of custom filters
- How to access data using VLOOKUP and HLOOKUP?
When overseeing your financial model, you are expected to effectively manage revenues and costs and at the same time, consider the impact your decisions have on profitability and growth from both a strategic and tactical point of view. Pivot tables are used to perform a cross-tabulation of data, summarising them into one or more classifications. By creating pivot charts, which combine all the same functionality of standard excel charts with the dynamic characteristics of pivot tables, you will be able to achieve a graphic report that updates data whenever it is changed.
Learn how to:
- Understand the key features of a Pivot Table
- Prepare a Pivot Table
- Analyse and display data from different points of view
- Create a Pivot Chart from your Pivot Table
Financial Modelling With Excel /
4. Developing appropriate techniques for using data tables
By constructing data tables in your spreadsheet model, you will be able to view, not only a few scenarios of your data, but also a large number of permutations that can be summarised in one table. This will help you to achieve a broader perspective of your financial model. Data tables are the basis of Excel’s most sophisticated way of developing a sensitivity analysis.
- Learn how to apply these key techniques by:
- Examining the features of a data table
- Applying conditional formatting within data tables
- Analysing various formatting techniques
When you are estimating future costs and preparing forecasting reports for your management team, you need to know your products/ services as well as your market. But, is this sufficient for dealing with uncertainty? Learn to use the Goal Seek feature to find a value of a single input and a desired level in terms of output. By applying the Solver technique, you will be able to determine a combination of inputs which optimises output.
Understand the practical application of these techniques:
- Undertaking a sensitivity analysis with the use of data tables
- Using Goal Seek to find the ‘indifference point’
- Applying two-way data tables and solver
- Adopting solver to maximise profit
- Examining two-way data tables and decision-making under uncertainty
Financial Modelling With Excel /
6. Applying scenario techniques to determine alternatives and constraints in your financial model
When modelling more complicated problems than data tables, you may encounter as many as 32 variables during the process. By creating multiple scenarios for a single ‘what-if’ model, you will be able to assess each scenario with its own set of variables.
Understand relationships between scenarios, using scenario summary and pivot table reports by:
- Defining scenarios in your spreadsheet
- Understanding how to browse your scenarios
- Adding, editing and deleting scenarios
- Routing and merging scenarios
- Creating scenario reports
Practical Exercises
Charts have a way of changing over time. New data arrives, old data becomes obsolete and new visual comparisons become meaningful. Excel’s procedures for working with the data can be most effective in creating your charts. You can also add new points and series to a chart, and change the order in which excel plots your series and multilevel categories of information.
Learn how to add trend lines and error bars and apply techniques for effectively charting your spreadsheet data by:
- Adding data
- Removing data
- Changing or replacing data
- Plotting or marking every “nth” point
- Changing the plot order
- Using multilevel categories
- Applying trend lines
- Creating error bars
/ Mullan Training
1st Floor, Blackstaff Studios, 8–10 Amelia Street
Blackstaff Square, Belfast BT2 7GS
Tel: 028 9032 2228 Fax: 028 9032 2229