How to prepare budgeted balance sheet in excel

Master Budgeting Operating

Chapter 8

FAC=GAAP= Financial Reporting

Chapter Focus in Operating Budget NOT Cash Flow/BS – financing, capital items, equity, investments, etc. not covered

PowerPoint Authors:

Susan Coomer Galbreath, Ph.D., CPA

Charles W. Caldwell, D.B.A., CMA

Jon A. Booker, Ph.D., CPA, CIA

Cynthia J. Rooney, Ph.D., CPA

Copyright © 2015 by McGraw-Hill Education. All rights reserved.

8-‹#›

8-‹#›

Learning Objective 1

Why do organizations budget and the processes used to create budgets.

8-‹#›

Learning objective number 1 is to understand why organizations budget and the processes they use to create budgets.

8-‹#›

The Basic Framework of Budgeting

A budget is a detailed quantitative plan for acquiring and allocating financial and other resources over a specified forthcoming time period for defined purpose.

The act of preparing a budget is called budgeting.

The use of budgets to control an organization’s activities is known as budgetary control.

8-‹#›

A budget is a detailed quantitative plan for acquiring and using financial and other resources over a specified forthcoming time period. The act of preparing a budget is called budgeting. The use of budgets to control an organization’s activities is known as budgetary control.

8-‹#›

Planning and Control

Planning – involves developing objectives and preparing various budgets to achieve those objectives.

Control – involves the steps [review, iterate] taken by management to increase the likelihood that the objectives set down while planning is attained and that budgets and objectives for each part of the organization is integrated to achieve objective.

Budget often are the controlling exercise to allocate resources to company objectives>

Essential for Resource Allocation

8-‹#›

Planning involves developing objectives and preparing various budgets to achieve those objectives.

Control involves the steps taken by management to increase the likelihood that the objectives set down at the planning stage are attained and that all parts of the organization are working together toward that goal.

To be effective, a good budgeting system must provide for both planning and control. Good planning without effective control is time wasted.

8-‹#›

Advantages of Budgeting

Advantages

Define goals

and objectives

Uncover potential

encumbrances

including bottlenecks

Coordinate

activities

Communicate

plans

Think about and

plan for the future

Means of allocating

Resources #1

8-‹#›

Budgets communicate management’s plans throughout the organization. Budgets force managers to think about and plan for the future. The budgeting process provides a means of allocating resources to those parts of the organization where they can be used most effectively. The budget process can uncover potential bottlenecks before they occur. Budgets coordinate the activities of the entire organization by integrating the plans of its various parts. Budgets define goals and objectives that can serve as benchmarks for evaluating subsequent performance.

While our focus in this chapter is on preparing operating budgets for a one-year time frame, longer term budgets also can be very helpful to organizations from a planning standpoint.

8-‹#›

Responsibility Accounting [part of MBO]

Managers should be held responsible for those items - and only those items - that they can actually control [or influence] to a significant extent. Responsibility accounting enables organizations to react quickly to deviations from their plans and to learn from feedback.

Responsibility- Learn & Correct

8-‹#›

The premise of responsibility accounting is that managers should be held responsible only for those items that they can control to a significant extent. Responsibility accounting systems enable organizations to react quickly to deviations from their plans and to learn from feedback obtained by comparing budgeted goals to actual results. The point is not to penalize individuals for missing targets.

8-‹#›

Choosing the Budget Period

Operating Budget

2xx1

2xx2

2xx3

2xx4

Operating budgets ordinarily

cover a one - fiscal year

corresponding to a company’s fiscal year. Many companies divide their annual budget

into four quarters or twelve months.

A continuous budget is a 12-month budget that rolls forward one month (or quarter) as the current month (or quarter) is completed.

As a practical matter for a Manager; income may be tied to performance to Annual Budget including objectives

8-‹#›

Operating budgets ordinarily cover a one-year period corresponding to a company’s fiscal year. Many companies divide their annual budget into four quarters. In this chapter, we focus on one-year operating budgets.

