My part to an accounting group project

have to figure out the quarterly numbers for direct materials purchases, cash purchases budget, and direct labor budget. I have members completed part to 3 parts before which need to be checked that numbers are acurate. Make sure to add onto excel sheet with completed part and do calculations on excell. 

 

1-3 of project completed attached 

template for project attached .. but do work by adding onto first attachment 

attachment of example teacher gave yo understand what needed to be done

input

Budget

0,000

$ 10.00 $ 10.00 $ 10.00 $ 10.00

receipts

of these credit sales in the month of the sale;

70%

of the next quarter’s sales.

statues at a cost of $10 per unit

20%

200

of the following month’s production needs.

lbs.

5

13,000

10%

.

50%

Budget

hour of direct labor.

0.05

$ 10.00

Budget

per month and include $10,000 in depreciation.

per year.

of $143,700 are scheduled for May and $48,

for June.

rate per year

16%

:

April

May

June $ – 0
Equipment purchases
April $ – 0
May

June

Schedule 1:

Sales

April

May

June

July

August

Sales in units

20,000

5

30,000

25,000

15,000

Sales price per unit

$ 10.00

Schedule 2:

Cash

1. All sales are on account.

2. The accounts receivable balance on March 31 was $30,000. All of this balance was collectible.

3. The company collects

70%

25% are collected in the month following sale; and the remaining 5% are uncollectible.

Cash in initial quarter

Cash in subsequent month

30%

Beg. Accounts receivable

$

9,000

Schedule 3: Production Budget in Units

1. ABC plans to stock ending inventory of finished goods to equal

20%

2.Finished Goods inventory at 1/1/20X7 is

200

EI-FG as % of next quarter sales

Beg. Inventory

Schedule 4: Direct Materials Purchases

1. Five lbs. of material are required per unit of product

2. The material costs $0.40 per lb.

3. Management desires to have materials on hand at the end of each month equal to

10%

4. The beginning materials inventory was

13,000

Each unit requires in lbs

cost per lb

$ 0.40

Beg Inv RM in lbs

EI RM of % next month

Schedule 5: Cash Purchases Budget

1. Half of a month’s purchases are paid for in the month of purchase;

the other half is paid for in the following month.

2. There are no discounts for early payment.

3. The accounts payable balance on March 31 was $1

2,000

They are all collected in April.

Purchase on Credit

50%

Purchase on Cash

Schedule 6: Direct

Labor

Each unit produced requires

0.05

Each hour of direct labor costs the company $10.

Management fully adjusts the workforce to the workload each month.

DLH per unit

cost per DLH

Schedule 7:

Overhead

1. Variable manufacturing overhead is $20 per direct labor hour.

2. Fixed manufacturing overhead is $50,500 per month.

This includes $20,500 in depreciation, which is not a cash outflow.

Var MO per DLH

$ 20.00

Fixed MO per month

$ 50,500

Depr in Fixed MO per month

$ 20,500

Schedule 8: Ending Finished goods

1. Royal Company uses absorption costing in its budgeted income statement and balance sheet.

2. Manufacturing overhead is applied to units of product on the basis of direct labor hours.

3. The company has no work in process inventories.

Schedule 9: Selling and Administrative Expense Budget

Variable selling and administrative expenses are $0.50 per unit sold.

Fixed selling and administrative expenses are $

70,000

Var Selling per unit

$ 0.50

Fixed selling per month

$ 70,000

Depr incl in fixed selling per month 10,000

$ 10,000

Schedule 10: Cash Budget

1. A line of credit is available at a local bank, which allows the company to borrow up to $75,000.

a. All borrowing occurs at the beginning of the month,

and all repayments occur at the end of the month.

b. Any interest incurred during the second quarter will be paid at the end of the quarter

The interest rate is

16%

2. Royal Company desires a cash balance of at least $30,000 at the end of each month.

The cash balance at the beginning of April was $40,000.

