Question

Requirement 2: 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: Year 3 Quarter Year 2 Quarter Data 1 2 Budgeted unit sales 45,000 65,000 115,000 70,000 Selling price per unit $ 7 80,000 100,000 | 1 Chapter 8: Applying Excel Data 1 45,000 2 65,000 3 115,000 Year 3 Quarter 4 1 7 0,000 80,000 2 100,000 $ 7 per unit $ 65,000 75% Budgeted unit sales 5 • Selling price per unit | 7 • Accounts receivable, beginning balance 8 . Sales collected in the quarter sales are 19 made 1. Sales collected in the quarter after sales are made • Desired ending finished goods inventory 12 13. Finished goods inventory, beginning 14. Raw materials required to produce one unit 15 • Desired ending inventory of raw materials 10 25% 11 is 30% of the budgeted unit sales of the next quarter 12,000 units 5 pounds 16 is 17 l. Raw materials inventory, beginning 18 . Raw material costs 191. Raw materials purchases are paid and • Accounts payable for raw materials, beginning balance 10% of the next quarter's production needs 23,000 pounds $ 0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase $ 81,500 a. What are the total expected cash collections for the year under this revised budget? Expected cash collections for the year b. What is the total required production for the year under this revised budget? Total required production for the year

Answer 1

Solutions:

Particulars Budgeted unit sales selling price per unit Total sales Schedule of expected cash collection Quarter 1 Quarter 2 Quarter 3 Quarter 4 45000 65000 115000 70000 $7.00 $7.00 $7.00 $315,000.00 $455,000.00 $805,000.00 $490,000.00 Year 3 Quarer 1 80000 $7.00 $560,000.00 Year 3 Quarer 2 100000 $7.00 $700,000.00 $7.00 10 11 12 13 14 15 Schedule of expected cash collection Particulars Quarter 1 Quarter 2 Quarter 3 Quarter 4 Accounts receivables beginning $65,000.00 Cash received for Q1 Sale (Units *$7*% collection) | $236,250.00 $78,750.00 Cash received for Q2 Sale $341,250.00 $113,750.00 Cash received for Q3 Sale $603,750.00 $201,250.00 Cash received for Q4 Sale $367,500.00 Budgeted Cash Collection $301,250.00 $420,000.00 $717,500.00 $568,750.00 Year $65,000.00 $315,000.00 $455,000.00 $805,000.00 $367,500.00 $2,007,500.00 16 17 18 19 20 21 22 23 Particulars Budgeted sales unit Add: ending inventory (30% of next quarter sales) | Less: Beginning inventory Production Budget Quarter 1 Quarter 2 Quarter 3 45000 65000 115000 19500 34500 21000 12000 19500 34500 24 25 Quarter 4 70000 24000 21000 Year Quarter 1, next year Quarter 2, next year 295000 80000 100000 24000 30000 12000 24000 26 27 Estimated production unit 52500 80000 101500 73000 307000 86000 28 29 et703 ) Sheet704 Sheet157 (18) Sheet225 (2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710

30 31 32 Quarter 4 73000 Year Quarter 1, next year 307000 86000 5 5 34 35 Budgeted Cost of raw material purchases Particulars Quarter 1 | Quarter 2 Quarter 3 Budgeted Production units 52500 80000 101500 Raw material per unit (In Pounds) Total requirement of raw materials 262500 400000 507500 Add: Desired ending inventory (10% of next quarter production needs) 40000 50750 36500 Less: Beginning inventory 23000 40000 50750 26250040000 365000 1535000 430000 43000 36500 43000 23000 38 39 40 Budgeted purchase units of raw material (In Pounds Raw material cost per pound 279500 $0.80 410750 $0.80 493250 $0.80 371500 $0.80 1555000 $0.80 41 42 Budgeted cost of purchases $223,600.00 $328,600.00 $394,600.00 $297,200.00 $1,244,000.00 43 44 45 46 47 48 Schedule of expected cash disbursement for merchandise purchases Particulars Quarter 1 Quarter 2 Quarter 3 Quarter 4 Accounts Payable, Beginning $81,500.00 01 Purchases $134,160.00 $89,440.00 02 Purchases $197,160.00 $131,440.00 Q3 Purchases $236,760.00 $157,840.00 Q4 Purchases $178,320.00 Total payments $215,660.00 $286,600.00 $368,200.00 $336,160.00 49 50 Year $81,500.00 $223,600.000 $328,600.00 $394,600.00 $178,320.00 $1,206,620.00 51 52 53 54 55 56 pager et703 ) Sheet704 Sheet157 (18) Sheet225 (2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710

Solution e:

Yes this is a potential problem as in third quarter 101500 units are to be produced.