A continuous or perpetual budget is a 12-month budget that rolls forward one month (or quarter) as the current month (or quarter) is completed. This approach keeps managers focused on the future at least one year ahead.

8-‹#›

Self-Imposed Budget

A self-imposed budget or participative budget is a budget that is prepared with the cooperation and participation of managers at the level for which decision will be made at all levels.

8-‹#›

A self-imposed budget or participative budget is a budget that is prepared with the full cooperation and participation of managers at all levels. It is a particularly useful approach if the budget will be used to evaluate managerial performance.

8-‹#›

Top Side Guidance

Certain Strategic & Operating objectives may be established and provided to line/functional Management as OBJECTIVES FOR THE BUDGET PERIOD which are to be factored into the Budget Process:

E.g., Market share increase 3%

Sales $ up 6%

Defect level from 0.7% to 0.2%

Productivity increase of 2.5%

Product Launch on July 1

Energy Consumption reduction by 6%

Customer satisfaction improvement

Others

8-‹#›

Advantages of Self-Imposed Budgets

Individuals at all levels of the organization are viewed as members of the team whose judgments are reviewed/valued by top management.

Budget estimates prepared by front-line managers may be more accurate than estimates prepared by top managers.

Motivation may be higher when individuals participate in setting their own goals than when the goals are imposed from above.

A manager who is not able to meet a budget imposed from above can claim that it was unrealistic. Self-imposed budgets eliminate this excuse.

8-‹#›

The key to self-imposed budgets is to get operational managers involved in the budgeting process and to clearly state their goals and expectations. Here is a list of four major advantages of self-imposed budgets.

First, individuals at all levels of the organization are viewed as members of the team whose judgments are valued by top management.

Second, budget estimates prepared by front-line managers (who have intimate knowledge of day-to-day operations) are often more accurate than estimates prepared by top managers.

Third, motivation is generally higher when individuals participate in setting their own goals than when the goals are imposed from above.

Fourth, a manager who is not able to meet a budget imposed from above can claim that it was unrealistic. Self-imposed budgets eliminate this excuse.

8-‹#›

Self-Imposed Budgets

Self-imposed budgets should be reviewed by higher levels of management to prevent “budgetary slack”.

Most companies issue broad guidelines in terms of overall profits or sales. Lower level managers are directed to prepare budgets that meet those targets.

Bottoms-up budgets [self – imposed] are ALWAYS reviewed and often require post review iteration. Many objectives are driven throughout the organization. Profit [S], market share [S], cash flow, CapX, manning levels, pay scales, efficiency …

8-‹#›

Self-imposed budgets should be reviewed by higher levels of management. Without such a review, self-imposed budgets may have too much “budgetary slack,” or may not be aligned with overall strategic objectives. Most companies do not rely exclusively upon self-imposed budgets in the sense that top managers usually initiate the budget process by issuing broad guidelines in terms of overall target profits or sales. Lower level managers are directed to prepare budgets that meet those targets.

8-‹#›

Human Factors in Budgeting

The success of a budget depends on three important factors:

Top management must be enthusiastic and committed to the budget process [Drive the Process].

Top management may use the budget to drive performance must take care recognize that non-achievement of goal may be due to factors beyond manger control.

Highly achievable budget targets are usually preferred [ART is take out slack] when managers are rewarded based on meeting budget targets.

8-‹#›

The success of a budget program depends upon three important factors:

Top management must be enthusiastic and committed to the budget process, otherwise nobody will take it seriously.

Top management must not use the budget to pressure employees or blame them when something goes wrong. This breeds hostility and mistrust rather than cooperative and coordinated efforts.

Highly achievable budget targets are usually preferred (rather than “stretch budget” targets) when managers are rewarded based on meeting budget targets.

8-‹#›

Budget with attendant Objectives must have a degree of task built in. Effort is to be required for attainment of Budget with attendant Objectives Economic incentive often associated with budget attainment

8-‹#›

The Master Budget: An Overview: 10 items

Production budget

Period: Selling and

administrative

budget

Direct Materials

budget

Manufacturing

Overhead budget

Direct Labor budget

