BUDGETING, FORECASTING AND COST MANAGEMENT USING EXCEL

(1)

BU DGET ING, FOR EC A ST ING A ND

COST M A N AGE M EN T USING E XCEL

// Introduction

The use of Excel is essential for anyone working in the broader area of finance. It is widely admitted that most users are very limited in their use of all the power which Excel offers. Generally people learn a couple of the essential functions and just make do. This results in huge inefficiencies in time taken to work properly.... and also results in less than optimal decisions.

The main reason which is given for this inefficiency is being busy... busy... busy at the office. This course resolves the problem by using two full days to focus on new learning and efficiency using the powerful functions and commands in Excel. The focus is on the key planning tool.... the budget. Another word for a budget is really that it is a

calculated guess about the future that is a forecast. The course therefore teaches the optimal use of Excel in the area of management accounting. It will achieve two objectives for participants:

• Develop Excel skills to use many of the functions and commands with ease

• Create confidence in making better financial decisions for your business

// Who Should Attend?

• Managers responsible for financial reporting • Accountants and other accounting staff • Financial controllers and financial managers • Business owners, entrepreneurs

• Business analysts

Liberation Through Education

-Innovative Training Events for a Competitive African Market 10 - 11 April 2014 at Hotel Cardoso, Maputo, Mozambique

PC

BASED

EVENT

This pc-based workshop will significantly enhance the skills and knowledge of delegates and improve their ability to develop detailed budgeting, forecasting and cost control

(2)

REGISTER NOW - Tel: +27 (0)12 653 2522 // Fax: +27 (0)86 570 8986 E-mail: [email protected] // Website: www.nolwaziafrica.com REGISTER NOW - Tel: +27 (0)12 653 2522 // Fax: +27 (0)86 570 8986

E-mail: [email protected] // Website: www.nolwaziafrica.com The following is a list of the main formula functions which will be covered:

The following is a list of the main Commands on the Ribbons which will be covered:

FUNCTION DESCRIPTION

FINANCIAL FUNCTIONS [10]

FV Returns the future value of an investment

IPMT Returns the interest payment for an investment for a given period IRR Returns the internal rate of return for a series of cash flows

MIRR Returns the IRR where+ and - cash flows are financed at different rates NPV Returns the net present value of an investment

PMT Returns the periodic payment for an annuity

PPMT Returns the payment on the principal for an investment for a given period PV Returns the present value of an investment

XIRR Returns the IRR for cash flows that is not necessarily periodic

XNPV Returns the NPV for a schedule of cash flows that is not necessarily periodic

MATH AND TRIGONOMETRY FUNCTIONS [8]

POWER Returns the result of a number raised to a power PRODUCT Multiplies its arguments

ROUND Multiplies its arguments SQRT Returns a positive square root

SUBTOTAL Returns a subtotal in a list or database

SUM Adds its arguments

SUMIF Adds the cells specified by a given criteria

SUMPRODUCT Returns the sum of the products of corresponding array components

STATISTICAL FUNCTIONS [11]

AVERAGE Returns the average of its arguments

COUNT Counts how many numbers are in the list of arguments

COUNTIF Counts the number of nonblank cells that meet the given criteria COVAR Returns covariance, the average of the products of paired deviations FORECAST Returns a value along a linear trend

GROWTH Returns values along an exponential trend MAX Returns the maximum value in a list of arguments MIN Returns the minimum value in a list of arguments STDEV Estimates standard deviation based on a sample TREND Returns values along a linear trend

VAR Estimates variance based on a sample Home All Tabs and Quick Access Toolbar Insert Table, Pivot Table, Charts

Page Layout Themes

Formulae Function Library, Name Manager, Formula Auditing Data What if Analysis, Subtotal

Review Comments, Protect Sheet

View Window

Developer Record Macro, Insert (Spin Button)

Please take note that Delegates should have a basic working knowledge of Excel and be in possession of their own laptops installed with any version of MS Excel [Applicable to Excel 2007, 2010 and 2013]

(3)

REGISTER NOW - Tel: +27 (0)12 653 2522 // Fax: +27 (0)86 570 8986 E-mail: [email protected] // Website: www.nolwaziafrica.com

This course will provide you with a proven set of methods, processes, tools and techniques to:

