Advanced Excel Training – Budgeting & Forecasting (4 Days) 25 CPD Hours
Business decisions and most routine financial roles are increasingly made based on a financial model built in Excel. In today’s ever-changing business environment being able to accurately model and forecast the volatile economic inputs is a critical skill for business professionals, the capability to write simple spreadsheets is just not enough. You have to be able to incorporate all the “what if” scenarios and stress any proposal to its limits. Financial modelling involves developing a dynamic spreadsheet that describes a financial structure. A well-structured financial model can facilitate and improve the reliability, quality and timeliness of your decision-making. Modelling techniques are used in many different areas, such as investment appraisal, capital planning, budgeting, valuation, financial analysis and forecasting. This course will help participants to design and structure budgeting and forecasting models & to perform what-if analysis in such a way that budget preparation will be reduced to simple input of assumptions.
This highly-practical, intensive, hands-on programme is packed with cutting edge spreadsheet design ideas to take your model structuring skills to the next level. By adopting best practice modelling standards you can develop better models, in less time and with greater accuracy. By the end of this course, you will walk away with the ability to design a robust budgeting and forecasting model and to perform what-if analysis based on model results. Your budgeting and project appraisal will be simplified to just button clicking!
The following is guaranteed from this practical, all-in-one workshop:
- Best Practice Modelling – Learn and apply the most effective ways to design, cross-check and structure robust, flexible and dynamic models to get your job done faster and painlessly;
- Excel Efficiency Tools – Learn efficiency tools that will make you super-efficient in Excel
- Excel Formulas & Functions – Leverage on a wide range of dynamic Excel functions, mathematical calculations, graphics and reporting features to work faster and accurately.
- Report Formatting & Presentation – Effectively communicate the results of your models to team members and senior management by way of dynamic reports that automatically change when source data changes.
- Graphical Analysis – Ability to design and format different kinds of graphs to appropriately present financial information.
- What-if Analysis – deal with risk and uncertainty by creating scenarios, building stress tests in your models and performing sensitivity analysis to make better, more profitable business decisions.
- Understanding the co-relation between P&L statement, Balance sheet and Cash flow statements and using them for business forecasting
Who Should Attend
This course has been designed for both student and professional financial officers, budget officers, middle and senior management from government agencies, non-government agencies and the private sector who are involved in the use and analysis of financial data and models for decision making including:
- Financial Controllers,
- Managers, Auditors,
- Administrators & Directors,
- Financial & Business Analysts,
- Private Equity and Venture Capitalists,
- Project Managers, Lenders & Corporate Treasury Managers,
- Risk Managers,
- Portfolio Managers,
- Economists & Corporate Finance Professionals,
- Budget Analysts and Financial & Strategic Planners and General Managers Sales,
- Marketing & Human Resources Managers,
- Middle & Lower level staff whose jobs involves preparation of budgets and forecasts.
- All delegates will receive a “challenges & expectations” form to ensure that the course director gains an outline of delegates’ job functions and the personal objectives for the course. The course is delivered using formal lectures combined with practical and interactive exercises to reinforce the concepts covered in each teaching session. Emphasis is placed on delegates gaining practical, hands-on experience of the design and construction of financial models in Excel.
- Macros will also looked at extensively. Delegates will also benefit from formal lectures and group discussions. Comprehensive notes will be provided for future reference.
- Case studies runs through the whole course intended to allow delegates to apply course concepts by producing a comprehensive model with a wide range of Excel tools and methods.
The course begins by concentrating on learning key advanced formulas, spreadsheet best practice and the advanced use of Excel. These skills are then applied to the construction of a robust budgeting model. The course is taught using a step-by-step approach to enable delegates to master the concepts.
What to bring for the course
Bring your Laptop or Desktop Computer loaded with Excel 2010 or better. The course will be demonstrated mostly using Excel 2019 or 2016. Rental of Laptop pre-loaded with Excel maybe available at $50 per day per laptop. Kindly advise us 3 days prior to the day the training commences.
Upon completion of each of these courses, you will receive a prestigious Certificate of Attendance, as a recognition of your accomplishment, skills and knowledge learned from this course. If you desire to take our competence tests, upon successful completion of the tests you will receive a Celsoft Certified Technician Certificate. These certificates will bear CPD hours.
You will take away with you the following from the training:
- A folder containing course manuals, exercises and reference models.
- An interactive manual loaded with exercises, solutions and brief notes.
- A pen and a note book for your in-course notes.
Support is available by email and telephone after the course to ensure that delegates can continue to develop and apply the principles learnt on the course. To access our support portal, please visit, www.mycelsoft.com
A. BUDGET PREPARATION, INTRODUCTORY TECHNIQUES
- Structuring the model – Understanding the business logic, assumptions and the structure of the model.
- The Opening Balance Sheet – Understanding the role of the opening balance sheet in achieving self balancing budgets.
- Quantitative Budgeting – Understanding the preparation of quantity or unit budgets as a prerequisite to the preparation of value budgets.
- Key Financial Statements – Understanding the role and inter connectivity between the Balance Sheet, Cash flow Statement and income Statement.
- The Assumptions Sheet – Designing the Assumptions sheet and the use of colour codes according to International Best Practice Standards.
- Linking Budgets – Understanding the principles of linking every budget to the financial statements according to the Accounting Equation.
- Offsetting / Lagging – Understanding the use of the OFFSET function to offset or lag budgets in Excel.
B. THE HUMAN RESOURCES BUDGET
- Preparation of the recruitment plan – Designing the recruitment, promotions and terminations budgets in numbers (units).
- Translating Unit budgets to monetary values – Preparation of the Salaries and benefits budgets.
- PAYE Budgets – The use of the MATCH & INDEX functions to prepare PAYE budgets picking from the Tax Tables.
C. THE CAPITAL EXPENDITURE BUDGET
- Depreciation computations – Designing and preparation of the capital expenditure budget, including using the SLN and DB functions to calculate depreciation and netbook values.
D. THE SALES BUDGET & FORECASTS
- Design and preparation – Designing and preparing the sales budgets.
- Forecasting – Performing advanced statistical analysis & forecasting using the FORECAST, TREND, GROWTH, CORREL & FREQUENCY functions. Using the Analysis Toolpak to compute MOVING AVERAGES, CORRELATIONS & COVARIANCES.
- Consolidating branch sales budgets – consolidating branch budgets using the consolidation by category technique.
E. LOAN AMORTIZATION & LINKING TO FINANCIAL STATEMENTS.
- Preparation of a loan amortization schedule– Amortizing a loan and linking the schedule to the financial statements, including the use of the IPMT, PPMT and PMT functions.
F. LINKING AND BALANCING-OFF
- Linking all budgets to financial statements – Balancing off the balance sheet.
G. DEALING WITH BUDGET MODEL INPUTS
- Control tools – Use of form controls such as SPIN BUTTONS, COMBO BOXES, CHECK BOXES, COMMAND BUTTONS, SCROLL BARS, OPTION BUTTONS and GROUP BOXES through cell linking.
- Protection – Protect your model so that it can be used by anyone with a limited use of Excel® including locking and protecting cells. Understand how to protect cells, worksheets and workbooks.
- Data Validation – Restriction incorrect data entry with data validation including creating error validation messages.
H. DYNAMIC PRESENTATION OF MODEL RESULTS
- Hyperlinks – Accessing and creating hyperlinks within a workbook and to other files and websites and the preparation of the switchboard thereof.
- Grouping and Ungrouping – how to group and hide, considering the benefits of each;
- Conditional formatting – Learn how to have Excel® automate variance analysis using complex conditional formatting.
- Charts and graphs – applying Excel® ’s rich contextual tabs to analyse, modify and print a chart, modify titles, labels, axes, grid lines and legends, working with data series and data range, formatting charts, data series and chart area options, changing colour, borders, patterns, working with templates, styles and formats including inserting pictures, text boxes and shapes.
- Sparklines – View trends in data quickly by making use of sparklines (2010).
- Embedding – Learn how to embed an Excel® worksheet in Word and vice versa, including linking the packages for faster reporting.
I. SCENARIOS AND SENSITIVITY ANALYSIS
- Manual scenario building– In-cell drop-down boxes & Combo-box drop down boxes.
- Data Tables – Show multiple scenario outcomes simultaneously with one and two-dimensional Data Tables including scatter charts to show relationship of variables.
- Scenario Manager – Using the Scenario Manager to create store and show various scenarios.
J. OPTIMIZING & TARGETING THE OUTPUT OF YOUR MODEL
- Goal Seek method – Goal seeking to calculate break-even point.
- Optimizing using Solver add-in – Use Solver to maximize profit or minimize costs.
K. BULLET-PROOFING YOUR MODEL
- Protection – Protect your model so that it can be used by anyone with a limited use of Excel® including locking and protecting cells.
- Understand how to protect cells, worksheets and workbooks.
L. PROJECT APPRAISAL
- IRR & NPV – Using the IRR and NPV functions to determine the projects to consider under limited resource conditions.
H. QUANTRIX MODELER IN BUDGETING
- Explore the use of latest modelling technologies like Quantrix Modeler in dynamic modelling.