Cash Budget

Sales budget:

PRIME DRIVER

Ending Inventory

budget

Budgeted Balance Sheet

Budgeted Income Statement

1

2

3

5

6

7

8

9

10

4

CoGS compiled from 1, 2,3,5,6,7

8-‹#›

The master budget consists of a number of separate but interdependent budgets. We have developed this schematic of the budgeting process to illustrate the interdependency of the various individual budgets. The sales budget shows the expected sales for the budget period expressed in dollars and units. It is usually based on a company’s sales forecast. All other parts of the master budget are dependent on the sales budget.

The production budget is prepared after the sales budget. It lists the number of units that must be produced during each budget period to meet sales needs and to provide for the desired ending inventory. The production budget in turn directly influences the direct materials, direct labor, and manufacturing overhead budgets, which in turn enable the preparation of the ending finished goods inventory budget. These budgets are then combined with data from the sales budget and the selling and administrative expense budget to determine the cash budget. The cash budget is a detailed plan showing how cash resources will be acquired and used over a specified time period. All of the operating budgets have an impact on the cash budget.

The last step of the process is to prepare a budgeted income statement and a budgeted balance sheet.

8-‹#›

Seeing the Big Picture

To help you see the “big picture” keep in mind that the 10 schedules in the master budget are designed to answer the 10 questions shown on the next screen.

8-‹#›

Seeing the Big Picture

How much sales revenue will we earn?

How much cash will we collect from customers?

How much raw material will we need to purchase?

How much manufacturing costs will we incur?

How much cash will we pay to our suppliers [materials resource needed] and our direct labor [labor resource needed], and how much cash will we pay for manufacturing overhead?

What is the total cost that will be transferred from finished goods inventory to cost of good sold [Inventory levels/Order fulfillment & CoGS]?

How much selling and administrative expense will we incur and how much cash will be pay related to those expenses?

How much money will we borrow from or repay to lenders – including interest [change in Cash position] ?.

How much operating income will we earn?

What will our balance sheet look like at the end of the budget period?

8-‹#›

The Master Budget: An Overview

A master budget is based on various estimates and assumptions. For example, the sales budget requires three estimates/assumptions as follows:

What in Market share [strategic] & price [strategic] then what are the budgeted unit sales?

What is the budgeted selling price per unit?

What percentage of accounts receivable will be collected in the period[s] being budgeted and subsequent periods.

8-‹#›

Learning Objective 2

Prepare a sales budget, including cash.

8-‹#›

Learning objective number 2 is to prepare a sales budget, including a schedule of expected cash collections.

8-‹#›

Budgeting Example

Excel A

In April Of the $175,000 A/R from March 31 25%/30% will be collected

1

8-‹#›

The marketing department of Royal Company prepares the following information that will be used to prepare a budget for the quarter ending June 30th.

8-‹#›

Learning Objective 3

Prepare a production budget.

8-‹#›

Learning objective number 3 is to prepare a production budget.

8-‹#›

The Production Budget

Production

Budget

Sales

Budget and Expected Cash Collections

Completed

The production budget must be

meet budgeted sales,

customer service levels [explain] and

target ending inventory.

8-‹#›

After we have budgeted our sales and expected cash collection, we must make sure the production budget is adequate to meet the forecasted sales and to provide for the desired ending inventory. We need inventory on hand at the end of the period to minimize the likelihood of an inventory stock-out.

8-‹#›

The Production Budget: Production Qty.

The management at Royal Company wants ending inventory to be equal to 20%* of the following month’s budgeted sales in units [Inventory Level].

On March 31st, 4,000 units were on hand.

Let’s prepare the production budget.

* this meets customer service level of 98% order shipment in 2 days

Excel B

2

3

8-‹#›

The management at Royal wants to minimize the probability of a stock out of inventory items. A policy has been implemented that requires the company to maintain ending inventory of 20 percent of the following month’s budgeted sales. At the beginning of the quarter, Royal had 4,000 units in inventory. If Royal was a merchandising company it would prepare a merchandise purchases budget instead of a production budget. Let’s get started on the production budget.

