General Instructions


It is important that students complete this assignment on their own.  College plagiarism rules apply to this assignment.



Part 1: Visio (35%)


Tutor Note: Microsoft Visio should be accessed using the Virtual Study PC for this part of the assignment.


The objectives of this question are:

  • to utilise your skills in the interpretation of written data into a graphical representation that demonstrates the logic and order of the process
  • to provide a platform for you to demonstrate your MS Visio skills


You are a Project Manager working in the property team of Barclays Bank. The team manages and maintains circa 1,750 properties in the UK for the various divisions of the bank (personal banking, small business banking, corporate banking etc.). The personal banking branch network is the largest part of the estate. There are currently circa 1,000 branches, but with an increasing number of customers preferring to use other banking channels (telephone, internet and mobile platforms) the bank’s management wants to know at the earliest opportunity which properties could be exited or downsized.


The Barclays property team is responsible for identifying this estates information. This is achieved by identifying property where the lease is about to expire (or have an upcoming break clause) and those branches that can be partly sub-let to reduce occupied space. Your senior manager has asked you to create a process map (flowchart) of the various activities involved in creating this information.  You have attended a meeting and took down the following notes:


Background: A few years ago the property team decided to outsource its Property Services, Capital Projects and Facilities Management functions to specialist companies. The Barclays property team is responsible for understanding what the various departments want to achieve and to manage/instruct the 3 outsourced organisations.  The Property Services function has been outsourced to Savills, a major firm that deals with property leases, rent renewals and property acquisitions for clients.  The Capital Projects function has been outsourced to Ridge & Partners, who have project managers, designers and quantity surveyors that work on fitting out projects.  The Facilities Management has been outsourced to Mitie which manages the cleaning services in the offices, day to day maintenance and carries out all churn (move) management.


Process: In their monthly report to the property team, Savills identifies the names of properties that have lease breaks or termination options coming up in the next 36 months.  The Barclays property team reviews this list and meets with the occupying business unit to assess their future business plans.  They may decide to stay in the building or look at alternative properties or close the branch.  If they wish to stay in the current building, the property team instructs Savills to renegotiate the lease renewal. If the business decides to exit the branch, Savills will negotiate with the freeholder to agree a payment for dilapidations. In both of these options, Ridge & Partners and Mitie have no involvement.


If the business wants to look at alternative properties (perhaps something smaller), Savills are instructed to find alternative properties in the locality, Ridge & Partners are instructed to produce a Feasibility Report (involving space planning of floors, costs, schedule and suitability of premises) and Mitie a Migration Report (involving the impacts on moving the branch bank). The Ridge & Partners Feasibility Report and Mitie Migration Report are passed to Savills.  Savills obtain an estimate of the lease costs from the Agents and produce a discounted cashflow of each option and issue all the reports to the property team.

At the same time, the Barclays Property Team will review the existing properties that the business has decided to stay in to establish if they can be downsized. If there are any that can be downsized, 


The Barclays Property Team discuss the alternate properties and downsizing opportunities with the business units.  If they decide to stay in the current building, the property team instruct Savills to renegotiate the lease renewal.  If they decide to relocate to a new building, the property team instructs Savills to acquire the lease, Ridge & Partners are appointed to carry out the building works and Mitie to manage the move.



Your flowchart should be kept to a maximum of 20 boxes, show links for options at each stage and clearly show which organisation does what activity.


Marks are awarded for:

  1. interpretation of the information and accuracy of the logic in the process map (12%)
  2. overall design/layout of the process map (8%)
  3. Visio skills used (15%)



Part 2: Excel (35%)


The objectives of this question are:

  • to build a new spreadsheet suitable for a third party to use (spreadsheet design skills)
  • to demonstrate spreadsheet design/layout & formatting skills
  • and to demonstrate the application of formulae and logic


You are a Project Manager working for Arcadis. A developer of student accommodation blocks has approached your company to assist with the evaluation of some development sites across the country. The developer is looking at various plots of land that will allow buildings to be designed having different size of floor plates and storey heights. The Developer has asked that you develop a spreadsheet which will calculate the floor area of the blocks when the Developer inserts the 3 key variables of number of bedrooms, number of floors and whether the facility is to be catered or self-catering.


Produce a spreadsheet that fits onto a single sheet of A4. The spreadsheet should be designed so that it can be used by the developer to experiment with different variables and see the resulting total floor area. The formulas and workings should be carefully laid out below this.






 Quantity  Floor Area
Bedroom (all singles) Defined as variable 11 m2
En-suite shower room Per bedroom 4 m2
Bathroom 1 per 20 bedrooms 6 m2
Kitchenette/lounge If catered:

1 per 20 bedrooms If self-catered:

1 per 6 bedrooms


18 m2


20 m2

Laundry Facility If under 199 bedrooms

If 200 to 299 bedrooms

If over 300 bedrooms

30 m2

35 m2

40 m2

Central Catering


If catered:

If self-catered:

240 m2

0 m2

Common Lounge If under 199 bedrooms

If 200 to 299 bedrooms

If over 300 bedrooms

225 m2

200 m2 plus 0.4 m2 per bedroom

200 m2 plus 0.3 m2 per bedroom

Plant room, risers, cleaners stores, lifts Per floor 30 m2
Security/Reception Area   65 m2
Circulation space   15% of all the total of the above areas


