The company has just hired a new marketing manager who insists that unit sales c
ID: 2556255 • Letter: T
Question
The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget: Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 45,000 65,000 105,000 60,000 90,000 90,000 Selling price per unit $7 per unit
A
B
C
D
E
F
F
A
B
C
D
E
F
F
1 Chapter 9: Applying Excel 2 3 Data Year 2 Quarter Year 3 Quarter 4 1 2 3 4 1 2 5 Budgeted unit sales 45,000 65,000 105,000 60,000 90,000 90,000 6 7 • Selling price per unit $7 per unit 8 • Accounts receivable, beginning balance $65,000 9 • Sales collected in the quarter sales are made 75% 10 • Sales collected in the quarter after sales are made 25% 11 • Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter 12 • Finished goods inventory, beginning 12,000 units 13 • Raw materials required to produce one unit 5 pounds 14 • Desired ending inventory of raw materials is 10% of the next quarter's production needs 15 • Raw materials inventory, beginning 23,000 pounds 16 • Raw material costs $0.80 per pound 17 • Raw materials purchases are paid 60% in the quarter the purchases are made 18 and 40% in the quarter following purchase 19 • Accounts payable for raw materials, beginning balance $81,500Explanation / Answer
Prepartion of Sales Budget Year-2 , Quarter Year-3 Quarter 1 2 3 4 1 2 Sales Unit 45,000 65,000 105,000 60,000 90,000 90,000 Selling Price/Unit $7 $7 $7 $7 $7 $7 Budgeted Sales Value $315,000 $455,000 $735,000 $420,000 $630,000 $630,000 Schedule of Expected Cash Collection Year-2 , Quarter Year 1 2 3 4 Total Account Receivable, Beginning Balance $65,000 $65,000 First Quarter Sales $236,250 $78,750 $315,000 Second Quarter Sales $341,250 $113,750 $455,000 Third Quarter Sales $551,250 $183,750 $735,000 Fourth Quarter Sales $315,000 $315,000 Total Cash Collection $301,250 $420,000 $665,000 $498,750 $1,885,000 Schedule of Production Budget Year-2 , Quarter Year-3 Quarter 1 2 3 4 1 2 Add: Sales Unit 315,000 455,000 735,000 420,000 630,000 630,000 Less Ending Inventory (30% of Next Quarter Sales) 136,500 220,500 126,000 189,000 189,000 - Total Needs 451,500 675,500 861,000 609,000 819,000 630,000 Less: Beginning Inventory 12,000 136,500 220,500 126,000 189,000 189,000 Production Unit 439,500 539,000 640,500 483,000 630,000 441,000 Schedule of Raw Material Purchase Budget Year-2 , Quarter Year-3 Quarter 1 2 3 4 1 2 Production Unit 439,500 539,000 640,500 483,000 630,000 441,000 Add: Raw material Rquired for Prodcution Unit @ 5 Pound/Unit 2,197,500 2,695,000 3,202,500 2,415,000 3,150,000 2,205,000 Less: Ending Inventory ( in Pound) 10% of Next Quarter 269,500 320,250 241,500 315,000 220,500 - Total Needs in Pound 2,467,000 3,015,250 3,444,000 2,730,000 3,370,500 2,205,000 Less: Beginning Inventory (in Pound) 23,000 269,500 320,250 241,500 315,000 220,500 Total Raw Material Purchase ( Pound) 2,444,000 2,745,750 3,123,750 2,488,500 3,055,500 1,984,500 Price/Pound $0.80 $0.80 $0.80 $0.80 $0.80 $0.80 Total Raw Material Purchase Cost $1,955,200 $2,196,600 $2,499,000 $1,990,800 $2,444,400 $1,587,600 Schedule of Expected Cash Payment Year-2 , Quarter Year 1 2 3 4 Total Account payable, Beginning Balance $81,500 $81,500 First Quarter Purchase $1,173,120 $782,080 $1,955,200 Second Quarter Purchase $1,317,960 $878,640 $2,196,600 Third Quarter Purchase $1,499,400 $999,600 $2,499,000 Fourth Quarter Purchase $1,194,480 $1,194,480 Total Cash Collection $1,254,620 $2,100,040 $2,378,040 $2,194,080 $7,926,780