Question

ONLY NEED HELP WITH HIGHLIGHTED SECTION (cant get cash dis to equal the correct number I've tried 100 different things I cant find where I'm going wrong please help!)

Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The required production for the year should be 274,000 units. The cost of raw materials to be purchased for the year should be $1,106,800, whereas the total cash disbursements for the year should be $1,095,980. If you do not get this answer, find the errors in your worksheet and correct them.

1 Chapter 8: Applying Excel 3 Data Year 2 Quarter Year 3 Quarter 5 Budgeted unit sales 40.000 75.000 100.000 50.000 70.000 80.000 7 - Seling price per unit 8 - Accounts receivable, beginning balance 9 . Sales collected in the quarter sales are made 10 - Sales collected in the quarter after sales are made 11 - Desired ending finished goods inventory is 12 . Finished goods inventory, beginning 13 - Raw materials required to produce one unit 14 - Desired ending inventory of raw materials is 15 - Raw materials inventory, beginning 10 . Raw material costs 17 . Raw materials purchases are paid 18 and 19 - Accounts payable for raw materials, beginning balanc 20 21 Enter a formula into each of the cells marked with a ? below 22 Review Problem: Budget Schedules $3 per unit $65,000 75% 25% 30% of the budgeted unit sales of the next quarter 12,000 units 5 pounds 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 24 Construct the sales budget Year 2 Quarter Year 3 Quarter 75.000 100.000 50.000 70.000 80,000 28 Budgeted unit sales 27 Selling price per unit 28 Total sales 29 30 Construct the schedule of expected cash collection 40,000 $8 $320,000 $$ $800,000 $900,000 $400,000 $500,000 Year 2 Quarter 31 4 $ 85.000 $240,000 $ 80.000 450.000 32 Accounts receivable, beginning balance 33 First quarter sales 34 Second quarter sales 35 Third quarter sales 30 Fourth quarter sales 37 Total cash collections $150.000 600.000 Year $ 25.000 $ 320,000 $ 800.000 $ 800,000 $ 300,000 $2.085.000 $ 200.000 300,000 $ 500.000 $ 305,000 $30000 $750,000 Year 2 Quarter 40,000 80.000 39 Construct the production budget 40 41 Budgeted unit sales 42 Add desired finished goods inventory 43 Total needs 44 Less beginning inventory 45 Required production 22.500 75.000 30.000 105.000 22.500 82.500 62.500 12.000 50,500 100.000 15.000 115.000 30,000 85,000 50,000 21.000 71.000 15,000 56.000 Year 3 Quarter 70.000 24.000 94.000 21.000 73.000 205,000 21,000 286,000 12.000 274,000 47 Construct the raw materials purchases budget Year 2 Quarter ter Year 3 Quarter 2 Year 50,500 82.500 85.000 50,000 274,000 73,000 365.000 49 Required production (units 50 Raw materials required to produce one unit 51 Production needs pounds 52 Add desired ending inventory of raw materials (pounds 53 Total needs (pounds) 54 Less beginning inventory of raw materials (pounds) 55 Raw materials to be purchased 58 Cost of raw materials per pound 57 Cost of raw materials to be purchased 252,500 41.250 293.750 23,000 270,750 $0.80 $216.000 412.500 42.500 455.000 41.250 413.750 $0.80 $331.000 425,000 28.000 453,000 42,500 410,500 $0.80 $329,400 280.000 137,000 417,000 28,000 389,000 $0.80 $311,200 1.370.000 38,500 1,406,500 23,000 1,383,500 $0.80 $1,108,800 $$ Year 2 Quarter $ 81,500 129,900 $ 59 Construct the schedule of expected cash payments 80 61 Accounts payable, beginning balance 82 First quarter purchases 03 Second-quarter purchases 84 Third-quarter purchases 85 Fourth-quarter purchases 88 Total cash disbursements 86.640 198.000 $ 132,400 197,040 Year $ 31.500 $ 210,600 $ 331,000 $ 131,360 $ 328,400 188,720S 188.720 $ 318,080 $ 1,144, 220 $ 211,480 5 235 240 $329,440 THIS SHOULD BE S 1,095.900

Answer 1

Solutions:

D E F G H Particulars Budgeted unit sales selling price per unit Total sales Schedule of expected cash collection Quarter 1 Quarter 2 Quarter 3 40000 75000 100000 $8.00 $8.00 $8.00 $320,000.00 $600,000.00 $800,000.00 Quarter 4 50000 $8.00 $400,000.00 Year 3 Quarer 1 70000 $8.00 $560,000.00 Year 3 Quarer 2 80000 $8.00 $640,000.00 14 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 *$8*% collection) $240,000.00 $80,000.00 Cash received for Q2 Sale $450,000.00 $150,000.00 Cash received for Q3 Sale $600,000.00 $200,000.00 Cash received for Q4 Sale $300,000.00 Budgeted Cash Collection $305,000.00 $530,000.00 $750,000.00 $500,000.00 15 Year $65,000.00 $320,000.00 $600,000.00 $800,000.00 $300,000.00 $2,085,000.00 16 17 19 20 23 Production Budget Quarter 1 Quarter 2 Quarter 3 40000 75000 1000001 22500 300000 15000 12000 225001 30000 Particulars Budgeted sales unit Add: ending inventory (30% of next quarter sales) Less: Beginning inventory 24 Quarter 4 50000 21000 15000 Year Quarter 1, next year Quarter 2, next year 265000 70000 80000 21000 24000 12000 21000 25 26 27 Estimated production unit 50500 82500 85000 560001 274000 73000 28 29 20 et703 Sheet704 Sheet157 (18) Sheet225 (2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710

T D E F G H 29 30 31 32 Quarter 4 | 56000 33 Year Quarter 1, next year 274000 73000 וססכעס Budgeted Cost of raw material purchases Particulars Quarter 1 Quarter 2 Quarter 3 Budgeted Production units 50500 825001 85000 Raw material per unit (In Pounds) Total requirement of raw materials 252500 412500 425000 Add: Desired ending inventory (10% of next quarter production needs) 41250 42500 28000 Less: Beginning inventory 23000 41250 42500 2800001 1370000 365000 36500 28000 36500 23000 1383500 Budgeted purchase units of raw material (In Pounds Raw material cost per pound 270750 $0.80 413750 $0.80 410500 $0.80 288500 $0.80 $0.80 42 Budgeted cost of purchases $216,600.00 $331,000.00 $328,400.00 $230,800.00 $1,106,800.00 43 44 45 46 47 48 49 Schedule of expected cash disbursement for merchandise purchases Particulars Quarter 1 Quarter 2 Quarter 3 Quarter 4 Accounts Payable, Beginning $81,500.00 Q1 Purchases $129,960.00 $86,640.00 Q2 Purchases $198,600.00 $132,400.00 Q3 Purchases $197,040.00 $131,360.00 Q4 Purchases $138,480.00 Total payments | $211,460.00 $285,240.00 $329,440.00 $269,840.00 Year $81,500.00 $216,600.00 $331,000.00 $328,400.00 $138,480.00 $1,095,980.00 50 51 52 53 54 55 56 per et703 ) Sheet704 Sheet157 (18) Sheet225 (2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710