Show the total floor area rounded upwards to the nearest 100m2.


Marks are awarded for:

  1. Design of the spreadsheet to meet the needs of the user (10%)
  2. Excel formatting skills (5%)
  3. Formula use and accuracy of the answer (20%)



Part 3: Excel (30%)


The objectives of this question are:

  • to build a new spreadsheet from a set of instructions (spreadsheet design skills)
  • to devise a cashflow forecast with appropriate formulas
  • demonstrate ability to interpret data and place it into correct years
  • to provide a platform for you to demonstrate your MS Excel skills



You work for Barclays Bank and you have been asked to compare two property solutions so one can be chosen for a new retail branch bank.  One option is a freehold property; the other is a leasehold property.  In order to inform the selection decision, you are to compile a spreadsheet showing the costs from an assumed start of July 2021, the running costs over a 20 year life-cycle (i.e. to 2041), identify the costs per year and net present value (NPV) of both schemes.


Both schemes are 700 m2 in total, allowing 450 m2 for front of house and 250 m2 for back of house.  VAT is assumed to be 20% for the whole period. Barclays Finance has notified you that its internal charge for capital is 6% (which is higher than market rates due to Barclays ability to earn more on the money market with its capital). Both solutions are to be fitted out in time for the branch to open on 1st December 2021.


Option 1: Freehold Property


The freehold cost is £3 million.  Legal fees are 2.5% of the freehold cost and the agents fees are 1.3%.  Stamp duty is calculated based on 5% of the assumed lettable costs for the first year (in this case the property manager has advised you should use £365/m2 per annum for this calculation).  Business rates are assessed at £25,000 p.a. (in 2021) and you have been asked to inflate this figure by 3% each year.


To fit-out the branch, the QS advises that you should allow £1,650/m2 for the front of house (the bank branch) and £1,000/m2 for the back of house (staff room, toilets, conference room, storage).  As it is a public facing facility, the front of house part will require to be refurbished every 7 years and the back of house part every 14 years.  The QS suggests that you should use the same rates /m2 but include for construction inflation at 6% per annum. Professional fees are to be allowed for at 11% of total construction costs.


To clean the building and provide facilities management, the FM company charges £125/m2 p.a. for the service.  This is to be inflated by 4% per annum.  An allowance for internal repairs is based on £5,000 in 2022 as a nominal sum to cover minor damage and from 2023, an allowance of £50/m2 for front of house and £10/m2 for back of house with a 5% inflation per annum for all future years.  However, when the offices are refurbished, the allowance that year will be nil (£0) and the year afterwards will be the values used in the year before the refurbishment plus inflation.


Being a freehold property, after 20 years the property will still be owned by Barclays Bank and the property manager suggests that the value of commercial property in the area increases from the purchase price by 2.5% per annum.


Option 2: Leasehold Property


The property manager is confident of negotiating a lease on good terms and believes the Landlord will agree to offer a £400,000 contribution to the cost of the fitting out of the branch and the period to the end of 2021 will be rent free. The following three years (2022 to 2024) the rent will be at £200/m2, rising 5% at 2025, then at the same rate until the next rise of 5% at 2029, then the same rate until another 5% rise at 2032 with no further increases to the end of the 20 year lease. The Landlord’s Contribution will be offset against each years lease cost until exhausted.


Agents fees are a lump sum of £15,000 and the legal fees a lump sum of £40,000.  Refurbishment costs, professional fees, stamp duty, business rates, FM costs, repair costs are the same costs and frequencies as option 1.


Being a leasehold property, the Bank does not own the building and therefore will not benefit in any rise in the property value over time. We are to assume that at the end of the lease Barclays Bank will exit the property and you should allow for a dilapidation cost of £300/m2 (at 2021 rates increasing at 5% per annum) to reinstate the building back to the pre-let condition.



Create a clear spreadsheet based on this information, showing the costs of each option per annum over the whole 20-year period and the NPV for each option.


If you make any assumptions, please make these clear in your answer.


Marks are available for good spreadsheet design, formatting skills, accuracy of placing costs in each year and use of variety of formulas.


Marks are awarded for:

  1. Design of the cashflow spreadsheet (6%)
  2. Excel formatting skills (4%)
  3. Formula use, accuracy of the placing of costs in the correct periods and accuracy of the overall answer (20%)



Submission Instructions


The files should be saved as ‘your last name’ and the part number (e.g. smithPart1).


Please note that the files will be marked using the PC versions of Excel and Visio available on the Virtual Study PC. Visio is not available on iOS but if you use an iOS version of Excel for Parts 2 and 3, it is recommended that you import these into the Virtual Study PC, check that everything has imported correctly and then save it as a PC version before submitting for marking.


There is NO requirement to submit this assignment to Turnitin or to hand in a hard copy to the office.


The submission process is:


  1. Upload the 3 files onto Moodle, so your assignment is officially time and date stamped. You must do this before the deadline of


15:00 pm on Tuesday 4th May 2021


  1. After submitting it on to Moodle, you should email all 3 files to Peter O’Connor at


p.o’connor@ucl.ac.uk for marking.



By sending in these files for marking you are confirming that this assignment is entirely your own, unaided work.  If this is not the case you must not send in any work but notify your tutor accordingly.