Question

DATA: The East Division of Kensic Company manufactures a vital component that is used in one of Kensic’s major product lines. The East Division has been experiencing some difficulty in coordinating activities between its various departments, which has resulted in some shortages of the component at critical times. To overcome the shortages, the manager of East Division has decided to initiate a monthly budgeting system that is integrated between departments.

The first budget is to be for the second quarter of the current year (April, May and June). To assist in developing the budget figures, the divisional controller has accumulated the following information.

Sales: Sales through the first three months of the current year were 30,000 units. Actual sales in units for January, February, and March, and planned sales in units over the next five months, are given below:

January (actual) 6,000

February (actual) 10,000

March (actual) 14,000

April (planned) 20,000

May (planned) 34,000

June (planned) 51,000

July (planned) 45,000

August (planned) 30,000

In total, the East Division expects to produce and sell 250,000 units during the current year.

Direct Material: Two different materials are used in production of the component. Data regarding these materials are given below:

MATERIAL 208:

Materials per Finished Component: 4 pounds

Cost per lb: $5.00

Inventory at March 31: 46,000 pounds

MATERIAL 311:

Materials per Finished Component: 9 feet

Cost per foot: $2.00

Inventory at March 31: 69,000 feet

Material No. 208 is sometimes in short supply. Therefore, the East Division requires that enough of the material be on hand at the end of each month to provide for 50% of the following month’s production needs. Material No. 311 is easier to get, so only one-third of the following month’s production needs must be on hand at the end of each month.

Direct Labor:   The East Division has three departments through which the components must past before they are completed. Information relating to direct labor in these departments is given below:

SHAPING DEPARTMENT:

Direct Labor-Hours per Finished Component:   .25

Cost per Direct Labor hour: $18.00

ASSEMBLY DEPARTMENT:

Direct Labor-Hours per Finished Component: .70

Cost per Direct Labor hour: $16.00

FINISHING DEPARMENT:

Direct Labor-Hours per Finished Component:   .10

Cost per Direct Labor Hour: $20.00

Direct labor is adjusted to the workload each month.

Manufacturing Overhead: East Division manufactured 32,000 components during the first three months of the current year. The actual variable overhead costs incurred during this three-month period are shown below. Each Division’s controller believes that the variable overhead costs incurred during the last nine months of the year will be at the same rate per component as experienced during the first three months.

Utilities $ 57,000

Indirect Labor $31,000

Supplies $16,000

Other $8,000

Total variable overhead $112,000

The East Division has planned/estimated fixed manufacturing overhead costs for the entire year as follows:

Supervision $ 872,000

Property Taxes $143,000

Depreciation $2,910,000

Insurance $631,000

Other $72,000

Total fixed manufacturing Overhead $4,628,000

Finished Goods Inventory: The desired monthly ending inventory of completed components is 20% of the next month’s estimated sales. The East Division has 4,000 units in the finished goods inventory on March 31.

Selling and Administrative Expenses: Selling and Administrative Expenses are budgeted at $400,000 per month plus 1% of total credit sales for the month.

REQUIRED:

1. Prepare a production budget for the East Division for the second quarter ending June 30. Show computations by month and in total for the quarter.

(10 pts.) _____

2. Prepare a direct materials purchases budget in units and dollars for each

type of material for the second quarter ending June 30. Again show computations by month and in total for the quarter.

(10 pts.) _____

3. Prepare a schedule of cash payments for direct materials for the second quarter. Assume that all direct materials are purchased on account and the East Division pays for ½ of the amount purchased in the month of purchase and the other ½ in the month following the purchase. The balance in the Accounts Payable account at 3/31 was $351,200.

(10 pts.) _____

4. Prepare a direct labor budget in hours and in dollars for the second quarter ending June 30. Again show computations by month in total for the quarter.

(5 pts.) _____

5. Prepare a manufacturing overhead budget for the second quarter. Show computations by month and in total for the quarter.

(5 pts.) _____

6. Compute a new “selling price per unit” for the East Division that will enable them to accumulate a balance of $100,000 in their cash account by the end of the second quarter. Assume that the cash balance at March 31 was $10,000.

