Excel.. How to solve this ( Maths) in Excel ( FINANCIAL )

[ishan01us]

Hi Friends,
I need your help.
I am learning excel and i have a excel file with few instructions.
Can any one able to solve this, & understand me - how we will solve it. it will be a great help.
Please find the attached excel(Math-Finance file). I tried to solve but I am not sure ,what i did and it seems its not matching


Please solve & kindly help me to understand.

I am not able to find the attached file options _


[TABLE="class: cms_table, width: 223"]
<tbody>[TR]
[TD="colspan: 3"]Rebate Calculation Sample:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 719"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gross Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]From[/TD]
[TD="colspan: 2"]To[/TD]
[TD]% Rebate[/TD]
[TD]$[/TD]
[TD]Rebate[/TD]
[TD="colspan: 2"]Net[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD="align: right"]25,000.00[/TD]
[TD="align: right"]0%[/TD]
[TD]$[/TD]
[TD]-[/TD]
[TD]$[/TD]
[TD="align: right"]25,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$[/TD]
[TD="align: right"]25,001.00[/TD]
[TD]$[/TD]
[TD="align: right"]50,000.00[/TD]
[TD="align: right"]10%[/TD]
[TD]$[/TD]
[TD="align: right"]2,500[/TD]
[TD]$[/TD]
[TD="align: right"]22,499.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$[/TD]
[TD="align: right"]50,001.00[/TD]
[TD]$[/TD]
[TD="align: right"]100,000.00[/TD]
[TD="align: right"]15%[/TD]
[TD]$[/TD]
[TD="align: right"]7,500[/TD]
[TD]$[/TD]
[TD="align: right"]42,499.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$[/TD]
[TD="align: right"]100,001.00[/TD]
[TD]$[/TD]
[TD="align: right"]200,000.00[/TD]
[TD="align: right"]20%[/TD]
[TD]$[/TD]
[TD="align: right"]20,000[/TD]
[TD]$[/TD]
[TD="align: right"]79,999.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$[/TD]
[TD="align: right"]200,001.00[/TD]
[TD]$[/TD]
[TD="align: right"]500,000.00[/TD]
[TD="align: right"]25%[/TD]
[TD]$[/TD]
[TD="align: right"]75,000[/TD]
[TD]$[/TD]
[TD="align: right"]224,999.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$[/TD]
[TD="align: right"]500,001.00[/TD]
[TD]$[/TD]
[TD="align: right"]1,000,000.00[/TD]
[TD="align: right"]30%[/TD]
[TD]$[/TD]
[TD]-[/TD]
[TD]$[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$[/TD]
[TD="align: right"]105,000[/TD]
[TD]$[/TD]
[TD="align: right"]394,996.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 1056"]
<tbody>[TR]
[TD="colspan: 16"]Q. 1: Create a spreadsheet to make this calculator work where the Gross Fee is the only input to calculate the Rebate and Net. Model needs to work for any Gross Fee input value.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 15"]Q 2: Instead of using the visuals above, create a single formula field to calculate the Rebate and one to calculate the Net but referencing only the Gross Fee. Single formula means Rebate = Gross Fees – [THE FORMULA] and Net = Gross Fees – [THE FORMULA].[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]Q 3: Using Test 1 and 2, calculate Rebate and Net from the Gross Fee of 9,542,332.56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 14"]Help: Model needs to work for any value.
Single formula means Rebate = Gross Fees – [THE FORMULA] and Net = Gross Fees – [THE FORMULA].


==================[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2nd questions :


[TABLE="class: cms_table, width: 631"]
<tbody>[TR]
[TD]Lease Terms:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lease Commencement[/TD]
[TD]01-01-15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lease Expiration[/TD]
[TD]31/12/19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area (SqFt)[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Base Rent (Per Month)[/TD]
[TD]$55,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OPEX (Per Month)[/TD]
[TD]$5,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Base Rent Growth Rate[/TD]
[TD]3% pa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Opex Growth Rate[/TD]
[TD]2% pa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]- First 2 months are free rent months[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]- Calculate the Cash Flow for the lease term[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Help: Show Total Obligation and Avg Cost / SF per year and for the whole term[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]