Master Budget

Overview

In this project you will need to prepare a master budget. A master budget is a collection of smaller, more specific operating and financial budgets that are connected to create the master budget.

The master budget is an extremely important tool companies use to plan ahead and then check and evaluate their performance. Knowing how to prepare a budget is a very important business (and life) skill. In addition, the knowledge you gain completing this project would be essential when you are asked to prepare a budget in MBA 715.

Instructions

Make sure to carefully review the information you are provided Download information you are provided

Make sure to follow the instructions included in the file

To make it easier on you, you should use the provided Excel templateDownload Excel template

Please make sure to use Excel formulas to calculate all numbers in the different budget (you will be graded on this)

Do not type numbers into Excel (except for numbers provided in instructions)

Using excel formulas would connect different parts of the master budget, which is a crucial characteristic of a master budget

Amazing Building Company
Master Budget
1. Sales Budget
20X2
4th
Quarter
S truss unit sales
60,000
x S sales price
$
12
S truss sales revenie
$
720,000
L truss unit sales
70,000
x L sales price
$
18
L truss sales revenue
$
1,260,000
Total sales revenue
$
1,980,000
Cash sales* (40% of quarterly sales)
$
792,000
†
Sales on account (60^ of total sales)
1,188,000
*40% of total sales.
60% of total sales.
†
2. Cash receipts budget:
Cash sales
Cash collections from credit sales
made during current quarter * (80% of current quarter’s credit sales)
Cash collections from credit sales
†
made during previous quarter (20% of previous quarter’s credit sales)
Total cash receipts
*80% of current quarter’s credit sales.
20% of previous quarter’s credit sales.
†
3. Production budget:
20X2
4th
Quarter
S truss:
Sales (in units)
60,000
Add: Desired ending inventory
13,000
Total units needed
73,000
Less: Expected beginning inventory
12,000
Units to be produced
61,000
L truss:
Sales (in units)
70,000
Add: Desired ending inventory
15,000
Total units needed
85,000
Less: Expected beginning inventory
14,000
Units to be produced
71,000
4. Direct materials and materials purchase budget
20X2
4th
Quarter
Lumber sheets:
S truss to be produced
61,000
Lumber quantity per unit (crates)
0.25
Needed for S truss prodction
15,250
L truss to be produced
71,000
Lumber quantity per unit (crates)
0.50
Needed for L truss prodction
35,500
Total Lumber needed for production (crates)
50,750
Add: Desired ending inventory
10,900
Total Lumber needs
61,650
Less: Expected beginning inventory
10,150
Lumber to be purchased
51,500
Price per Lumber crate
$
6
Cost of Lumber to be purchased
$
309,000
Raw-material purchases:
Cash payments for purchased during
the current quarter
†
Cash payments for purchased during
the preceding quarter**
Total cash pmts. For raw-material purchases
†
80% of current quarter’s purchases
**20% of previous quarter’s purchases
5. Direct labor budget
Direct labor:
Total trusses produced (S and L)
Direct-labor hours per truss
Direct-labor hours to be used
Rate per direct-labor hour
Total cash payments for direct labor
6. Overhead budget
Production overhead:
Indirect material
Indirect labor
Other
Depreciation
Total overhead
Less: Non-cash depreciation
Total cash payments for production overhead
7. Summary cash budget
Cash balance, beginning of period
Proceeds from bank loan (1/2/23)
Cash receipts
Total cash available
Disbursements:
Direct materials
Direct labor
Overhead
Selling and administration expenses
Dividends
Purchase of equipment
Quarterly installment on loan principal
Quarterly interest payment
Total disbursements
Cash balance, end of period
8. Budgeted scheudule of costs of good manufactured sold for the year 20X3
Amazing Building Company
Budgeted Schedule of Cost of Goods Manufactured and Sold
For the Year Ended December 31, 20X3
Direct material:
Raw-material inventory, 1/1/23
Add: Direct materials purchased
Raw material available for use
Deduct: Raw-material inventory, 12/31/23 (see column G Row 148)
Raw material used
Direct labor
Manufacturing overhead:
Indirect material
Indirect labor
Other overhead
Depreciation
Total manufacturing overhead
Budgeting over/underapplied overhead (NONE)
–
Overhead applied to work-in-progress*
Cost of goods manufactured
Add: Finished-goods inventory, 1/1/23
Cost of goods available for sale
Deduct: Finished-goods inventory, 12/31/23 (see columns E-F Row 160)
Cost of goods sold
9. Budgeted income statement for 20X3
Amazing Building Company
Budgeted Income Statement
For the Year Ended December 31, 20X3
Sales revenue
Less: Cost of goods sold
Gross margin
Selling and administrative expenses
Interest expense
Total expenses
Net income
10. Budgeted statement of retained earnings for 20X3
Amazing Building Company
Budgeted Statement of Retained Earnings
For the Year Ended December 31, 20X3
Retained earnings, 12/31/22
Add: Net income
Deduct: Dividends
Retained earnings, 12/31/23
11. Budgeted balance sheet sa of December 31, 20X3
Amazing Building Company
Budgeted Balance Sheet
December 31, 20X2
Cash
$
Accounts receivable*
95,000
237,600
Inventory:
Raw material
59,200
Finished goods
167,000
Plant and equipment (net of accumulated depreciation)** (see below)
7,856,800
Total assets
$
Accounts payable
$
8,415,600
61,800
Common stock
5,000,000
Retained earnings
3,353,800
Total liabilities and stockholders’ equity
$
8,415,600
$
7,856,800
Plant and equipment (net of accumulated depreciation)** (see below)
Balance at beginning of the year
Plus: Purchases during the year
Less: Depreciation during the year
Balance at end of the year
1,000,000
20X3
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
65,000
75,000
20X3
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
237,600
20X3
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
20X3
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
9,400
9,400
20X3
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
61,800
20X3
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
142,000
0.20
28,400
$
20
$
568,000
20X3
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
1st
2nd
3rd
4th
Entire
Quarter
Quarter
Quarter
Quarter
Year
20X3
$
95,000
1,000,000
–
–
–
1,000,000
1,000,000
–
–
–
1,000,000
$
250,000
250,000
250,000
250,000
1,000,000
25,000
18,750
12,500
6,250
62,500
59,200
Deduct: Raw-material inventory, 12/31/X3
Desired ending inventory
$
59,200
–
9,400
Price per crate
Raw-material inventory, 12/31/23
Deduct: Finished-goods inventory, 12/31/X3
S Truss
L Truss
Desired ending inventory units
–
Production cost per unit $
Total cost
167,000
–
7.50
$
9.00
-
December 31, 20X3
–
MBA 641
Preparing a Master Budget
Background
The Amazing Building Company (ABC) is in the process of completing their annual budget for 20X3.
During November of 20X2, Amanda Brick, President was discussing the company’s master budget with
her staff. Brick updated the group that she has decided to go ahead and purchase the industrial robot they
have been considering. She plans to make the acquisition on January 2 of next year, and expects it will
take most of the year to train the personnel and reorganize the production process to take full advantage of
the new equipment.
When questioned about funding the purchase, Brick replied as follows: “The robot will cost $1,000,000.
We’ll finance it with a one-year $1,000,000 loan from National Savings and Loan. I’ve negotiated a
repayment schedule of four equal installments on the last day of each quarter. The interest rate will be
10%, and interest payments will be quarterly as well.” Therefore, the outstanding balance for all of Q1
would be $1,000,000 with a $250,000 payment due on the last day of Q1, $750,000 for all of Q2 with a
$250,000 payment due on the last day of Q2, etc. and being fully paid off by the end of the year.
ABC is a manufacturer of roof trusses. The firm’s two product lines are designated as S (small trusses)
and L (large trusses). The primary raw material is dimensional lumber. Allowing for normal breakage
and scrap lumber, ABC can get either four S trusses or two L trusses out of a crate of lumber. Other raw
materials, such as nails and glue, are insignificant in cost and are treated as indirect materials. Jane Bean,
ABC’s controller, is in charge of preparing the master budget for 20X3. She has gathered the following
information:
1. Sales in the fourth quarter of 20×2 are expected to be 60,000 S trusses and 70,000 L trusses. The
sales manager predicts that over the next two years, sales in each product line will grow by 5,000
units each quarter over the previous quarter. For example, S truss sales in the first quarter of
20×3 are expected to be 65,000 units.
2. ABC’s sales history indicates that 60 percent of all sales are on credit, with the remainder of the
sales in cash. The company’s collection experience shows that 80 percent of the credit sales are
collected during the quarter in which the sale is made, while the remaining 20 percent is collected
in the following quarter.
3. The S truss sells for $12, and the L truss sells for $18. These prices are expected to hold constant
throughout 20X3.
4. ABC’s production manager attempts to end each quarter with enough finished goods inventory in
each product line to cover 20 percent of the following quarter’s sales. Moreover, an attempt is
made to end each quarter with 20 percent of the crates of lumber needed for the following
quarter’s production, with Q4 20X3 desired ending inventory being 9,400 crates. Since metal
strips are purchased locally, ABC buys them on a just-in-time basis; inventory is negligible.
5. All of ABC’s direct materials purchases are made on account, and 80 percent of each quarter’s
purchases are paid in cash during the same quarter as the purchase. The other 20 percent is paid
in the next quarter.
6. Projected production costs in 20X3 are as follows:
S Truss
Direct Material:
Crate of lumber:
S: 1/4 crate @ $6 per crate
L: 1/2 crate @ $6 per crate
Direct Labor:
.2 hour @ $20 per hour
Production overhead:
.2 direct labor hour x $10 per hour
Total production cost per unit
L Truss
$1.50
$3.00
$4.00
$4.00
$2.00
$7.50
$2.00
$9.00
7. The predetermined overhead rate is $10 per direct labor hour. The following production
overhead costs are budgeted for 20X3.
Indirect materials
Indirect labor
Other overhead
Depreciation
Total overhead
Q1
$ 8,200
38,800
29,000
20,000
$ 96,000
Q2
$ 9,200
42,800
34,000
20,000
$106,000
Q3
$ 10,200
46,800
39,000
20,000
$116,000
Q4
$ 11,200
50,800
44,000
20,000
$126,000
Entire Year
$ 38,800
179,200
146,000
80,000
$444,000
All of these costs will be paid in cash during the quarter incurred except for the depreciation charges.
8. ABC’s quarterly selling and administrative expenses are $125,000, paid in cash.
9. Bean anticipates that dividends of $60,000 will be declared and paid in cash each quarter.
10. ABC’s projected balance sheet as of December 31, 20X2, follows:
Cash
Accounts receivable
Inventory:
Raw material
Finished goods
Plant and equipment (net of accumulated depreciation)
Total Assets
$
Accounts payable
Common stock
Retained earnings
Total liabilities and stockholders’ equity
$
95,000
237,600
59,200
167,000
7,856,800
$8,415,600
61,800
5,000,000
3,353,800
$8,415,600
Required:
Prepare ABC’s master budget for 20X3 by completing the following schedules and statements
using the Excel template provided.
1. Sales budget
2. Cash receipts budget
3. Production budget
4. Direct materials and materials purchase budget
5. Direct labor budget
6. Overhead budget
7. Summary cash budget
8. Budgeted schedule of cost of goods manufactured and sold for the year 20X3
9. Budgeted income statement for 20X3
10. Budgeted statement of retained earnings for 20X3
11. Budgeted balance sheet as of December 31, 20X3.