(5 pts.) _____

7. Using the selling price per unit computed in #6 prepare a sales budget for the second quarter. Show computations by month and in total for the quarter.

(5 pts.) _____

8. Prepare a schedule of expected cash collections for the second quarter using the selling price per unit calculated in question #6. Assume that the East Division collects on its credit sales as follows; 70% in the month of sale, 20% in the month following the credit sale, 10% in the second month following the credit sale. To compute the balance in Accounts Receivable at 3/31 assume that the selling price per unit prior to 3/31 was $75.00.

(5 pts.) _____

9. Prepare a cash budget for the second quarter in month and in total for the East Division.

(20 pts.)

Answer 1

D E 3 B C SALES BUDGET April May 20,000 35,000 $89.32 $89.32 $1,786,301 $3,126,026 Units Sales price per unit Total Sales June 50000 $89.32 $4,465,752 Total T 105,000 $89.32 $9,378,079 July 45,000 5 mm PRODUCTION BUDGET May June 0 April Total July August 20.000 35,000 50,000 105,000 45,000 30,000 2. Sales in Units Desired units of ending inventory 7000 10000 9000 9000 6000 27,000 Desired Total Units 45,000 59,000 114,000 51,000 Less: desired units of beginning inventory 7000 4000 4000 9000 10000 49,000 42,000 38,000 23,000 Total production units 110,000 110,000

DIRECT MATERIALS BUDGET (No. 311) April May June 47 Quarter July 23,000 9 207000 38,000 9 342000 42,000 49,000[ 110,000 99 4 41000 990000 990,000 124740 124740 565740 1114740 378000 112860 319860 145530 487530 48 Units to be produced 49 X 9 feet 50 Total material needs 51 52 Add: Desired end. Inv. 53 Subtotal 54. 55 Less: Beginning 56 Inventory 57 58 Total materials to be 59 purchased 60 61 x $2.00 62 63 Total cost of purchased 64 69000 112860 145530 69000 250860 374670 420210 1,045,740 $ 1,045,740.00 $2.00 $2.00 $2.00 $2.00 $501,720 $749,340 $840,420 $2,091,480

Page Layout Formulas Data 6916502 Compatibilny Model Microsoft acel Review Arial - 10 Format Painter Clipboard F68 - General Font Alignment Arben 44 45 DIRECT MATERIALS BUDGET (No. 311) April June May Quarter July 38,000 23,000 9 207000 4 9,000 1 10,000 42,000 3 42000 441000 990000 990,000 378000 112860 319860 145530 487530 1247401 565740 1114740 46 47 48 Units to be produced 49 x 9 feet 50 Total material needs 51 52 Add: Desired end. Inv. Subtotal 54 55 Less: Beginning 56 Inventory 57 58 Total materials to be 59 purchased 60 61 x $2.00 62 63 Total cost of purchased 64 65 Total purchases Direct Materials 69000 112860 145530 69000 250860 374670 420210 $2.00 1,045,740 $ 1,045,740.00 $2.00 $2.00 $2.00 $501,720 $749,340 $840,420 $2,091,480 66 67 Oma erial No 208 68 Honor8PCO 69 Total purchases H+ Sheet1 Sheet2 Ready April $610,000 $501,720 $1 111 720) May $870,000 $749,340 $1 619 3401 June $910,000 $840,420 $1 750 4201 Quarter $2,390,000 $2,091,480 $4 481 4801 Sheet3

