Solve the 2 sheets.
+ B D E F G H K L M N o P P R S w 1 Below is an unstructured data that shows different cost types for each building per each month. Write a formula in the yellow cell that sums up different combinations automatically. No pivot tables please. For example, Building C Opex Costs in April are 68. But what if we want to lookup Opex for Q in July or Genex for F in May or Capex for B in Sep... Formula should bring the correct value for every possible combination. So when cells C5, D5, E5 are changed randomly; F5 should still show the correct sum. 2 3 4 5 6 e Genex Mar 7 8 Mar 9 10 11 12 Building a b Aug 45 73 43 Feb 14 56 29 37 87 2 Oct 88 93 28 Nov 42 100 87 72 Dec 52 38 33 94 34 97 95 30 37 d Jan 82 52 98 70 26 46 98 86 56 2 2 61 e f 99 Apr 51 49 51 61 10 99 4 78 23 85 96 98 May 44 4 55 70 89 36 31 40 57 61 77 12 Jun 46 23 17 76 71 94 38 19 74 42 17 95 30 76 46 78 Sep 98 49 90 44 13 81 89 87 9 26 61 34 41 g g h h a b b 30 68 16 2 80 16 37 32 87 57 20 53 |99B%9man99 Jul 100 37 19 34 71 20 18 12 21 87 18 97 53 2 54 99 $249a89a%8bnn3w9 Cost Capex Opex Genex Capex Opex Capex Opex Capex Capex Opex Genex Capex Opex Genex Capex Opex Genex Capex Opex Genex Capex Opex Genex Genex Capex Opex 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 90 49 d 66 3 3 64 33 50 78 60 76 89 26 64 86 37 50 25 12 25 82 74 e f 12 52 94 6 18 7 44 7 85 61 60 64 98 14 15 32 16 71 23 84 90 80 92 48 96 26 23 55 93 44 95 6 75 96 39 87 g g h j p q e e f 7 63 90 53 21 84 21 6 94 36 50 19 5 5 49 11 49 35 66 32 84 98 46 97 15 41 37 84 64 20 4 63 40 94 84 46 45 22 93 53 74 68 76 68 14 4 13 61 89 76 29 34 69 75 9 41 77 69 72 78 55 3 21 18 92 29 3 23 34 58 9 52 20 2 18 58 5 24 30 56 98 95 4 31 29 59 31 11 55 22 29 44 g h 28 47 95 13 17 19 20 42 18 68 66 64 35 12 33 34 37 49 17 j p 67 32 37 29 56 22 8 38 34 87 50 28 8 79 90 8 100 65 79 15 81 87 35 q 42 4 + E Assessment #1 Assessment #2 - Explore A B D E F F G H 1 J K L M N 0 0 P Q R R S S e 58 41 74 68 50 20 55 3 3 21 84 64 28 47 42 18 59 31 5 49 f 9 67 76 95 20 68 11 11 32 37 29 30 31 32 33 34 35 36 37 34 69 75 9 50 28 49 g h j p 68 14 65 79 53 37 49 17 95 13 17 19 18 92 29 3 3 6 32 55 22 29 44 52 79 90 8 8 100 55 96 q 8 18 58 5 5 24 30 56 98 79 68 87 57 72 100 23 13 16 4 a 42 63 56 57 b 49 91 68 11 43 37 56 18 77 18 66 64 35 12 78 4 4 52 10 61 7 24 64 C 15 81 87 39 23 73 34 9 51 3 4 d e e f 38 39 40 41 42 43 44 45 46 47 65 100 90 52 13 17 36 30 48 3 3 58 20 16 73 59 42 59 53 62 1 79 84 15 g h 88 56 26 65 100 93 91 47 50 5 30 84 52 61 22 33 54 42 11 1 100 64 46 46 Genex Capex Opex Genex Genex Capex Opex Genex Capex Opex Genex Genex Genex Genex Genex Genex Genex Genex Genex Genex Genex Genex Genex Genex Genex Genex Opex Opex Opex Opex Opex Opex Opex 57 a b 8 100 60 11 93 d d 44 63 20 87 52 11 29 8 38 34 87 99 86 21 21 58 86 81 29 71 70 28 5 5 48 70 85 14 94 3 13 70 52 75 3 8 1 47 9 100 48 9 9 79 35 47 e 61 1 50 24 54 80 34 34 67 32 5 97 23 58 a b 59 64 82 75 95 78 93 69 43 19 43 8 6 7 69 73 15 54 66 22 16 51 48 49 50 51 52 53 54 55 56 57 29 56 22 6 73 22 99 97 32 49 100 100 67 70 39 83 86 22 25 19 69 11 14 33 96 35 74 36 9 9 43 53 60 97 88 96 5 5 25 33 65 63 14 85 63 92 37 63 25 15 17 35 47 d 65 33 52 84 12 48 30 21 71 54 41 73 47 6 6 48 66 52 75 86 43 18 11 44 93 13 31 39 30 4 62 62 e f 64 33 88 27 48 56 5 100 52 88 22 58 93 46 18 56 4 g h 33 24 48 66 98 11 58 59 60 j 9 q 9 48 6 97 1 1 59 77 10 10 79 16 97 2 9 22 90 39 82 22 51 46 6 44 34 7 90 82 3 3 5 78 14 30 18 79 92 4 84 25 61 62 63 + Assessment #1 Assessment #2 - A1 JX A B D E F G H - #2 Calculate the Levered IRR of an investment with the following metrics - Show full cash flow (levered & unlevered) while coming to the IRR calculation Important note: Model should be fully parametric (should still work/calculate correctly when we change the parameters below) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Entry price Hold period Exit yield (Net Initial) Opex Genex Capex Bank Loan - LTV Bank Loan - Interest Rate pa Bank Loan - Amortization Bank Loan - Maturity - Corporate Income Tax Capital Gains Tax 87,500,000 5 years 5.75% (15.00%) of gross rental income (5.00%) of gross rental income (3.50%) of gross rental income 75.0% 3.0% per annum 5.0% per annum % 20 years 21.0% 10.0% 15 16 17 Rental Contracts for Revenue Estimates 18 Tenant Annual Rent Contract Duration 19 20 3 years 2 years Zara Apple Kaufland Coolblue Claudia Stratter Alphonso Dominguez 5 years Rent Reversionary at Contract Renewal 12.9% (7.5%) 10.0% 3.5% (1.7%) 5.0% 1,000,000 750,000 2,500,000 1,299,000 66,000 862,314 6 years 1 years 4 years 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 > + Assessment #1 Assessment #2