3. Cash dividends of $51,000 are to be paid to shareholders in April.

Equipment purchases

800

4. This equipment will be installed and tested during the second quarter and will not

become operational until July, when depreciation charges will commence.

Interest

Interest rate per quarter

4%

Minimum Cash

$ 30,000

Dividends

$ 51,000

$ – 0

$ 143,700

$ 48,800

solution

Schedule 1: Sales Budget

2,000

0

$22 $22 $22 $22 $22 $22

$88,000

Schedule 2: Cash receipts Q1 Q2 Q3 Q4 Total

$ 9,000

Schedule 3: Production Budget in Units Q1 Q2 Q3 Q4 Total
Sales in units 2,500 3,300 4,000 2,000 11,800 1500 4000

800 400 300 300 800

200 660 800 400 200

Schedule 4: Direct Materials Purchases Q1 Q2 Q3 Q4 Total
Units to be produced

Schedule 4: Direct Materials Purchases Q1 Q2 Q3 Q4 Total
Units to be produced

Schedule 5: Cash Purchases Budget Q1 Q2 Q3 Q4 Total
Schedule 6: Direct Labor Budget Q1 Q2 Q3 Q4 Total

Units to be produced

Schedule 7: Overhead Budget Q1 Q2 Q3 Q4 Total

rate /hour

Q1 Q2 Q3 Q4 Total

rate

Overhead

2,000

Sales

before tax

Net income

Q1 Q2 Q3 Q4 Total

. Cash

Labor
OH
S&A
Dividends
Interest

Beg Bal

Cash

9,000

2,000

8,000

70,000

102,942 65,070

Q1

Q2

Q3

Q4

Total

Q1, 20X8

Q2, 20X8

Sales in Units

2,500

3,

300

4,000

11,800

1500

400

x sales price

$22

Sales revenue

$55,000

$72,600

$8

8,000

$44,000

$259,600

$33,000

Account receivable

$ 9,000

Current quarter sales

38,500

$ 50,820

$ 61,600

$ 30,800

$ 181,720

Last quarter sales

16,500

21,780

26,400

64,680

$47,500

$67,320

$83,380

$57,200

$255,400

plus desired EI- FG

660

Total needs

3,160

4,100

4,400

2,300

12,100

Less BI- FG

Units to be produced

2,960

3,440

3,600

1,900

11,900

Amt of cement per unit

Cement needs for production

plus desired EI RM-cement

Total need-cement

less BI RM-cement

Cement to be purchased

Cement cost per lb.

Total cement purchase cost

Amt of plaster per unit

Plaster needs for production

plus desired EI RM-plaster

Total need-plaster

less BI RM-plaster

Plaster to be purchased

Plaster cost per lb.

Total plaster purchase cost

Total DM purchase cost (Cement+Plaster)

Prior month purchases

Current purchases

DL needed per unit

DL hours needed

cost per hour

Total budgeted DL

DL hours needed

V

OH

Budgeted variable MO

Budgeted fixed MO

Total Manufacturing Overhead

Less Depreciation

Cash disbursements for MO

Schedule 8: Selling and Administrative Expense Budget

Units sold

Var

S&A

Budgeted variable S&A

Budgeted fixed S&A

Total budgeted S&A expense

Less depreciation

Cash disbursements for S&A expense

Schedule 9: Ending Finished goods

Amt.

Cost per

Direct materials-cement

Direct materials-plaster

Direct labor

Variable overhead

Fixed overhead

Units in ending inventory

Cost of ending inventory

Schedule 10: Budgeted cost of goods sold

Direct materials used – cement

35,700

Direct materials used – plaster

14,280

Direct labor used

61,880

43,280

Costs added

155,140

Beginning FG inventory

Available for sale

157,140

Less EI

-3,911

Cost of goods sold

153,229

Schedule 11: Budgeted income statement

less COGS

Gross margin

Selling and admin

Interest expense

Net income

Tax expense (30% of net income before tax)

32,509

