Question

Directions: Complete requirements 1-3 below. The primary purpose of this assignment is to apply your basic Excel skills to the topics covered in Chapters 8. In every cell that requires an entry, you are required to use a cell reference or formula. De not just enter an amount into a cell. Your grade will be based on the use of cell references and formulas, as well as the accuracy of your answers. Requirement 1 using the Excel worksheet that appears in Sheet 2, recreate the Review Problem related to Mynor Corporation on pages 362-363. In Sheet 2, you are required to enter a cell reference or formula in every cell with a "?". Do not just enter an amount, as that will defeat the purpose of this Excel assignment. Your answers in Sheet 2 should match the answers on pages 362 - 363 before you proceed to the next requirement. Requirement 2 Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The total expected cash collections for the year should now be $2,085,000. If you do not get this answer, find the errors in your worksheet and correct them. Requirement 3 The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price per unit from $8 to $7. The marketing manager would like to use the following projections in the budget: Year 2 Quarter Your Quarter Ited to 0.000 0 00 120 00 00 00 00 100.000 S pre per un Spa

Answer 1

ORIGINAL BUDGET Year 3 Year 2 Qtr.1 Qtr.2 Qtr.3 Qtr.4 Qtr.1 Qtr.2 Sales budget Budgeted unit sales Selling price per unit 60,000 40,000 80,000 1,00,000 50,000 70,000 8 8 8 8 8 Total sales 3,20,000 4,80,000 8,00,000 4,00,000 5,60,000 6,40,000 Schedule of expected cash collections Accounts receivables, beginning balance First quarter sales Second quarter sales Third quarter sales Fourth quarter sales 65,000 2,40,000 80,000 4,20,000 1,40,000 3,60,000 1,20,000 4,80,000 6,00,000 2,00,000 3,00,000 1,00,000 Total cash collections 7,20,000 3,05,000 4,40,000 5,00,000 5,20,000 6,20,000 Production budget Budgeted unit sales Add: Desired finished goods inventory (30% of budgeted unit sales of next quarter) 40,000 60,000 1,00,000 50,000 70,000 80,000 18,000 30,000 15,000 21,000 24,000 Total needs 58,000 90,000 1,15,000 71,000 94,000 80,000 f eginnnginventory -12,000 18,000 -30,000 -15,000 -21,000 24,000 Reyunedd production 46,000 72,000 85,000 56,000 73,000 56,000

Raw materials purchase budget Required production (units) Raw materials required to produce one unit Production needs (pounds) 72,000 46,000 85,000 56,000 73,000 56,000 5 2,80,000 3,60,000 4,25,000 2,30,000 3,65,000 2,80,000 Add: Desired ending inventory of raw materials (pounds)-10% of next quarter's production needs Total needs (pounds) 42,500 36,000 28,000 28,000 36,500 2,66,000 4.02.500 3,16,500 4,53,000 3,93,000 2,80,000 23000 Less: Beginning inventory of raw materials (pounds) Raw materials to be purchased 36.000 42,500 -28,000 -36,500 -28,000 3,66,500 2,43,000 2,88,500 4,10,500 3,56,500 2,52,000 Cost of raw materials per pound 0.8 0.8 0.8 0.8 0.8 0.8 1,94,400 Cost of raw materials to be purchased 2,93,200 3,28,400 2,30,800 2,85,200 2,01,600 Schedule of expected cash payments Accounts payables, beginning balance First quarter purchases Second quarter purchases 81500 77,760 1,16,640 1,71,120 1,14,080 1,17,280 1,97,040 1,75,920 1,20,960 Third quarter purchases Fourth quarter purchases 1,31,360 1,38,480 92,320 Total cash disbursements 2,53,680 1,98,140 3,14,320 2,69,840 2,63,440 2,35,040 Camsca

REVISED BUDGET Year 3 Year 2 Qtr.3 Qtr.1 Qtr.2 Qtr.1 Qtr.2 Qtr.4 Sales budget Budgeted unit sales Selling price per unit 70,000 90,000 50,000 1,20,000 80,000 1,00,000 7 7 Total sales 3,50,000 4,90,000 8,40,000 5,60,000 6,30,000 7,00,000 Schedule of expected cash collections Accounts receivables, beginning balance 65,000 First quarter sales 2,62,500 87,500 4,72,500 1,57,500 Second quarter sales 3,67,500 1,22,500 5,25,000 Third quarter sales 6,30,000 2,10,000 Fourth quarter sales 4,20,000 1,40,000 Total cash collections 3,27,500 4,55,000 7,52,500 6,30,000 6,12,500 6,82,500 Production budget Budgeted unit sales 50,000 70,000 1,20,000 80,000 90,000 1,00,000 Add: Desired finished goods inventory (30% of budgeted unit sales of next quarter) 24,000 21,000 36,000 27,000 30,000 Total needs 71,000 1,06,000 1,44,000 1,07,000 1,00,000 1,20,000 e eginaing inventory kequired orcaustion -12,000 -21,000 36,000 -24,000 -27,000 -30,000 59,000 85,000 1,08,000 83,000 93,000 70,000

Raw materials purchase budget Required production (units) Raw materials required to produce one unit 70,000 93,000 83,000 1,08,000 85,000 59,000 5 5 3,50,000 4,65,000 4,15,000 5,40,000 4,25,000 2,95,000 Production needs (pounds) Add: Desired ending inventory of raw materials (pounds)-10% of next quarter's production needs Total needs (pounds) 35,000 41,500 46,500 54,000 42,500 3,50,000 5,00,000 5,81,500 4,61,500 4,79,000 3,37,500 -46,500 35,000 Less: Beginning inventory of raw materials (pounds) Raw materials to be purchased -54,000 -41,500 42,500 23000 4,53,500 3,15,000 4,36,500 5,27,500 4,20,000 3,14,500 0,8 0.8 0.8 0.8 0.8 0.8 Cost of raw materials per pound Cost of raw materials to be purchased 2,51,600 4,22,000 3,62,800 2,52,000 3,49,200 3,36,000 Schedule of expected cash payments 81,500 Accounts payables, beginning balance First quarter purchases Second quarter purchases Third quarter purchases Fourth quarter purchases 1,00,640 2,17,680 1,50,960 1,45,120 2,09,520 1,39,680 1,51,200 2,53,200 1,68,800 2,01,600 1,34,400 2,32,460 3,10,160 2,96,320 3,92,880 3,70,400 3,52,080 Total cash disurse ments