Financial Modelling

[hardeepkt]

Hi all I need help to build a model.
I dont really know how to
[TABLE="width: 686"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Modelling Skills[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]We have 12 new sites coming on over the next 34 months (starting Feb 18 and finishing in Dec 20). We need to create a model to calculate our ‘onboarding fees’ for each site.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]We will be creating a mobilisation team to undertake this work and their costs need to be covered in full during this period. The take on of the properties will be phased but the team will need to be in situ from very early on in the project, so a formula to ensure that our costs are covered by the fees is quite difficult to achieve.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]All staff will be costed at a day rate (not on a salary basis) as this covers the costs of the office and support functions[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Each site will be in the onboarding period for 15 months only[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Onboarding fees will be charged monthly from the site start date (equally over the 15 month period)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A profit of 15% is to be incorporated into the fee calculations[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Analysis of costs and fees per Qtr will be required to ensure all costs are covered during each stage of the process. Qtr's run to Mar/Jun/Sept/Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The model needs to be able to cope with changes to day rates, unit numbers, month required and site dates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This model should be able to provide us with a basis for calculating our onboarding fees for any additonal sites[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Information Available[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staff required will be as follows :-[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Title[/TD]
[TD]Day Rate[/TD]
[TD]Month required[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Head of BtR / Mobilisation[/TD]
[TD="align: right"]£460[/TD]
[TD]Jan-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project Manager - 21 month contract[/TD]
[TD="align: right"]£260[/TD]
[TD]Jul-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project Manager Asst - 18 month contract[/TD]
[TD="align: right"]£170[/TD]
[TD]Oct-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobilisation manager - per day cost[/TD]
[TD="align: right"]£260[/TD]
[TD]Feb-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobilisation manager - per day cost[/TD]
[TD="align: right"]£260[/TD]
[TD]Jul-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobilisation assistant[/TD]
[TD="align: right"]£170[/TD]
[TD]Feb-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Residential Research Analyst[/TD]
[TD="align: right"]£200[/TD]
[TD]May-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Financial Modelling Assistant[/TD]
[TD="align: right"]£200[/TD]
[TD]Apr-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L&D Manager[/TD]
[TD="align: right"]£200[/TD]
[TD]Sep-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Recruitment / HR Manager[/TD]
[TD="align: right"]£200[/TD]
[TD]Jun-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bath & Bristol General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Dec-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]London General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Oct-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Leeds General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Nov-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Aug-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deansgate Square General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Jun-18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 1 General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Jan-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 2 General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Feb-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 3 General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Mar-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 4 General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]Apr-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 5 General Manager[/TD]
[TD="align: right"]£330[/TD]
[TD]May-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site Information[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site[/TD]
[TD]Start Date[/TD]
[TD]No of units[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A7 - Slate Yard[/TD]
[TD]Oct-19[/TD]
[TD="align: right"]199[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bath[/TD]
[TD]Feb-18[/TD]
[TD="align: right"]171[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deansgate Square[/TD]
[TD]Jun-18[/TD]
[TD="align: right"]350[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD]Nov-18[/TD]
[TD="align: right"]220[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]Jan-19[/TD]
[TD="align: right"]479[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Leeds[/TD]
[TD]Feb-19[/TD]
[TD="align: right"]247[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bristol[/TD]
[TD]Mar-19[/TD]
[TD="align: right"]375[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 1[/TD]
[TD]Apr-19[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 2[/TD]
[TD]May-19[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 3[/TD]
[TD]Jun-19[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 4[/TD]
[TD]Jul-19[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Site No 5[/TD]
[TD]Aug-19[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Cross posted https://chandoo.org/forum/threads/excel-modelling.38691/

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.