Question

# Data                                    Year 2 Quarter     &

Data                                    Year 2 Quarter                              Year 3 Quarter

1              2             3    4           1             2

Budgeted unit sales        40,000 60,000 100,000             50,000 70,000 80,000

Selling price per unit \$12

Accounts receivable, beginning balance \$65,000

Sales collected in the quarter sales are made 75%

Sales collected in the quarter after sales are made 25%

Desired ending finished goods inventory is 30% of budgeted unit sales of next QTR

Finished goods inventory, beginning 12,000

Raw materials required to produce one unit 5 pounds

Desired ending inventory of raw materials is 10% of next Qtr production needs

Raw materials inventory, beginning 23,000 pounds

Raw material costs \$0.80 per pound

Raw materials purchases are paid 60% in Qtr purchases are made and 40% in Qtr following purchase

Accounts payable for raw materials, beginning balance \$81,500

Direct labor cost per hour \$15

Direct labor hour per unit 0.2

Variable MOH rate \$2

Total fixed MOH \$60,000

Variable S&A expense rate \$1.80

Minimum cash balance \$50,000

Annual Interest rate 12%

BASED on above info what is the EXCEL formulas for the missing info below to get correct answers?

 Construct the production budget Year 2 Quarter Year 3 Quarter 1 2 3 4 Year 1 2 Budgeted unit sales ? ? ? ? ? ? ? Add desired finished goods inventory ? ? ? ? ? ? Total needs ? ? ? ? ? ? Less beginning inventory ? ? ? ? ? ? Required production ? ? ? ? ? ?
 Construct the direct labor budget Year 2 Quarter 1 2 3 4 Year Required production (units) ？ ? ? ？ ？ Direct labor-hours per unit ？ ？ ？ ？ ？ Total direct labor-hours needed ？ ？ ？ ？ ？ Direct labor cost per hour ？ ？ ？ ？ ？ Total direct labor cost ？ ？ ？ ？ ？

 PRODUCTION BUDGET Q1 Q2 Q3 Q4 Year Q1 Q2 Budgeted Sales Units 40,000 60,000 1,00,000 50,000 2,50,000 70,000 80,000 Add: Desired Ending Finished inventory 18,000 30,000 15,000 21,000 21,000 24,000 Total Needs 58,000 90,000 1,15,000 71,000 2,71,000 94,000 Less: Beginning Finished Inventory 12,000 18,000 30,000 15,000 12,000 21,000 Required Production in units 46,000 72,000 85,000 56,000 2,59,000 73,000 LABOUR COST BUDGETS QUARTER-1 QUARTER-2 QUARTER-3 QUARTER-4 Total Units to be produced 46,000 72,000 85,000 56,000 2,59,000 Labour required per unit 0.2 0.2 0.2 0.2 0.2 Labour hours 9200 14400 17000 11200 51800 Labuor Rate per hour 15 15 15 15 15 Budgeted Labour cost in \$ 1,38,000 2,16,000 2,55,000 1,68,000 7,77,000

#### Earn Coins

Coins can be redeemed for fabulous gifts.