10 Format Painter General Font E Merge a center 789 - 64 65 Total purchases Direct Materials 66 E 67 Material No 208 68 Material No 311 69 Total purchases April $610.000 $501,720 $1,111,720 May $870,000 $749,340 $1,619,340 70 June $910,000 $840,420 $1,750,420 Quarter $2,390,000 $2,091,480 $4,481,480 $4,481,480 71 Quarter $351,200 SCHEDULE OF CASH PAYMENTS FOR DIRECT MATERIALS 73 April 74 May June 75 Opening Accounts Payable $351,200 $555,860 76 $809,670 77 Direct Material Purchases $1,111,720 $1,619,340 $1,750,420 78 79 Ending Accounts Payables $555,860 $809,670 $875,210 80 81 Cash Payment for Direct Materials $907,060 $1,365,530 $1,684,880 82 83 84 $4,481,480 $875,210 $3,957,470 $3,957,470 85 86 DIRECT LABOR BUDGET May OX April June Quarter 38 39 thonopreduced 23,000 0251 49,000 0.251 0 1 38,000 0.25 110,000 0251 x 25 (Shanina) Sheet1 Sheet2 Sheet3

E89- 0.25 DIRECTLABOR BUDGET April May 23,000 38,000 0.25 June 0.25 49,000 0.25 Quarter 110,000 0.25 5750 $18.00 9500 $18.00 12250 $18.00 27500 $18.00 $103,500 $171,000 $220,500 0.70 $495,000 $495,000 0.70 0.70 0.70 85 86 87 88 Units to be produced 89 x 25 (Shaping) 90 91 Hrs. Shaping Dept. 92 x $18.00 93 94 Labor cost Shaping dept. 95 96. x.70 (Assembly) 97 98 Hrs. Assembly Dept. 99 x $16.00 100 101 Labor cost for Assembly dept 102 103 104 x 10 (Finishing) 105 106 Hrs. Finishing Dept. 107 x $20.00 108 109 Labor cost Finishing dept 110 Total Labor Cost 111 M Sheet1 Sheet2 Sheet3 16100 $16.00 26600 $16.00 34300 $16.00 77000 $16.00 $257,600 $425,600 $548,800 $1,232,000 $1,232,000 0.1 0.1 0.1 0.1 2300 $20.00 3800 $20.00 4900 $20.00 11000 $20.00 $46,000 $407,100 $76,000 $672,600 $98,000 $867,300 $220,000 $1,947,000 K honor 8 Pro X

- General F134 I I 112 MANUFACTURING OVERHEAD BUDGET April May June Quarter $3.50 $1.78 $0.97 $3.50 $1.78 $0.97 $0.50 $3.50 $1.78 $0.50 $0.25 113 114 115 116 Variable Overhead: Rate / Unit 117 Utilities 118 Indirect Labor 119 Supplies 120 Other 121 122 Total production units 123 124 Utilities 125 Indirect Labor 126 Supplies 127 Other 128 129 Total Variable Overhead 130 131 Fixed Overhead $3.50 $1.78) $0.97 $0.50 $0.25 $0.25 $0.97 $0.50 $0.25 23000 38000 49000 110000 $40.969 $22,281 $11,500 $5,750 $40.969 $22,281 $11,500 $5,750 $40.969 $22,281 $11,500 $5,750 $40,969 $22,281 $11,500 $5,750 $80,500 $133,000 $171,500 $390,000 $385.000 $385,000 $1,170,000 132 $390,000 $390,000 133 Total Overhead 34 $470,500 $523,000 $561,500 $242,500 $228,000 $242,500 $280,500 $1,555,000 $1,555,000 $727,500 $827,500 $242,500 $319,000 35 less depreciation 36 Tata! Overhead paid in cash 38 honor 8 Pro » Sheet1 Sheet2 Sheet3 eady

110,000 DIRECT MATERIALS BUDGET (No. 208) April May June Quarter July 24 25 26 27 Units to be produced 28 x 4 lbs 29 Total material needs 30 31 Add: desired end. Inv. 32 Subtotal 23,000 4 92000 38,000 49,000 44 4 4 152000 196000 42,000 110,000 4 440000 440,000 84,000 524,000 168000 76,000 168,000 98,000 250,000 84000 280,000 34 Less: Beginning 5 Inventory 46,000 76,000 98,000 46000 7 Total materals to be 8 purchased 122,000 174,000 182,000 x $5.00 478,000 478,000 $5.00 $5.00 $5.00 $5.00 Total cost of purchases $610,000 $870,000 $910,000 $2,390,000

