The aim of this project was learn how to use scientific and mathematical methods to provide an optimum solution to a realistic financial problem. In order to do this the information has to be put together in a structured way so that the desired outcome can be maximized by altering inputs subject to a series of constraints. To find the best possible combination of allocations without an optimizing tool like Solver would be almost impossible. The key steps were firstly understanding the problem, then formalizing it in a way that it could be solved using an optimizing tool. Having done this the requirement was to present the whole project in a consultancy type format.
Vista Properties has purchased 140,000 square metres of land on which a shopping centre is to be built, and it has an option to buy an additional 20,000 square metres of adjacent land. It wants to know how to allocate the space on the land it has already purchased and whether this option should be exercised.
The problem regarding allocation is one of choosing the mix of shop types and floor areas will give the best financial results over a 7 year period. This has to be done within several constraints on minimum and maximum sizes of shops and financial targets.
The Midvale Shopping Centre project has already been agreed, so this was tackled first. The option to purchase 20,000 square metres of adjacent land was then tackled.
The issue was how to allocate the floor-space available between the various types of shop in a way that would be most beneficial to Vista Properties. The method recommended is based on finding the highest level of net present value (NPV) less costs not already included in net present value. That is, less improvement and construction costs. This is referred to as profit in the remaining text. The net present value figures supplied include fixed charges. Mr Wasser had challenged whether fixed charges should be included. He was quite right to do so as the model assumes that all the component charges vary with floor space within each shop type. Fixed charges are however constant and should be modelled as such.
I order to calculate the fixed charges that have been included, the net present value of 7 years fixed cost was found to be £608,522. This was divided by floor area of 45,000 as it is assumed that the accountant would have done this. This gave a figure of £13.52 to add back for each shop type. In Excel a column was set up with the size of floor space available to allocate to each shop type. This is the column that Solver could change to achieve the best profit, subject to the various constraints. The profit was found by first multiplying all floor space allocations by the adjusted NPV’s and then subtracting construction and improvement costs. Construction costs were £6 times the total floor space in metres. Improvement costs were the sum of each shop floor space times the individual shop type cost of improvement.
Constraints were applied as follows:
The sum of floor-spaces within each group must be greater than the group minimum. For example in Group A the sum of Supermarket an Dept. Store floor space had to be greater than the group total of 20. Supermarket an Dept. Store floor space had to be individually more than 10 000 sq mtrs each.
The maximum floor space was constrained for each shop type according to the supplied table.
In evaluating the option, the available floor space not allocated above was allocated optimally with Solver. It was assumed that the minimum requirements had been satisfied by the above phase.
The first thing to set in Solver was the cell to be maximised, which was the one with NPV less improvement and construction costs. It also had to be told that this was to be maximised. The next thing was to tell it the cell range that it was allowed to alter in order to produce a maximum solution.
The constraints were then added to the Solver tool in Excel. This involve in each case telling it the cell that was to be above or below a certain value, which could be a number or another cell. For example the cell representing the sum of the floor-space was told that this must be less or equal to the cell containing the 45 for the maximum amount that could be allocated. Having set up the cell to maximize, the cells that could be changed and the constraints as described above, the Solver tool was told to produce a solution. The dialogue indicated that it could produce a solution meeting all the criteria and this was an optimal value. Like all solutions of this type it can only be as good as the input and relies on accurate data and assumptions such as that all space allocated would be taken up with no breaks in tenancy.
Also there was the possibility of extra revenue when sales exceeded targets, but there was no information to enable this to be used.
The allocations for the Meadvale Project are dominated by the ones with the highest profit potential which are the Supermarket and the Department Store.
The remaining space available under local planning rules would therefore be less profitable. In this case, although the optimum mix has been obtained it does not represent a great return on the Capital Outlay of £200,000. However, the company had experienced large capital gains on previous projects and would need to consider whether this would justify going ahead as the project would at least return a small profit.
Appendix 1: Midvale
Group |
No |
Type of Shop |
Cost of interior improvements (a) |
Present Value(a) plus fixed |
Guarantee Rent (a) |
Group min(b) |
Shop max(b) |
Size (a) |
A |
1 |
Supermarket |
9 |
73.52 |
3.2 |
20 |
20 |
20.0 |
A |
2 |
Dept Store |
13 |
93.52 |
4.1 |
0 |
20 |
20.0 |
B |
3 |
Shoes |
12 |
58.52 |
3 |
0 |
0.9 |
0.0 |
B |
4 |
Women's clothing |
8 |
63.52 |
3.2 |
0 |
3 |
0.0 |
B |
5 |
Men's Clothing |
7 |
61.52 |
3.2 |
0 |
2 |
0.0 |
C |
6 |
Opticians |
6 |
63.52 |
3 |
2 |
4 |
3.0 |
C |
7 |
Chemists |
7 |
59.52 |
3.1 |
0 |
1.6 |
0.0 |
D |
8 |
Gift |
8 |
48.52 |
2.5 |
2 |
3 |
0.0 |
D |
9 |
Mobile phone |
9 |
63.52 |
2.4 |
0 |
1.3 |
1.3 |
D |
10 |
Café |
10 |
53.52 |
2.6 |
0 |
1.5 |
0.0 |
D |
11 |
Music |
7 |
59.52 |
2.3 |
0 |
1.5 |
0.7 |
D |
12 |
Bakery |
11 |
48.52 |
3 |
0 |
1 |
0.0 |
(a) £ per sq mtr |
||||||||
(b) 000's sq mtrs |
||||||||
SIZE CONSTRAINTS |
Allocated |
Min(a) |
Max(a) |
|||||
Total Square metres |
45.0 |
45 |
||||||
Group A |
40.0 |
2 |
||||||
Group B |
0.0 |
0 |
||||||
Group C |
3.0 |
2 |
||||||
Group D |
2.0 |
2 |
||||||
Shop 1 |
20.0 |
10 |
20 |
|||||
Shop 2 |
20.0 |
10 |
20 |
|||||
INCOME (£000) |
£ 000 |
|||||||
Minimum Rent |
£ 1,118.11 |
Must exceed Improvements + interest |
||||||
Sales excess |
£ - |
|||||||
Other |
£ - |
|||||||
Total income |
£ 1,118.11 |
|||||||
Present value |
£ 3,655.60 |
|||||||
COSTS |
£ 000 |
|||||||
Fixed |
£125 |
|||||||
Construction |
270 |
|||||||
Improvements |
475 |
|||||||
Interest on improvements |
332 |
|||||||
Improvements + interest |
807 |
|||||||
0 |
||||||||
Total costs excluding fixed |
1,077 |
|||||||
COST CONSTRAINTS |
||||||||
Improvements |
Les than |
450 |
||||||
PROFIT (PRESENT VALUE LESS COSTS) |
2,579 |
|||||||
NPV of 7 year fixed |
608.55 |
|||||||
Nett profit |
1,970 |
|||||||
Appendix 2: Options
Group |
No |
Type of Shop |
Cost of interior improvements (a) |
Present Value(a) plus fixed |
Guarantee Rent (a) |
Group min(b) |
Shop max(b) |
Size (a) |
A |
1 |
Supermarket |
9 |
73.52 |
3.2 |
0 |
0.0 |
0.0 |
A |
2 |
Dept Store |
13 |
93.52 |
4.1 |
0 |
0.0 |
0.0 |
B |
3 |
Shoes |
12 |
58.52 |
3 |
0 |
0.9 |
0.0 |
B |
4 |
Women's clothing |
8 |
63.52 |
3.2 |
0 |
3.0 |
3.0 |
B |
5 |
Men's Clothing |
7 |
61.52 |
3.2 |
0 |
2.0 |
2.0 |
C |
6 |
Opticians |
6 |
63.52 |
3 |
0 |
1.0 |
1.0 |
C |
7 |
Chemists |
7 |
59.52 |
3.1 |
0 |
1.6 |
0.0 |
D |
8 |
Gift |
8 |
48.52 |
2.5 |
0 |
3.0 |
0.0 |
D |
9 |
Mobile phone |
9 |
63.52 |
2.4 |
0 |
0.0 |
0.0 |
D |
10 |
Café |
10 |
53.52 |
2.6 |
0 |
1.5 |
0.0 |
D |
11 |
Music |
7 |
59.52 |
2.3 |
0 |
0.8 |
0.0 |
D |
12 |
Bakery |
11 |
48.52 |
3 |
0 |
1.0 |
0.0 |
(a) £ per sq mtr |
||||||||
(b) 000's sq mtrs |
||||||||
SIZE CONSTRAINTS |
Allocated |
Min(a) |
Max(a) |
|||||
Total Square metres |
6.0 |
6 |
||||||
Group A |
0.0 |
0 |
||||||
Group B |
5.0 |
0 |
||||||
Group C |
1.0 |
0 |
||||||
Group D |
0.0 |
0 |
||||||
Shop 1 |
0.0 |
0 |
0.0 |
|||||
Shop 2 |
0.0 |
0 |
0.0 |
|||||
INCOME (£000) |
£ 000 |
|||||||
Minimum Rent |
£ 133.00 |
Must exceed improvements + interest |
||||||
Sales excess |
£ - |
|||||||
Other |
£ - |
|||||||
Total income |
£ 133.00 |
|||||||
Present value |
£ 377.12 |
|||||||
COSTS |
£ 000 |
|||||||
Fixed |
£29 |
|||||||
Construction |
36 |
|||||||
Improvements |
44 |
|||||||
Interest on improvements |
31 |
|||||||
Improvements + interest |
75 |
|||||||
0 |
||||||||
Total costs |
215 |
|||||||
COST CONSTRAINTS |
||||||||
Improvements |
Les than |
450 |
||||||
PROFIT (PRESENT VALUE LESS COSTS) |
163 |
|||||||
NPV of 7 year fixed |
141 |
|||||||
Nett profit |
22 |
Appendux 3: Formulas Midvale Cols A to E
<
Group |
No |
Type of Shop |
Cost of interior improvements (a) |
Present Value(a) plus fixed |
A |
1 |
Supermarket |
9 |
73.52 |
A |
2 |
Dept Store |
13 |
93.52 |
B |
3 |
Shoes |
12 |
58.52 |
B |
4 |
Women's clothing |
8 |
63.52 |
B |
5 |
Men's Clothing |
7 |
61.52 |
C |
6 |
Opticians |
6 |
63.52 |
C |
7 |
Chemists |
7 |
59.52 |
D |
8 |
Gift |
8 |
48.52 |
D |
9 |
Mobile phone |
9 |
63.52 |
D |
10 |
Café |
10 |
53.52 |
D |
11 |
Music |
7 |
59.52 |
D |
12 |
Bakery |
11 |
48.52 |
(a) £ per sq mtr |
||||
(b) 000's sq mtrs |
||||
SIZE CONSTRAINTS |
Allocated |
Min(a) |
||
Total Square metres |
=SUM(I2:I13) |
|||
Group A |
=SUM(I2:I3) |
2 |
||
Group B |
=SUM(I4:I6) |
0 |
||
Group C |
=SUM(I7:I8) |
2 |
||
Group D |
=SUM(I9:I13) |
2 |
||
Shop 1 |
=+I2 |
10 |
||
Shop 2 |
=+I3 |
10 |
||
INCOME (£000) |
£ 000 |
|||
Minimum Rent |
=SUMPRODUCT(F2:F13,I2:I13)*7 |
|||
Sales excess |
0 |
|||
Other |
0 |
|||
Total income |
=SUM(E32:E34) |
|||
Present value |
=SUMPRODUCT(E2:E13,I2:I13) |
|||
COSTS |
£ 000 |
|||
Fixed |
125 |
|||
Construction |
=+D20*6 |
|||
Improvements |
=SUMPRODUCT(D2:D13,I2:I13) |
|||
Interest on improvements |
=+E43*0.1*7 |
|||
Mathematical methods in finance solutions. (2017, Jun 26).
Retrieved October 13, 2024 , from
https://studydriver.com/mathematical-methods-in-finance-solutions/
A professional writer will make a clear, mistake-free paper for you!
Get help with your assignmentPlease check your inbox
Hi!
I'm Amy :)
I can help you save hours on your homework. Let's start by finding a writer.
Find Writer