8-‹#›

Learning Objective 4

Prepare a direct materials budget, including cash disbursements for materials.

8-‹#›

Learning objective number 4 is to prepare a direct materials budget, including a schedule of expected cash disbursements for purchases of materials.

8-‹#›

The Direct Materials Budget

At Royal Company, five pounds of material are required per unit of product.

Management wants materials on hand at the end of each month equal to 10% of the following month’s production.

On March 31, 13,000 pounds of material are on hand. Material cost is $0.40 per pound.

Let’s prepare the direct materials budget.

Excel C

5

8-‹#›

Each good unit of output requires 5 pounds of direct material. Management does not want to run out of direct materials, so a policy has been established that materials on hand at the end of each month must be equal to 10% of the following month’s production. At the beginning of the month, Royal has 13,000 pounds of direct material on hand. Each pound of direct material costs $0.40. Let’s complete the direct materials budget.

8-‹#›

Expected Cash Disbursement for Materials

Royal pays $0.40 per pound for its materials.

One-half of a month’s purchases is paid for in the month of purchase; the other half is paid in the following month.

The March 31 accounts payable balance relating to materials is $12,000.

Let’s calculate expected cash disbursements.

Excel C

5

8-‹#›

Recall that Royal pays $0.40 per pound of direct materials. The company pays for one-half of its purchases in the month of the purchase and one-half in the following month. At the beginning of the quarter, Royal owed creditors $12,000 for purchases of direct materials. Let’s begin the expected cash disbursement for direct materials schedule.

8-‹#›

Learning Objective 5

Prepare a direct labor budget.

8-‹#›

Learning objective number 5 is to prepare a direct labor budget.

8-‹#›

The Direct Labor Budget

At Royal, each unit of product requires 0.05 hours (3 minutes) of direct labor.

The Company has a “no layoff” policy so all employees will be paid for 40 hours of work each week.

For purposes of our illustration assume that Royal has a “no layoff” policy, workers are paid at the rate of $10 per hour regardless of the hours worked.

For the next three months, the direct labor workforce will be paid for a minimum of 1,500 hours per month.

Let’s prepare the direct labor budget.

Excel D

6

8-‹#›

Carefully review the information on the screen. A unique aspect of direct labor at Royal is the no overtime policy. The company agrees to no layoffs of employees if work is slow, but in return, pays its employees straight time at $10 per hour for all hours worked. With the current work force, Royal will have to pay for a minimum of 1,500 hours of direct labor regardless of the work available. Let’s prepare this budget.

8-‹#›

Quick Check 

What would be the total direct labor cost for the quarter if the company follows its no lay-off policy, but pays $15 (time-and-a-half) for every hour worked in excess of 1,500 hours in a month?

a. $45000

b. $67500

c. $45700

d. $62000

8-‹#›

Determine the total direct labor costs if Royal were to pay time-and-one-half for all hours in excess of 1,500 hours per month.

8-‹#›

Learning Objective 6

Prepare a manufacturing overhead budget.

8-‹#›

Learning objective number 6 is to prepare a manufacturing overhead budget.

8-‹#›

Manufacturing Overhead Budget

At Royal, manufacturing overhead is applied to units of product on the basis of direct labor hours.

The variable manufacturing overhead rate is $20 per direct labor hour.

Fixed manufacturing overhead is $50,000 per month, which includes $20,000 of noncash costs (primarily depreciation of plant assets).

Let’s prepare the manufacturing overhead budget.

Excel E

7

8-‹#›

Royal applies overhead on the basis of direct labor hours. The variable manufacturing overhead rate is $20 per direct labor hour. The fixed manufacturing overhead is $50,000 per month, of which $25,000 is noncash costs, primarily depreciation on the factory assets. This budget will provide a schedule of all costs of production other than direct materials and direct labor.

8-‹#›

Ending Finished Goods Inventory Budget

Excel F

8-‹#›