B 5 Total purchases Direct Materials 1 7 Material No 208 8 Material No 311 9 Total purchases April $610,000 $501,720 $1,111,720 May $870,000 $749,340 $1,619,340 June $910,000 $840,420 $1,750,420 Quarter $2,390,000 $2,091,480 $4,481,480 $4.481.480 ا ا SCHEDULE OF CASH PAYMENTS FOR DIRECT MATERIALS April May June د Quarter د 5. Opening Accounts Payable $351,200 $555,860 $809,670 $351,200 III $1,111,720 $1,619,340 $1,750,420 Direct Material Purchases $4,481,480 $809,670 $555,860 $875,210 $875,210 Ending Accounts Payables $907,060 $1,365,530 $1.684,880 $3,957,470 $3,957,470 Cash Payment for Direct Materials ,01,410

87 88 Units to be produced 89 X 25 (Shaping) 90 91 Hrs. Shaping Dept. 92 x $18.00 DIRECTLABOR BUDGET May 38,000 0.25 April 23,000 June 49,000 0.25 0.25 Quarter 110,000 0.25 5750 $18.00 33 9500 $18.00 12250 $18.00 27500 $18.00 24 Labor cost Shaping dept. 25 26. x.70 (Assembly) $103,500 $171,000 $220,500 27 0.70 $495.000 $495,000 0.70 0.70 0.70 98 Hrs. Assembly Dept. 29 x $16.00 DO 01 Labor cost for Assembly dept 16100 $16.00 26600 $16.00 34300 $16.00 77000 $16.00 $257,600 2 $425,600 $548,800 $1,232,000 $1,232,000 53 14 x 10 (Finishing) 0.1 0.1 0.1 0.1 16 Hrs. Finishing Dept. 7 x $20.00 2300 $20.00 3800 $20.00 4900 $20.00 11000 $20.00 8 + 9 Labor cost Finishing dept Total Labor Cost $46,000 $407,100 $76,000 $672,600 $98,000 $867,300 $220,000 $1,947,000

CASH BUDGET April May Quarter $1,535,411 $2,650,479 $3,929,862 $8,115,751 140 141 142 Total Cash Receipts 143 144 Cash Payments: 145 146 Material Purchases 47 Direct Labor 48 Overhead 49 Fixed Sell & Admin 50 Variable Sell & Admin 51 $2 Total Cash Payments 53 14 Excess (deficiency) of Cash $907,060 $407,100 $228,000 $400,000 $17,863 $1,365,530 $672,600 $280.500 $400,000 $31,260 $1,684,880 $867,300 $319,000 $400,000 $44,658 $3,957,470 $1,947,000 $827,500 $1,200,000 $93,781 $1,960,023 $2,749,890 $3,315,838 $8,025,751 $90,000 $614,024 $99,412 -$424,612 -$514,024 $10,000 -$414,612 $10,000 7 Beg. Balance of cash $100,000 $100,000 -$514,024 -$414,612 Fndng Balance of cash

D SALES BUDGET (Repeated) March April February May June Quarter 163 164 165 Units 166 Sales price per unit 167 Total Sales 168 10.000 $75.00 $750,000 14.000 $75.00 $1,050,000 20000 $89.32 $1,786,301 35,000 $89.32 $3,126,026 50,000 $89.32 $4,465,752 105000 $89.32 $9,378,079 169 170 Quarter $75,000 $315,000 $0 $1,786,301 171 SCHEDULE OF EXPECTED CASH COLLECTIONS 172 April May June 173 174 February (10) $75,000 $0 $0 175 March (.20; 10) $210,000 $105,000 176 177 April (.70; 20; 10) $1,250,411 $357,260 $178,630 178 179 May (70; 20) $0 $2,188,218 $625,205 180 $0 181 June (70) $0 $3,126,026 182 183 $3,929,862 $1,535,411 Totals $2,650,479 $ 184 185 86 - Since cash balance is depend on sales budget. Therefore, I have used Goal Seek Command. $2,813,424 $0 $3,126,026 $8,115,751 8,115,751