Excel Lab Assignment using cash budget and Index match

BUAD 281 Excel Lab 3 Assignment Build a cash budget using the assumptions in the Assumptions tab. Note the following: 1) Cash budget must be on a separate worksheet. Use formulas to reference the assumptions in the Assumptions tab. Do NOT 2) Use INDEX MATCH in your formulas to look up/pull the values for the following items: Cash collections Dividends Equipment purchases Direct labor Direct materials Manufacturing overhead Selling & admin 3) Think about how you can write the formula using absolute referencing so that you don't have to write a formula for each ite helpful to anchor only the column or the row (e.g., A$5 anchors the row at row 5 but will move columns; $A5 anchors the colu Point breakdown Proper format of the cash budget Proper use of INDEX MATCH Correct answer Professionalism 5 10 3 2 20 Bonus Try to write the financing section so that you don't have to manually determine when to repay the loan and interest. Hint: You might need some supplemental calculations below the cash budget. DUE DATE: FRIDAY, JULY uscexcellabs@gmail.com BUAD 281/ Excel Lab 3/SUMMER e Assumptions tab. Do NOT copy and paste the numbers into your Cash budget. write a formula for each item and each quarter. Hint: You might find it umns; $A5 anchors the column at A but will move rows) y the loan and interest. Y, JULY 14, 10:00 AM PDT M PDT 1 2 3 4 5 6 7 8 9 10 Name Jennifer Leila John Piper Peter Dennis Chloe Susan Madison Julie Future City Manhattan Hong Kong San Francisco Byron Bay Johannesburg Berlin Portland Beirut Santiago Sayulita Write the INDEX formula that will give me "Chloe" Write the INDEX formula that will give me "Santiago" Write the INDEX formula that will give me "Hong Kong" Write the INDEX formula that will give me "Piper" Madison Beirut Write the MATCH formula that gives you the position of the NAME that is typed in A19 Write the MATCH formula that gives you the position of the CITY that is typed in A19 Leila Write an INDEX MATCH formula that gives you the future city for the NAME that is typed n of the NAME that is typed in A19 n of the CITY that is typed in A19 uture city for the NAME that is typed in A22 Assumptions Beginning cash balance, January 1 Quarterly interest rate Minimum cash requirement Selling price per unit $5,000 2% $2,000 $10 Quarters Cash collections Dividends Equipment purchases Direct labor Direct materials Manufacturing overhead Selling & admin 1 $4,700 $100 $2,813 $1,790 $134 $230 2 $6,450 $0 $3,000 $4,388 $3,710 $186 $280 3 $14,100 $0 $0 $6,300 $4,800 $250 $630 4 $14,200 $200 $0 $4,950 $4,810 $205 $530