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 Using the new marketing manager's revised budgeted unit sales and selling price, use cell reference to update to these new amounts in Sheet 2 (at the top, in green). If you used cell references and formulas correctly, your spreadsheet should auto-compute the new amounts throughout the spreadsheet. Based on these revised amounts, answer the following questions. Your spreadsheet in Sheet 2 must also support your answers, so be sure to use a cell reference to answers tems a-d. What are the total expected cash collections FOR THE YEAR under this revised budget? Use a cell reference from Sheet 2. Do not just enter an amount. What is the total required production in units FOR THE YEAR under this revised budget? *Use a cell reference from Sheet 2. Do not just enter an amount. What is the total cost of raw materials to be purchased FOR THE YEAR under this revised budget? "Use a cell reference from Sheet 2. Do not just enter an amount. What is the total expected cash disbursements for raw materials FOR THE YEAR under this revised budget? "Use a cell reference from Sheet 2. Do not just enter an amount. Year 2 Quarter Year 3 Quarter Budgeted un sales 40,000 60.000 100.000 50.000 70.000 80.000 $8 perunt 365.000 75% Soling price per unit - Accounts receivable, beginning balance 9 - Sales collected in the quarter sales are made 10 Sales collected in the quarter aer 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 16 - Raw material costs 17 Raw materais purchases are paid 13 and 19 Accounts payable for raw materials, beginning balance 25% 30% of the budgeted unt sales of the next quarter 12,000 units 5 pounds 10% of the next quarte'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 Remember to enter a cell reference or formula into each of the cells marked with a 7 below. Do not just enter a number. 25 Create the sales budget Year 2 Quarter Year 3 Quarter 27 Budgeted sales 28 Selling price per unit 29 Total sales Create the schedule of expected cash 31 collections Accounts receivable beginning balance 34 First curses Second quarters 36 Third-quater sales 37 Fourth quarter sales 3 Total cash collections 40 Create the production budget Y 2 Quarter ory 42 Dudosted unt sans 41 Add cured tished goods w 44 Total needs 45 Less beginning inventory 45 Recured production Create the production budget Year 2 42 Budgeted unit sales 43 Add desired finished goods inventory 44 Total needs 45 Loss beginning inventory 46 Required production Create the raw materials purchases 48 budget Year 3 Quarter SO Required production (unit) 51 Rasmat ure to producent 52 Production needs (pounds) 53 Add desired ending inventory of raw materials coounds) 54 Totales (pounds 55 L beginning investory of m a pounds) 56 Raw materials to be purchased 57 Cost of raw materials per pound S8 Cost of metal to be purchased Create the schedule of expected cash 60 payments 52 Accounts payable beginning balance 63 Fint quarter purchases A Second quarter purchases 55 Th or urches 56 Fourth quarter purch 67 Total cash bruments