Royal can now complete the ending finished goods inventory budget. The first step in preparing this budget is to compute direct materials cost per unit. We know that each unit requires 5 pounds of direct material at $0.40 per pound, for a total of $2.00 per unit. The information needed can be derived by referring back to the direct materials budget.

8-‹#›

Learning Objective 7

Prepare a selling and administrative expense budget.

8-‹#›

Learning objective number 7 is to prepare a selling and administrative expense budget.

8-‹#›

Selling and Administrative Expense Budget

At Royal, the selling and administrative expense budget is divided into variable and fixed components.

The variable selling and administrative expenses are $0.50 per unit sold.

Fixed selling and administrative expenses are $70,000 per month.

The fixed selling and administrative expenses include $10,000 in costs – primarily depreciation – that are not cash outflows of the current month.

Let’s prepare the company’s selling and administrative expense budget.

Excel G

4

8-‹#›

Royal has a variable and fixed component to its selling and administrative (S & A) expenses. The company estimates variable selling and administrative expenses at $0.50 per unit sold. Fixed selling and administrative expenses are estimated at $70,000 per month. Of this amount, $10,000 are noncash expenses, primarily depreciation. The selling and administrative expense budget will be prepared in a manner similar to our overhead budget. This budget lists the budgeted expenses for areas other than manufacturing and it is typically a compilation of many smaller individual budgets.

8-‹#›

Learning Objective 8

Prepare a cash budget.

8-‹#›

Learning objective number 8 is to prepare a cash budget.

8-‹#›

Format of the Cash Budget

The cash budget is divided into four sections:

The Direct Method used in the Example tries to conform to the company’s cash receipts and cash disbursement – simplified approach

The Indirect Method commonly used in larger company’s uses the Cash Flow Statement approach of analyzing Balance Sheet differences and certain specific transaction [outside scope of Ch. 8] and categorize into three categories of Operating, Financing, and Investing; ACC310 covers Cash Flow Statements

Excel H

8

8-‹#›

The preparation of the cash budget can be quite complex. This budget should be broken down into time periods that are as short as feasible. It consists of four major sections:

Cash receipts section lists all cash inflows excluding cash received from financing;

Cash disbursements section consists of all cash payments excluding repayments of principal and interest;

Cash excess or deficiency section determines if the company will need to borrow money or if it will be able to repay funds previously borrowed; and

Financing section details the borrowings and repayments projected to take place during the budget period.

8-‹#›

The Budgeted Income Statement

Cash

Budget

Budgeted

Income

Statement

Completed

With interest expense from the cash budget, Royal can prepare the budgeted income statement.

9

8-‹#›

We are now ready to move from the preparation of individual budgets to preparing our budgeted financial statements. The cash budget must be prepared first so that the interest expense can be determined for the budgeted income statement. Let’s begin with the budgeted income statement.

8-‹#›

Learning Objective 9

Prepare a budgeted income statement.

Excel I

8-‹#›

Learning objective number 9 is to prepare a budgeted income statement.

8-‹#›

Other elements of Budget cycle not covered in this chapter.

Accrued expenses, Capital budget, Equity, Long term Debt, …

8-‹#›

Learning objective number 10 is to prepare a budgeted balance sheet.

8-‹#›

Learning Objective 10

Prepare a budgeted balance sheet.

8-‹#›

Learning objective number 10 is to prepare a budgeted balance sheet.

8-‹#›

The Budgeted Balance Sheet

Royal reported the following account balances prior to preparing its budgeted financial statements:

Land - $50,000

Common stock - $200,000

Retained earnings - $86575 (April 1)

Equipment - $125,000

Excel J

10

8-‹#›

Please make note of this supplemental information as we will need it to complete the budgeted balance sheet.

8-‹#›

Alternative /method to Budget Inventory & Accounts Receivable is to Budget from performance indicator such as Inventory Turns & A/R days or turns…

8-‹#›

Learning objective number 10 is to prepare a budgeted balance sheet.

8-‹#›

End of Chapter 8

8-‹#›

End of Chapter 8.

8-‹#›

Supervisor

Supervisor

Middle

Management

Supervisor

Supervisor

Middle

Management

Top Management