• Analyze and prepare financial statements accurately and aesthbetically

• Assess budgeting models to appropriately capture all of the costs and benefits

• Build models and find your way around excel worksheets to enhance your performance • Deal with tables of data to import, organize and

explore massive data sets

• Consolidate your budgets into financial

statements in excel to increase transparency and business performance

• Ensure you make effective decisions with limiting constraints

• Find your way around the Excel Ribbons to assist you in finding the commands that you need to complete

• Improve the value of money, loans, time and business valuation through financial modeling • Master capital investment projects and cost of

capital to determine your organisations long-term investment

• Develop a flexible forecast system of cost management and Capital Budgeting (Project Analysis)

• Increase competitiveness through better budgeting and controlling systems

COURSE OUTLINE

Ribbons Functions and Commands of Excel • The power of Excel for building financial models • The 8 Ribbons of Excel with their commands and

functions

• Using formulae: copying, anchoring and special pasting

• Using functions: financial, statistical and mathematical

• Case Study in Excel: Using a template, the following will be reviewed:

• Excel Help (F1) • Inserting Comments • Freeze Panes • Excel Options • Inserting Hyperlinks • Replace

• Quick Access Toolbar • Sorting and Filtering • Naming Cells • Cell formats • Show Formulae • Hyperlink • Paste Special • Audit Tools

• Anchor and Paste

Financial Statements as the Basis of Planning

• Review of the financial objectives of business: ROI, ROA, ROE

• Overview of financial reports

• Balance Sheet (Statement of Financial Position)

• Income Statement (Statement of Financial Performance)

• Cash Flows (Statement of Cash Flows) • Strategic Key Performance Indicators • Key objectives of budgeting

• Quantitative objectives • Qualitative objectives

• Case Study in Excel: Using financial statements and an Excel template to build a model for financial analysis of past information with a view to forecasting future years

Managing by Exception using Variances

• Setting budgeting objectives and tolerance levels • Operating budgets

• Sales (volumes, and pricing) and other Revenue budgets

• Expense (Opex), distinguishing between Fixed Costs and Variable Costs

• Capital budgets

• Capital expenditure Decisions (Capex: Fixed assets and Working Capital)

• Financing sources (Equity and Debt) • Monthly reporting procedures and timely action • Case Studyin Excel: Building a model to calculate

monthly and year to date variances and linking to monthly reports where tolerance levels have been contravened

Budgeting Processes and Methods

• The budget process: Timing and Cycles

• Strategic tools essential in planning for the budget process • SWOOTT • PESTEL • 5 Forces of Competition • Gap analysis • Budgeting Techniques • Incremental based budgets • Zero Based Budgeting • Activity Based Budgeting • Rolling Budgets

• “Beyond Budgeting” compared to traditional budgeting principles

• Leadership principles

• Performance management principles

• Case Studies in Excel: Building financials models using “What if” analysis

(4)

(ROAM as the primary measure) 2. Rolling and Flexible Budgets for SME

Manufacturing business Forecasting Revenues and Costs

• The three principles of forecasting in order • Ignoring the past

• Due diligence and the view of the “reasonable person”

• Substituting error for chaos

• Necessity to apply a range of different forecasting methods: • Qualitative Models • Market Research • Delphi Method: • Quantitative Models • Econometric Modelling: • Time Series Methods:

• Projecting levels of forecasting: Base, Best and Worst

• Converting annual budgets to monthly budgets considering seasonality

• Case Studies in Excel: Building financial model templates for the Income Statement using forecasting methods and Scenario manager 1. Forecasting Sales and Expenses to EBIT with

an “Assumptions table for retail 2. Convert annual sales unit forecast to

seasonalised monthly budget

Cost Volume and Profit Analysis with Data Tables • Determining the “cost” of a product by selecting a

costing basis • Marginal costing

• Full costing with different overhead allocation bases

• Activity based allocation of overheads • Standard costing

• “Lean” approaches to cost determination • Categories of costs

• Fixed, Variable and Semi-variable

• Impact of fixed and variable costs on volumes and profitability

• Determining the cost per unit, break even point and margin of safety

• Building Data Tables for quick reference to a range of assumptions

• One way data tables • Two way data tables

• Case Study in Excel: Using a financial model to determine break-even and unit cost calculations and apply sensitivity, scenario analysis and data tables for a bottling company

