EasyFinance

中文

HOME > Public Semina > Course Online

HOW TO PREPARE AND TRACK BUDGET BY EXCEL

Developing an annual budget is an important task each enterprise must accomplish.

And for Financial people, budget development is a trivial, time consuming, burdensome task involving different aspects. We can improve budget development efficiency if Excel?is effectively applied. However, due to lack of expertise in Excel?operation or incomprehension of Excel?functionality, data is re-entered, updating is not consistent and data collection of each department is time consuming.

This course will introduce how to solve above problems during budget development by applying different kinds of features in Excel?such as forms, charts and functions.
Therefore you may pay more attention on the budget itself and analysis of variance between budgeted and actual value and less effort on collecting, checking, updating and calculating data, thus improving the role of the financial department in corporation management.

Training Objectives

Learn budget development in multinational corporations, items included in a budget and how to manage daily operation based on a budget
Learn how to prepare a budget, compare and analyze variance between a budgeted and actual value by using advanced Excel?skills
Participants can obtain Excel?templates for data collection from sales, production and purchasing departments and use this data to prepare income statements, balance sheets and statements of cash flow. All this work will be done automatically by the templates thus improving the efficiency of budget development!
Participants can also obtain report templates for analysis on budget and actual data, which help you spend less time on calculating monthly variances, but instead focus on analyzing them, finding out the cause of variances and methods to eliminate them.

Training Attendees

Financial manager, budgeting developer, financial analyst, cost controller and so on. Require that trainees have some knowledge of content and method of budget development, tracking and control of budgets and basic Excel?skills.


	Training Outline

	Budget development based on Excel
	Basis for budget development
	- Contents of a budget
	- Relationship among Excel files of various budget items
	- Basic stipulation on budget development using Excel
	Sales budget
	- IF and SUMIF functions in a sales budget
	- Relative reference, absolute reference and mixed reference
	- How to show figures in thousands or millions
	Production budget - direct material cost
	- Conditional format in a material price budget
	- How to calculate complicated formula by array
	- Application of TRANSPOSE functions in a material cost budget
	Production budget - direct labor cost
	- How to calculate Hourly Rate and labor efficiency
	- How to budget direct labor demand
	- What are "round"functions
	Labor and salary expense budget
	- What are "look up/matching"functions?
	Fixed assets investment budget
	- DATEDIF and other date functions applied in a fixed assets budget
	Production budget - period expense
	- Improving link efficiency among sheets by using the INDIRECT function
	Balance sheet budget
	- Budgeting of credit term and accounts receivable balance

	- Budgeting of inventory turnover and inventory balance
	- Budgeting of payment term and accounts payable balance
	- Budgeting of other receivables/payables, prepaid/accrued expenses in a balance sheet
	Automatic formation of income statements and statements of cash flow
	Budget tracking based on Excel
	Tracking and variance analysis on sales and gross margin
	- How to calculate different variances between budgeted gross margin and actual gross margin
	- A visual representation of change from budgeted gross margin to actual gross margin by Excel charting
	- Integrated application of text functions in managing data such as product code, customer code and date
	Period expense control
	- How to design Excel forms that are both convenient for users to fill in and easy for financial department to summarize and analyze
	- Sum up expense report of each cost center by Consolidation
	- Compare and analyze actual and budgetary expense of cost center by dynamic charting
	Performing financial statements in fixed format each month
	- How to automatically develop the fixed format financial statements each month by applying the INDEX function
	- Comparison of actual and budget of key financial ratios template (application of J, K, L )