Schedule 12: Cash Budget

Beg Bal

Cash Receipts

Cash Available

Cash disbursements-DM

Taxes

Purchase equip

Total disbursements

Ending cash balance

Repay

Borrowing

Cash at end of period

Total Borrowing

Schedule 13: Budgeted Balance sheet

End Bal

4,850

Accounts Receivable

Inventory raw materials

420

FG Inventory

Land

Building and Equip

Accum. Deprec.

-29,200

Total Assets

102,942

65,070

Acct Payable

2,580

Tax payable

Capital stock

17,500

RE

44,990

Total L&SE

input

solution

Budget

receipts

Q1 Q2 Q3 Q4 Total

Q1 Q2 Q3 Q4 Total

Q1 Q2 Q3 Q4 Total

Units to be produced

Schedule 4: Direct Materials Purchases Q1 Q2 Q3 Q4 Total
Units to be produced

Q1 Q2 Q3 Q4 Total

Budget

Q1 Q2 Q3 Q4 Total

Units to be produced

Budget

Q1 Q2 Q3 Q4 Total

rate /hour

Q1 Q2 Q3 Q4 Total

rate

Overhead

Sales

expense

before tax

Net income

Q1 Q2 Q3 Q4 Total

. Cash

Labor
OH
S&A
Interest

Beg Bal

Cash

2,000

102,942 65,070

Schedule 1:

Sales

Q1

Q2

Q3

Q4

Total

Q1, 20X8

Q2, 20X8

Sales in Units

x sales price

Sales revenue

Schedule 2:

Cash

Account receivable

Current quarter sales

Last quarter sales

Schedule 3: Production Budget in Units

Sales in units

plus desired EI- FG

Total needs

Less BI- FG

Units to be produced

Schedule 4: Direct Materials Purchases

Amt of cement per unit

Cement needs for production

plus desired EI RM-cement

Total need-cement

less BI RM-cement

Cement to be purchased

Cement cost per lb.

Total cement purchase cost

Amt of plaster per unit

Plaster needs for production

plus desired EI RM-plaster

Total need-plaster

less BI RM-plaster

Plaster to be purchased

Plaster cost per lb.

Total plaster purchase cost

Total DM purchase cost (Cement+Plaster)

Schedule 5: Cash Purchases Budget

Prior month purchases

Current purchases

Schedule 6: Direct

Labor

DL needed per unit

DL hours needed

cost per hour

Total budgeted DL

Schedule 7:

Overhead

DL hours needed

V

OH

Budgeted variable MO

Budgeted fixed MO

Total Manufacturing Overhead

Less Depreciation

Cash disbursements for MO

Schedule 8: Selling and Administrative Expense Budget

Units sold

Var

S&A

Budgeted variable S&A

Budgeted fixed S&A

Total budgeted S&A expense

Less depreciation

Cash disbursements for S&A expense

Schedule 9: Ending Finished goods

Amt.

Cost per

Direct materials-cement

Direct materials-plaster

Direct labor

Variable overhead

Fixed overhead

Units in ending inventory

Cost of ending inventory

Schedule 10: Budgeted cost of goods sold

Direct materials used – cement

35,700

Direct materials used – plaster

14,280

Direct labor used

61,880

43,280

Costs added

155,140

Beginning FG inventory

2,000

Available for sale

157,140

Less EI

-3,911

Cost of goods sold

153,229

Schedule 11: Budgeted income statement

less COGS

Gross margin

Selling and admin

Interest

Net income

Tax expense (30% of net income before tax)

32,509

Schedule 12: Cash Budget

Beg Bal

Cash Receipts

Cash Available

Cash disbursements-DM

Taxes

Dividends

Purchase equip

Total disbursements

Ending cash balance

Repay

Borrowing

Cash at end of period

Total Borrowing

Schedule 13: Budgeted Balance sheet

End Bal

4,850

Accounts Receivable

9,000

Inventory raw materials

420

