Palani Murugappan - Effective Budgeting Using Microsoft Excel

PREFACE

Hello once again!

Welcome to Effective Budgeting using Microsoft Excel.

Many of you have heard of the word budget. However, do you really know what it means and how you can apply it to your advantage? Do you understand the basics of budgeting, forecasting, etc? How do you apply some of the characteristics of budgeting to your work? If you could not quite answer these questions, then this book is definitely for you.

This book was written to help any users wanting to have a clear understanding of how Excel can help to perform some aspects of budgeting using some of its built-in financial and logical functions. It starts off by explaining the concepts of budget and budgeting, moving on to variances in budgets, budgeting process, why budgets fail, limitations and characteristics of budgeting, cash budget, static budget, flexible budget and finally, how to prepare a master budget.

Many books have been written on budgets but this book focuses on the use of Excel in budgeting. It explains the more important facts on master budgets and flexible budgeting. Anyone new to budgets or budgeting will find this book handy.

So, why wait? Turn that PC on and start doing some budgeting exercises!

Palani Murugappan

Email : palani12@yahoo.com

What is budget and budgeting?

Many of us are often confused with the various terminologies that uses the keyword budget. To begin with, budgets are forecasts of future revenues and expenses. Budgeting is the process of gathering information to assist in making these forecasts.

Budgets are often used for planning and control within an organization. Budgeting is all about planning for and managing the business operations and its cash flow. It is often used as a tool to fulfill future goals set by management based on relevant financial and non-financial factors.

For many shoppers, most will have a budget as to how much they will spend on a shopping spree. For example, a trip down the nearest shopping mall and passing by a shop with new designer clothes often attracts the eye of women. They then decide if they want to purchase the designer outfit by looking at the tab. If it is within their budget, they will decide to purchase it. Otherwise, they may have to save further and come back another day to purchase it (assuming they do not swipe their credit card to make the payment!).

In any organization, the use of budgets provide all employees and the management a control feature to ensure that anything financial is adhered to according to a plan, which is coordinated well by the respective heads of departments or leaders of groups.

Some of the disadvantages of applying a budget includes (but not limited to) increased paper work (i.e. leaders need to sit and work out many months ahead of what future expenses or revenue will be in the next financial year); time consuming (i.e. if budgets are not forecasted appropriately and on time); inflexible (i.e. due to unforeseen circumstances, budgets may not be appropriate at a point in time if a certain degree of allowance is not catered for); and meeting with resistance (i.e. if management decides to slash some of the overtime activities, employees may not act favorably to this).

Difference between budgeting and forecasting

Very often, the terms budgeting and forecasting are used interchangeably. However, there is a difference between the two.

Forecasting involves estimating future events and analyzing their implications on the budget. It is an integral part of budgeting.

Budgeting on the other hand, involves preparing budgets and using them as the basis for control and planning. A budget is somewhat a financial plan.

To go a step further, within an organization, forecasting may be done for various purposes such as future business conditions, identifying future opportunities, etc. These estimates are not included in the budgeting process.

In a snapshot, budgeting looks into the development, management and the analysis of an organization.

Factors influencing budgets

Some of the factors that can influence budgets are as follows:

Inflation be prepared should an inflation (or recession) takes place.

Exchange rate there is no guarantee that the exchange rate will remain the same throughout the budgeting period.

Creditors if there is a cash flow problem, can the company still pay its creditors on time?

Taxation assume tax rate will remain the same.

Interest rate in the event the base, overdraft and lending rates increases, will this have an impact on the budget?

Wages if the organization performs well, how many percent of salary increase will be declared across the board?

Why are budgets important to managers?

In the preparation of budgets, managers (or heads of departments) are meant to think and be prepared for the changing environment. In this manner, budgets are used as aids in planning, communicating, setting standards of performance, motivating personnel towards individual and departmental goals, measuring results, and directing attention to problem areas that require investigation.

Budgets are used to transfer information to individual decision makers in the organization. For example, in the bottom-up budgeting, each of the departmental budgets are compiled to estimate how much of cash is needed by the organization for a given period.

In top-down budgeting, the CEO or Managing Director will decide on how much cash each department will get.

More often than not, budgeted amounts can be used as goals to motivate employees. For example, the insurance manager informs his team that if they manage to collect a premium of $1 million collectively, an incentive of an overseas trip to the team will be rewarded. In this way, the manager will be motivating his team to ensure that they do meet their target in order for him to fulfill his budget of $1 million.

Variances in budgets

In most budgets, they are bound to be some variations. For example, if you had budgeted a sales figure of $1 million for the next 12 months but only end up with $800,000, then you had over-budgeted (also known as unfavorable) as you could not attain the original budgeted figure.

However, if you had budgeted a sales figure of $1 million and actually achieved a sales of $1.5 million, then you had under-budgeted (also known as favorable) as the actual sales is far more than the budgeted sales for the given time frame.

The following exercise will help you understand this better. It compares the budgeted sales figures against actual sales. If the budgeted figures are greater than the actual figures, the difference is placed in the Unfavorable column, otherwise it is placed in the Favorable column.

Note that in the Unfavorable column, the difference (or variance) is denoted as a positive value only and not as negative.

Now if you take the total of both the Unfavorable and Favorable columns, you would get the following values i.e. total for Unfavorable is $31,000 and Favorable is $7,000 . The variance (difference) between these two values if $24,000 which is unfavorable (unfavorable outweighs favorable).