Financial Modelling

hardeepkt

New Member
Joined
May 26, 2018
Messages
2
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]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top