FG Inventory

Land

8,000

Building and Equip

70,000

Accum. Deprec.

-29,200

Total Assets

102,942

65,070

Acct Payable

2,580

Tax payable

Capital stock

17,500

RE

44,990

Total L&SE

inputs

0,000

$ 10.00 $ 10.00 $ 10.00 $ 10.00

of these credit sales in the month of the sale;

are collected in the month following sale; and the remaining 5% are uncollectible.

70%

25%

5%

of the following month’s budgeted unit sales.

20%

of the following month’s production needs.

lbs.

5

13,000

10%

50%

hour of direct labor.

0.05

$ 10.00

per year.

was $40,000.

of $143,700 are scheduled for May and $48,800 for June.

16%

April

May

$ – 0

Equipment purchases
April $ – 0
May

June

Schedule 1: Sales Budget

April

May

June

July

August

Sales in units

20,000

5

30,000

25,000

15,000

Sales price per unit

$ 10.00

Schedule 2: Cash receipts

1. All sales are on account.

2. The accounts receivable balance on March 31 was $30,000. All of this balance was collectible.

3. The company collects

70%

2

5%

Cash in initial month

Cash in subsequent month

Uncollectible

Schedule 3: Production Budget in Units

1. The company desires to have inventory on hand at the end of each month equal to

20%

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

EI as % of next month sales

Schedule 4: Direct Materials Purchases

1. Five lbs. of material are required per unit of product

2. The material costs $0.40 per lb.

3. Management desires to have materials on hand at the end of each month equal to

10%

4. The beginning materials inventory was

13,000

Each unit requires in lbs

cost per lb

$ 0.40

Beg Inv RM in lbs

EI RM of % next month

Schedule 5: Cash Purchases Budget

1. Half of a month’s purchases are paid for in the month of purchase;

the other half is paid for in the following month.

2. There are no discounts for early payment.

3. The accounts payable balance on March 31 was $12,000.

They are all collected in April.

Purchase on Credit

50%

Purchase on Cash

Schedule 6: Direct Labor Budget

Each unit produced requires

0.05

Each hour of direct labor costs the company $10.

Management fully adjusts the workforce to the workload each month.

DLH per unit

cost per DLH

Schedule 7: Overhead Budget

1. Variable manufacturing overhead is $20 per direct labor hour.

2. Fixed manufacturing overhead is $50,500 per month.

This includes $20,500 in depreciation, which is not a cash outflow.

Var MO per DLH

$ 20.00

Fixed MO per month

$ 50,500

Depr in Fixed MO per month

$ 20,500

Schedule 8: Ending Finished goods

1. Royal Company uses absorption costing in its budgeted income statement and balance sheet.

2. Manufacturing overhead is applied to units of product on the basis of direct labor hours.

3. The company has no work in process inventories.

Schedule 9: Selling and Administrative Expense Budget

Variable selling and administrative expenses are $0.50 per unit sold.

Fixed selling and administrative expenses are $70,000 per month and include $10,000 in depreciation.

Var Selling per unit

$ 0.50

Fixed selling per month

$ 70,000

Depr incl in fixed selling per month 10,000

$ 10,000

Schedule 10: Cash Budget

1. A line of credit is available at a local bank, which allows the company to borrow up to $75,000.

a. All borrowing occurs at the beginning of the month,

and all repayments occur at the end of the month.

b. Any interest incurred during the second quarter will be paid at the end of the quarter

The interest rate is

16%

2. Royal Company desires a cash balance of at least $30,000 at the end of each month.

The cash balance at the beginning of

April

3. Cash dividends of $51,000 are to be paid to shareholders in April.

Equipment purchases

4. This equipment will be installed and tested during the second quarter and will not

become operational until July, when depreciation charges will commence.

Interest rate per year

Interest rate per quarter

4%

Minimum Cash

$ 30,000

Dividends:

$ 51,000

$ – 0

June

$ 143,700

$ 48,800