Principles of Capital Budgeting and Project selection • The search for strategic and profitable growth of a

business

• Organic growth

• Mergers and Acquisitions • Grasping compounding and growth

• Future values of a lump sum and annuities • Present Values of expected future lump sums

and/or annuity income

• Methods for selecting from opportunities available • Payback period

• Discounted Cash Flow approaches (NPV, IRR, MIRR)

• Measuring and mitigating forecast risks • Forward contracts

• Option contracts

• Case Study in Excel: Building a cash flow model for project analysis to determine the Payback period, Net present value, Internal rate of return and Modified internal rate of return to make a project investment decision

Approaches in Service, Manufacturing, Retail and Government sectors

• Review

• Distinguishing different approaches depending on the Industry

• Services • Resources • Industrials • Retail

• Discussion with delegates based on their industry and expectations

• Practice in building a suitable model for individual needs.

Course Director

Professor David Flynn

BA(stell) ; B Ed(UCT); B Com (Hons)(Natal); MBA(UCT); NCTD; IMA (SA)

David Flynn is the author and co-author of several texts in the field of financial accounting and financial management. He has served as Head of Department of Accounting at the University of Cape Town, a visiting professor at Rhodes Investec Business School and Windsor University Canada, and as a facilitator and consultant to organizations in a number of centres worldwide as well as in his home country, South Africa.

Apart from seminars in South Africa he travels widely in order to present seminars and has most recently served as a facilitator in southern African countries such as Kenya, Namibia, Mozambique, Tanzania, Zambia and Malawi as well as further afield in Dubai, Kuwait, Kuala Lumpur, Manila, Shanghai and Sao Paulo.

(5)

COMPANY DETAILS Company Name: ... Postal Address: ... Postcode: ... Country: ... Physical Address: ... ... Invoice for Attention Of: ... Tel: ... Fax: ... Email: ... VAT Number: ...

MANAGER AUTHORISATION

Full Name: ... Contact Tel Number: ... Email: ... Signature: ...

COURSE FEES PER DELEGATE

$1 050.00 USD x ... Delegates = $ ...

Please note that payments is required no later than 10 days from invoice date. In the the event of non-payment, Nolwazi Africa reserves the right to cancel the booking and the full amount under disagreement will be due and payable.

BANKING DETAILS

Bank Name: First National Bank Centurion Account Name: Nolwazi Africa Events

Account Number: 6222 5618 422 Branch Code: 261 550 Swift Code: FIRNZAJJ926

CONFIRMATION DETAILS

Upon reception of your booking, we will e-mail each registered person our delegate confirmation pack. this pack will include venue information with a map and specific event details. If you have not received confirmation within one week of booking, kindly contact us on +27 (0)12 653 2522 for assistance. Delegate fees include all workshop materials, lunch and refreshments for each day. Please note that hotel accommodation and travel are not included in the price. We can offer travel and hotel booking assistance if required. Please feel free to contact us, we will gladly assist you.

CANCELLATION & TRANSFER POLICY

Delegates unable to attend the event may send a substitute delegate in their place. Please send written details of substitution. Written cancellations must be received more than 10 working days prior to the date of the event and will be liable for 50% of the event fee. Failure to cancel, or cancellations received 10 working days or less prior to the event date, will result in liability for the full event fee. Nolwazi Africa reserves the right to alter the program and speaker details without notice.

GROUP DISCOUNTS 2 Delegates @ 5% discount 3 Delegates @ 10% discount 5+ Delegates @ 15% discount DELEGATE REGISTRATION Delegate 1: Full Name: ... Designation: ... Contact Tel Number: ... Email: ...

Delegate 2:

Full Name: ... Designation: ... Contact Tel Number: ... Email: ...

Delegate 3:

Delegate 4:

Delegate 5:

Delegate 6:

To register for this event please complete the registration form below and fax it to +27 (0)86 570 8986 or email it to [email protected]. Alternatively please call us on +27 (0)12 653 2522.

REGISTRATION FORM

Budgeting, Forecasting and Cost Management using Excel

10 - 11 April 2014 // Hotel Cardoso, Maputo, Mozambique

(NOL196)

Your Sales Executive: Jaime Thomson

Charles Winton Jaime Thomson Phillip Ngazimbi