Unformatted Attachment Preview
1. Sales Budget
July
Budgeted sales in units
August
12.000
September
14.000
Total
14.500
October
40.500
15.000
x Price per unit
$
35,00
$
35,00
$
35,00
$
35,00
$
35,00
= Projected sales revenue
$
420.000
$
490.000
$
507.500
$
1.417.500
$
525.000
2. Production Budget
July
Budgeted product sales in units
+ Desired product units in ending inventory
= Total product units needed
- Product units in beginning inventory
= Product units to produce
August
September
Total
12.000
14.000
14.500
40.500
3.500
3.625
3.750
10.875
15.500
17.625
18.250
51.375
3.000
3.500
3.625
10.125
12.500
14.125
14.625
41.250
3. Material Purchases Budget
July
Units to produce
August
12.500
x Material needed per unit
= Material needed for units to produce
+ Desired units of material in ending inventory
= Total units of material needed
- Units of material in beginning inventory
= Units of material to purchase
September
14.125
Total
14.625
41.250
2
2
2
2
25.000
28.250
29.250
82.500
1.413
1.463
3.025
5.900
26.413
29.713
32.275
88.400
2.500
1.413
1.463
5.375
23.913
28.300
30.813
83.025
4. Cash Payments for Material Purchases
July
Material purchases (sheets)
August
23.913
September
28.300
Total
30.813
83.025
x Cost per unit
$
3,00
$
3,00
$
3,00
$
3,00
= Total cost
$
71.739
$
84.900
$
92.439
$
249.078
Payables from June
$
43.200
$
43.200
July purchases
$
30.740
August purchases
$
40.998,84
$
35.531
$
76.529
September purchases
Total payments in month
$
73.940
$
71.739
$
48.520
$
84.051
$
39.610
$
39.610
$
52.829
$
88.130
$
238.600
$
52.829
Accounts Payable
5. Cash Payments for Direct Labor
July
Units to produce
August
12.500
x Hours per unit
14.125
2
= Total hours required
September
14.625
2
25.000
Total
41.250
2
28.250
2
29.250
82.500
x Wage rate per hour
$
7,00
$
7,00
$
7,00
$
7,00
= Direct labor cost
$
175.000
$
197.750
$
204.750
$
577.500
6. Cash Payments for Manufacturing Overhead
July
Units to produce
August
12.500
September
14.125
Total
14.625
41.250
x Variable overhead rate
$
2,00
$
2,00
$
2,00
$
2,00
= Variable overhead cost
$
25.000
$
28.250
$
29.250
$
82.500
+ Fixed overhead
$
25.000
$
25.000
$
25.000
$
75.000
= Total manufacturing overhead cost
$
50.000
$
53.250
$
54.250
$
157.500
- Deduct depreciation
$
5.000
$
5.000
$
5.000
$
15.000
= Manufacturing overhead - cash
$
45.000
$
48.250
$
49.250
$
142.500
7. Cash Payments for S&A Expenses
July
Budgeted unit sales
August
12.000
September
14.000
Total
14.500
40.500
x Variable S&A per unit
$
2,50
$
2,50
$
2,50
$
2,50
= Variable S&A expense
$
30.000
$
35.000
$
36.250
$
101.250
+ Fixed S&A expense
$
30.000
$
30.000
$
30.000
$
90.000
= Total S&A expense
$
60.000
$
65.000
$
66.250
$
191.250
- Deduct depreciation
$
2.000
$
2.000
$
2.000
$
6.000
= S&A expense - cash
$
58.000
$
63.000
$
64.250
$
185.250
8. Cash Receipts Budget
July
Budgeted sales in units
x Price per unit
= Projected sales revenue
August
September
12.000
14.000
14.500
40.500
35
35
35
35
420.000
490.000
507.500
1.417.500
Receipts from June sales
$
175.000
Receipts from July sales
$
210.000
Receipts from August sales
$
385.000
$
175.000
126.000
$
84.000
$
420.000
$
-
$
245.000
$
147.000
$
392.000
$
98.000
$
253.750
$
253.750
$
253.750
$
371.000
$
484.750
$
1.240.750
$
351.750
9. Comprehensive Cash Budget
July
August
September
Total
Beginning cash balance
$
125.000
$
93.060
$
20.000
$
125.000
Cash receipts
$
385.000
$
371.000
$
484.750
$
1.240.750
Cash available
$
510.000
$
464.060
$
504.750
$
1.365.750
Materials budget
$
73.940
$
76.529
$
88.130
$
238.600
Labor budget
$
175.000
$
197.750
$
204.750
$
577.500
Manufacturing OH budget
$
45.000
$
48.250
$
49.250
$
142.500
S&A expense budget
$
58.000
$
63.000
$
64.250
$
185.250
Equipment purchases
$
65.000
$
50.000
$
115.000
$
60.000
$
60.000
Cash payments:
Dividends
Total cash payments
$
416.940
$
445.529
$
456.380
$
1.318.850
Balance before financing
$
93.060
$
18.530
$
48.370
$
46.900
$
1.470
$
1.470
Borrowing
Principal repayment
$
1.470
$
1.470
$
46.900
$
46.900
Interest***
Ending cash balance
$
93.060
Accounts Receivable
$
Receipts from September sales
Total cash receipts
Total
$
20.000
10. Budgeted Income Statement
Johnson Inc.
Budgeted Income Statement
For the Three Months Ended September 30, 2017
Sales
$
1.417.500
Cost of goods sold
$
978.000
Gross Margin
$
439.500
Selling and admin expenses
$
191.250
Operating income
$
248.250
Interest expense
$
Net income
$
248.250
**Cost of Goods Sold (Unit Cost) Computation
Production costs per unit
Quantity
Cost
Per Unit Cost
Direct materials
40.500
$
243.000,00
$
6,00
Direct labor
40.500
$
577.500,00
$
14,26
Manufacturing overhead (see calc below)
40.500
$
157.500,00
$
3,89
$
24,15
$
1,91
Manufacturing OH per unit calculation
From Labor and Mfg. OH Budgets
Labor hours
Mfg. OH
July
25.000
$
50.000
August
28.250
$
53.250
September
29.250
$
54.250
Total
82.500
$
157.500
Total manufacturing overhead for quarter
Total labor hours required
= $
157.500
82.500
11. Budgeted Balance Sheet
Johnson Inc.
Budgeted Balance Sheet
September 30, 2017
Current assets:
Cash
$
46.900
Accounts receivable
$
351.750
Raw materials inventory
$
9.075
Finished goods inventory
$
90.556
$
498.280
0
$
10.000
Building (net of depreciation)
$
94.000
Equipment (net of depreciation)
$
265.000
Total property and equipment
$
369.000
$
867.280
Accounts payable
$
52.829
Common stock
$
200.000
Retained earnings
$
518.340
$
771.169
Total current assets
Property and Equipment
Total assets
Liabilities and Equities:
Total liabilities and equities
1. Sales Budget:
Johnson Inc. sells high-tech staplers. Johnson is preparing budgets for the quarter ending September 30,
2017. The sales price is $35 per stapler. Johnson expects the following number of units to be sold in the
coming year:
July
August
September
October
12,000
14,000
14,500
15,000
Required:
Prepare a sales budget for the third quarter of the coming year, showing units and sales revenue by month
and in total for the quarter.
Check Figure: Total projected sales revenue for the quarter - $1,417,500
2. Production Budget:
Johnson wants ending inventory to be 25 percent of the next month’s budgeted sales in units. 3,000 units
were on hand June 30.
Required:
Prepare a production budget for the third quarter of the year. Show the number of staplers that should be
produced each month as well as for the quarter in total.
Check Figure: Total budgeted product units to produce for the quarter – 41,250
3. Materials Purchases Budget:
Two sheets of metal are required to produce a single stapler. Johnson wants to have materials on hand at
the end of each month equal to 10 percent of the following month’s production needs. The materials
inventory on June 30 is 2,500 sheets of metal. October production is budgeted for 15,125 units.
Required:
Prepare a direct materials purchases budget for metal sheets for the quarter ending September 30. Show
how many sheets of metal should be purchased each month as well as for the quarter in total.
Check Figure: Total budgeted units of material to purchase for the quarter – 83,025
1
4. Cash Payments of Materials Purchases:
Johnson Inc. purchases raw materials on account for use in production. Sheets of metal used in production
cost $3.00 each. Forty percent of a month’s purchases are paid for in the month of purchase; the other 60
percent is paid for in the following month. No discount terms are available. The accounts payable balance
on June 30 is $43,200.
Required:
Prepare a cash payment for materials purchases budget and highlight your answer for the following
questions:
1.
2.
3.
4.
How much cash is required for payments made on account, payable in the month of July?
How much cash is required for payments made on account, payable in the month of August?
How much cash is required for payments made on account, payable in the month of September?
What is the expected accounts payable balance on September 30, 2017?
Check Figure: Total budgeted cash payments made for materials purchases for the quarter - $239,445
5. Cash Payments for Direct Labor:
Each unit produced requires 120 minutes (2.0 hours) of direct labor. Employees are paid at a rate of $7.00
per hour.
Required:
Prepare a cash payments for direct labor budget, showing total labor cost by month and in total for the
quarter.
Check Figure: Total budgeted cash payments made for direct labor for the quarter - $577,500
6. Cash Payments for Manufacturing Overhead:
Variable manufacturing overhead is $2.00 per unit produced and fixed manufacturing overhead is $25,000
per month. Fixed manufacturing overhead includes $5,000 in depreciation which does not require a cash
outflow.
Required:
Prepare a cash payments for manufacturing overhead budget, showing total manufacturing overhead cost
by month and in total for the quarter.
Check Figure: Total budgeted cash payments made for manufacturing overhead for the quarter $142,500
2
7. Cash Payments for Selling and Administrative (S&A) Expenses Budget:
Variable selling and administrative expenses are $2.50 per unit sold and fixed selling and administrative
expenses are $30,000 per month. Fixed selling and administrative expenses include $2,000 in depreciation
which does not require a cash outflow.
Required:
Prepare a cash payments for S&A expenses budget, showing total S&A expense by month and in total for
the quarter.
Check Figure: Total budgeted cash payments made for S&A expenses for the quarter - $185,250
8. Cash Receipts Budget:
All sales are on account. Johnson’s collection pattern is:
•
50 percent collected in month of sale
•
30 percent collected in month after sale
•
20 percent collected in the second month after sale
June sales were $350,000. Accounts receivable on June 30 is $175,000, all of which is collectible.
Required:
Prepare a cash receipts budget (schedule of cash receipts) and highlight your answer for the following
questions:
1.
2.
3.
4.
How much total cash is Johnson Inc. expecting to receive in the month of July?
How much total cash is Johnson Inc. expecting to receive in the month of August?
How much total cash is Johnson Inc. expecting to receive in the month of September?
What is the expected accounts receivable balance on September 30, 2017?
Check Figure: Total budgeted cash receipts for the quarter - $1,240,750
3
9. Comprehensive Cash Budget:
Johnson Inc. – Additional Information:
–
–
–
–
–
–
Pays a $60,000 cash dividend in August.
Purchases equipment costing $65,000 in July and $50,000 in September.
Has a $125,000 cash balance on June 30.
Maintains a $20,000 minimum cash balance.
Principal repayments occur at the end of each quarter.
Pays annual interest at 10 percent when a principal payment is made.
Required:
Prepare a comprehensive cash budget by month and in total for the quarter.
Check Figure: Ending cash balance for the quarter - $46,020
10. Budgeted Income Statement:
Johnson Inc. applies manufacturing overhead on the basis of direct labor hours.
Required:
Prepare a budgeted income statement.
Check Figures: PDOHR - $1.91 (cut off trailing decimals in your spreadsheet); Net Income - $261,505
11. Budgeted Balance Sheet
Johnson Inc. reports the following account balances on September 30 prior to preparing its budgeted
financial statements: Land - $10,000; Building (net) - $100,000; Common stock - $200,000; Equipment
(net) - $165,000; Retained earnings - $316,835.
Required:
Prepare a budgeted balance sheet.
Check Figure: Total Assets - $771,170
4