9116122, 751 PM ‘Assessment Review - Corporate Finance Institute 6G CFI. Advanced Excel Formulas & Functions Correct Answer Parcally Correct Incorrect Answer tps. onlinatests app/Assees. aspx? Guid-0C721EDD7772429E ASASCFAEAACATZOB&a-R* wr5116722, 751 PM ‘Assessment Review - Corporate Finance Institute ‘1 Toreturn the value of the cell D8, the formula should be =OFFSET(A1, 1. , 2. ). Your Answer 17 23 Correct Answer a7 23 Explanation [Al needs to move 7 rows down and 3 columns to the right to reach D8, hips oninetestsappiAssess.aspx9u 1C721EDD7772429EASSCFAEAACAT2GBBa-R1 anr5116722, 751 PM ‘Assessment Review - Corporate Finance Institute 2 Tosetup scenarios, you need'to first use___to setup alist then _to set up the reference cel, Last you need to use____to set up the cells that display the output results from the scenario. Your Answer Data validation; VLOOKUP; CHOOSE Correct Answer Data validation; VLOOKUP; CHOOSE Explanation None. hips oninetestsappiAssess.aspx9u 1C721EDD7772429EASSCFAEAACAT2GBBa-R1 ant5116722, 751 PM ‘Assessment Review - Corporate Finance Institute 3 The 1, function calcul loan repayment. -s the repayment far a loan; the _2._ function calculates the interest portion of Your Answer 1.PMT 2.1PMT Correct Answer 1, PMT 2.1PMT Explanation None. hips oninetestsappiAssess.aspx9u 1C721EDD7772429EASSCFAEAACAT2GBBa-R1 ant5116722, 751 PM ‘Assessment Review - Corporate Finance Institute 4 A e c > E F 6 4 ' 1 #of periods 6 2 3 2017/12/31 2018/12/31 2019/12/31 2020/12/31 2024/12/31 2022/12/31 2023/12/31 2024/12/31 4 For 1000 300 400 500 600 700 200 300 Based on the image above, the dynamic formula that finds the XIRR of the 6th period would be: (must use cell B1 to make formula dynamic) =XIRR(BA:OFFSETIB4,0,__),B3:OFFSET(B3, and the output would be_4. Your Answer 81-1, B11, 34.1% Correct Answer B1-1, B1-1, 34.1% Explanation The ending column should by offset by 0 rows, and # of periods less 1 columns. So the formula should be =XIRR(B4:OFFSET(B4,0,81-1),83:0FFSET(83,0,81-1)) and the output would be 34.1%. tps. onlinetests.app/Assess.aspx7quid-0C721EDD7772429E ASSSCFAEAACATZGB4a=R1 snr5116722, 751 PM 5 A 8 < ° 1 ax me 2 » 1 2 3 4 5 3 2 2 6 8 w 4 a 3 9 2 3s 5 * 4 c—z sw 6 se s 0 2m 2 7 The formula = INDEX(@2:F6MATCH("4x",__0) MATCH('3x",_0)) would give the result of the cell with the red box. Your Answer AZNB, BUT Correct Answer AZiAG, BIFI Explanation ‘Assessment Review - Corporate Finance Institute A2:A6 selects the row labels, B1:F1 selects the column labels, hips oninetestsappiAssess.aspx9u 1C721EDD7772429EASSCFAEAACAT2GBBa-R1 en75116722, 751 PM ‘Assessment Review - Corporate Finance Institute 6 A 8 c 1 Revenue 100,000 2 cocs 40% 40,000 3. Gross Profit 60,000 4 Solaries 20,000 5 Matketing 12,000 6 Rent 5,000 7. Eamings Before Tax 23,000 8 Tax 25% 5,750 9. Net income 17,250 Use goal seek to answer this question. All else equals, to have a net income of 20,000, the COGS margin percentage must be__, and the gross profit must be Your Answer 36.3%, 63,667 Correct Answer 36.3%, 63,667 Explanation ‘Open Data - What If Analysis - Goal Seek, Set cell: C9, To value: 2000, By changing cell: B2, The result would be COGS margin: 36.3%, gross profit: 63,667. htpssc.onlinetesis.app/Assess.aspx7quid-0C721EDD7772429E ASSSCFAEAACATZGB4a=R amr9116122, 751 PM ‘Assessment Review - Corporate Finance Institute 1 ei 3 [=COUNTAAt] 4 The formula in the cell above would yield the result: 1 Your Answer 19 Correct Answer 1.9 Explanation COUNTA counts the cells that contain information, so out of the cells that are selected, all 9 contain information, hips oninetestsappiAssess.aspx9u "T21EDD7772429EAS3SCFAEAACA2688a-R° anr5116722, 751 PM 8 A B 1 1 2 2 2 4 3 3 6 4 4 8 5 5 10 6 6 2 7 7 4 8 8 16 9 9 18 10 10 20 The output of this function is: 1. ‘Assessment Review - Corporate Finance Institute 12 15 18 21 24 7 30 12 16 20 24 28 32 36 40 INDIRECTIMID(CELL(‘address",C3),2,")&CELL('row',D8)) Your Answer 1.18 Correct Answer 118 Explanation INDIRECT takes the text and recognizes as the "address" of a cell MID(CELL('address”\C3)2,1) takes the second character in the address of C3, which is the C from $C$3, CELL("row’,D6) takes the row from D6, whichis 6. Combining that with INDIRECT, It should be INDIRECT(C6), which is 18, htpssc.onlinetesis.app/Assess.aspx7quid-0C721EDD7772429E ASSSCFAEAACATZGB4a=R 10 15 20 25 30 35 40 45, 50 12 18 24 30 36 42 48 54 onr5116722, 751 PM ‘Assessment Review - Corporate Finance Institute 9 The formula =MIDCABCDEFGHI34) would yield the resut:_1 Your Answer 1. CDEF Correct Answer 1. CDEF Explanation =MID(ABCDEFGHI,3,4) returns the 4 characters starting from the 3rd character. tps. onlinetests.app/Assess.aspx7quid-0C721EDD7772429E ASSSCFAEAACATZGB4a=R1 son75116722, 751 PM 10 A B 1 1 2 2 2 4 3 3 6 4 4 8 5 5 10 6 6 2 7 7 4 8 8 16 9 9 18 10 10 20 The output of this function is: =IF(AND(C6>B7,0R(E1*A4=E4,D5"true" false") p- Your Answer True Correct Answer True Explanation AND functions are only true when all of the logical statements are true. OR functions are true if one of the logical statements are true. C6>87 is true, E1A4=E4 is true, DS htpssc.onlinetesis.app/Assess.aspx7quid-0C721EDD7772429E ASSSCFAEAACATZGB4a=R ‘Assessment Review - Corporate Finance Institute 12 15 18 21 24 7 30 12 16 20 24 28 32 36 40 10 15 20 25 30 35 40 45, 50 12 18 24 30 36 42 48 54 sn79116122, 751 PM ‘Assessment Review - Corporate Finance Institute 11 Pivot Table - Data.xisx Please download and open the attached file. Round your answers to whole numbers, without the dollar symbol Hats generated _1_total in revenue, and pants generated _2._in revenue through Instagram. Shorts generated on average _3._ in revenue through AdWords. 4. (product) costed the most in shipping costs and _S._(channel) costed the most in marketing cost. Your Answer 1.480 2.231 3.32 4. shirt 5. Facebook Correct Answer 1.480 2.379 3.32 4. Tshirt 5. Facebook Explanation Pivot Table - Answers.xisx hips oninetestsappiAssess.aspx9u "T21EDD7772429EAS3SCFAEAACA2688a-R° snr5116722, 751 PM ‘Assessment Review - Corporate Finance Institute 12 Pivot Table - Dataxlsx Please download and open the attached file Insert a Pivot Table, slicer, and timeline to answer the following questions, Round your answer to whole numbers, without the dollar symbol, For the first 5 days, the total revenue generated through Facebook and Instagram is_1._ From Jan 4th to 6th, the revenue generated through non-social media channels (AdWords and Email) totals to_2. Your Answer 1.551 2.383 Correct Answer 1.551 2.383 Explanation Pivot Table - Answers xlsx hips oninetestsappiAssess.aspx9u 1C721EDD7772429EASSCFAEAACAT2GBBa-R1 san75116722, 751 PM ‘Assessment Review - Corporate Finance Institute 13. When creating a stacked chart to portray the breakdown of the income starement, the following itern is nat included Your Answer Revenue Correct Answer Revenue Explanation Revenue is the sum of all of the others, soif itis included, i would skew the chart hips oninetestsappiAssess.aspx9u 1C721EDD7772429EASSCFAEAACAT2GBBa-R1 sanr5116722, 751 PM ‘Assessment Review - Corporate Finance Institute 14 ‘According to the lecture video on building dynamic charts, which of the following Excel functions are used in the "Refers to:" formula in Name Manager? Your Answer OFFSET Correct Answer OFFSET coUNT Explanation We used the OFFSET and COUNT functions to build the formula in Name Manager in order to create dynamic charts, tps. onlinetests.app/Assess.aspx7quid-0C721EDD7772429E ASSSCFAEAACATZGB4a=R1 ssi75116722, 751 PM ‘Assessment Review - Corporate Finance Institute 15. The combo box (form control) option in macros can be used to substitute for__ when doing scenario analysis. Your Answer Data validation Correct Answer Data validation Explanation None. hips oninetestsappiAssess.aspx9u 1C721EDD7772429EASSCFAEAACAT2GBBa-R1 s6n79116122, 751 PM ‘Assessment Review - Corporate Finance Instiute Page 1 of 1 summary Return to Course hips. onlinetesis.app/Assess.aspx?quid-0C721EDD7772429E ASSSCFAEAACATZGB&a=Rt amar