Excel Business Modelling for Telecoms Companies

Develop best practice business modelling techniques and understand the challenges and solutions for modelling within the telecommunications sector.

Course Overview

This course is designed specifically for those required to use Excel within the communications sector. The business modelling courses can be run over two, three or four days and each course is tailored specifically for your organisation and based on your choice from the range of modules listed below. A number of modules focus on best practice and participants will learn the "golden rules" of best practice model structure and design as well as exploring many, often underutilised, features and functions of Excel which make developing models faster and the resulting models more powerful. Participants will also learn how to use simple macros and Visual Basic to turn their spreadsheets into impressive applications. Other sessions focus on different elements of the financial statements including sessions on revenue forecasting techniques such as the use of s-shaped and diffusion curves as well as all other elements of the financial statements such as costs, capital expenditure as well as valuation techniques as well as tools for assessing the risks of a project. The course is highly practical and participant numbers are strictly limited to ensure high levels of personal attention. Participants will leave with a library of model components developed during the course which they can use immediately in their own models.

About the Trainer

Graham Friend holds an M.A. and an M.Phil. in Economics from Trinity College, Cambridge. On leaving Cambridge Graham joined PriceWaterhourseCoopers as a Management Consultant where he also qualified as a Chartered Accountant. Graham has over 15 years experience of consulting and business modelling experience with a focus in the telecommunications, media and technology sectors. Graham has a wealth of practical experience gained from managing diverse modelling and business planning assignments across Western European as well as Africa, the Middle East, the Caribbean, Australia, Singapore, Hong Kong and China. Graham is an expert in strategic, commercial and financial analysis and planning as well as business modelling. In addition to speaking at conferences around the world Graham was asked by The Economist to co-author two books, one on Business Planning and the other on Business Modelling. Graham is a highly experienced business trainer and has a gift for presenting complex commercial issues in a clear, concise and practical manner. Graham delivers training to companies such as Vodafone, Orange and Motorola as well as regulators such as the United Kingdom's Ofcom.

Course benefits

• Discover the principles of best practice business modelling
• Learn how to plan, structure and construct models for use within the communications sector
• Review Excel's functions and features and how to use them
• Develop modular business models to ensure greater efficiency
• Appreciate how to use macros to make modelling easier and quicker
• Learn how to use Visual Basic to turn simple spreadsheets into impressive applications
• Develop techniques for efficiently testing and debugging models
• Learn how to use your model to make better management decisions
• Gain advice from modelling professionals on how to develop your own models

Who should attend?

The workshop assumes some previous, limited experience of Excel such as the ability to create simple formulas. Those who will benefit most are those that work regularly with Excel and may include individuals working as

• Cost centre managers and budget holders
• Engineers, IT and technical managers
• Sales and marketing managers
• Product development managers
• Operational managers
• Project managers
• Corporate attorneys / legal professionals
• HR professionals

Prerequisites

The course assumes some basic familiarity with Excel and the ability to move around different sheets within Excel as well as being able to construct simple formula.

Course Structure and Contents

The business modelling course is flexible and can be run over two, three or four days depending on your choice of modules. Many companies select the first 8 modules which together provide comprehensive coverage of best practice business modelling techniques. Companies then go on to select further modules to extend the course into the areas of particular interest. We strongly recommend that all courses at least include the first eight modules.

Principles of best practice business modelling

• Using scenario planning to identify model inputs and outputs
• Learning the three golden rules of business modelling
• Developing modular models
• Using range names

Model structure and layout

• Creating a scenario and sensitivity manager
• Identifying best practice model structure and input and output sheet design
• Using Excel's templates and styles features
• Creating user inputs with data validation
• Working with graphs
• Working with multiple workbooks and linking and consolidating files

Excel's cool functions and features

• Discovering new ways to enter and manipulate data
• Learning to use the I-transformation for graphs
• Learning useful functions including IF and nested IFs, AND, OR, CHOOSE, OFFSET, LOOKUP, SUMPRODUCT, SUMIF, MATCH, INDEX
• Working with Array functions
• Creating Pivot tables

Testing and debugging

• Developing a testing and debugging strategy
• Using the auditing toolbar
• Using GOTO SPECIAL to debug models
• Using FIND to locate hidden errors

Introduction to macros

• Using the record function to create simple macros
• Working with the Visual Basic editor to edit recorded macros
• Creating buttons to activate macros
• Creating your own personal toolbar
• Building a navigation menu for your model
• Automate the printing of your model

List boxes, buttons and scroll bars

• Learning how to turn a spreadsheet into an application
• Learning how to use scroll bars and spinners
• Making use of check boxes and radio buttons
• Learning how to use lists
• Generating messages and alerts

Simple programming using Visual Basic

• Understanding the elements of a Visual Basic macro
• Learning how to extract and enter data into a spreadsheet using VB
• Performing operations using Visual Basic
• Understanding loops and subroutines
• Creating macros that run automatically when you open the model
• Creating splash screens

Getting the most from your model

• Learning how to use GOAL SEEK and SOLVER
• Learning how to use Excel's own SCENARIO MANAGER
• Performing What If and Sensitivity analysis
• Automating the use of GOAL SEEK and running sensitivities with Visual Basic macros

Overview of the financial statements and investment appraisal

• A theoretical session
• Examining the different types of financial model
• Reviewing the layout, contents and significance of the financial statements
• Understanding the inter-dependencies within the financials
• Reviewing the theory of investment appraisal techniques such as Discounted Cash Flow and the Internal Rate of Return

Customers and revenues

• Reviewing different approaches to forecasting customers and revenues
• Comparing top down versus bottom up models
• Contrasting real versus nominal forecasts
• Examining time series and simple regression techniques
• Modelling the dynamics of the customer base
• Creating a simple revenue model

Advanced revenue modelling

• Developing multiple regression techniques
• Modelling product life cycle curves
• Modelling elasticity effects
• Creating a debtors account and modelling working capital
• Approaches to modelling bad debt

Operating costs and capital expenditure

• Understanding the different approaches available for modelling costs and capital expenditure
• Making cost and capex forecasts endogenous to the model
• Discovering techniques for modelling depreciation and amortisation
• Modelling stock and creditors

Building the financial statements

• Establishing the layout of the financial statements within the model
• Linking revenue and cost workings into the Profit and Loss
• Modelling working capital and creating the Cash Flow Statement
• Linking workings into the Balance Sheet

Interest, taxation and dividends

• Examining the challenges of modelling interest charges and avoiding circular references
• Computing taxation charges for the Profit and Loss and for valuation workings
• Modelling dividends and shareholder funds

Completing the financial statements

• Establishing the links between the financial statements
• Modelling financing structures
• Modelling analytical ratios
• Testing the workings of the financial statements in the model

Investment appraisal

• Creating a project appraisal module
• Using Excel's NPV, IRR functions
• Creating your own valuation models
• Modelling terminal values and normalising final year cash flows
• Techniques for calculating Pay Back
• Using the model for Break-Even analysis and sensitivity analysis