1.0. Decision Analysis
14

Decision Analysis
Indecision analysis, a company is presented with the set of alternativeactions and may decide to choose whichever it please (Götze et al.2008, p. 13)

The three alternative actions Dreamcatcher can choose are certainty, ignorance or risk

Maximax optimistic action

Thisentails selecting the best strategy based on maximum possible returnassociated with the decision. The maximax decision is selected. InDreamcatcher, the decision with the maximum return is 21,900, acomponent of International Expansion optimistic.
Project / Scenario 
Pessimistic 
Average 
Optimistic 
Maximum 
International Expansion 
7,500 
13,800 
21,900 
21,900 
Domestic Expansion 
8,600 
14,000 
19,500 
19,500 
No Change 
11,000 
12,300 
16,700 
16,700 
Probability 
25% 
55% 
20% 

Maximin pessimistic action
Thisentails selecting the best of the worst strategy through evaluationof each decision by minimum possible expected return. The decisionthat yields maximum of the evaluated minimum returns is normallyselected. This is mostly carried as a protection strategy and takesinto consideration the waste case scenario.
Project / Scenario 
Pessimistic 
Average 
Optimistic 
Maximin 
International Expansion 
7,500 
13,800 
21,900 
7,500 
Domestic Expansion 
8,600 
14,000 
19,500 
8,600 
No Change 
11,000 
12,300 
16,700 
11,000 
Probability 
25% 
55% 
20% 
Therefore,11,000 is the maximin and a component of ‘No Change’ pessimistic.

Risk
Decisionmaker, in this case, Dreamcatcher, knows all the possible states ofnature and may easily assign probability of occurrence for everystate. This makes use of the expected rate of return. The alternativewith the highest expected rate of return can be chosen or theExpected Value (EV) or Expected Monetary Value (EMV) can be used.
Thetable below presents the formula’s used in calculating the expectedrate of return.
A 
B 
C 
D 
E 

1 

2 
Project / Scenario 
Pessimistic 
Average 
Optimistic 
 Expected Return (ER) 
3 
International Expansion 
7,500 
13,800 
21,900 
=SUMPRODUCT(B3: D3,$B$6:$D$6) 
4 
Domestic Expansion 
8,600 
14,000 
19,500 
=SUMPRODUCT(B4: D4,$B$6:$D$6) 
5 
No Change 
11,000 
12,300 
16,700 
=SUMPRODUCT(B5: D5,$B$6:$D$6) 
6 
Probability 
25% 
55% 
20% 

 7 
Best Decision 

8 
  
  
  
  

9 
  
  
  
EVwPI 
=SUMPRODUCT(B7:D7,B6:D6) 
10 
  
  
  
EV Best 
=MAX(E3:E5) 
  
  
  
EVPI 
=E9E10 
Thevalues are as presented below. Either, Dreamcatcher can decide topick international expansion as it has the highest expected rate ofreturn, valued at 13,845.
A 
B 
C 
D 
E 

1 

2 
Project / Scenario 
Pessimistic 
Average 
Optimistic 
  Expected Return (ER) 
3 
International Expansion 
7,500 
13,800 
21,900 
13845 
4 
Domestic Expansion 
8,600 
14,000 
19,500 
13750 
5 
No Change 
11,000 
12,300 
16,700 
12855 
6 
Probability 
25% 
55% 
20% 
  
 7 
Best Decision 
11,000 
14,000 
21,900 
  
8 
  
  
  
  
  
9 
  
  
  
EVwPI 
14830 
10 
  
  
  
EV Best 
13845 
  
  
  
EVPI 
985 

Recommendation
Irecommend the use of Expected Value of Perfect Information (EVPI) inmaking their decisions. EVPI is a will enhance measuring of how muchbetter Dreamcatcher can do on any decision when the occurrence ofeach state of nature is known. EVPI is critical when the companyneeds to know the amount that it is willing to pay for the perfectinformation. EVPI also depicts the upper limit to which Dreamcatchercan pay for any additional imperfect information. Therefore, from thetable above, expected value of the best action using the imperfectinformation (EV_{Best})is 13,845, which falls under international expansion.

Case Company: Manufacturing of amplifiers
Acompany manufacturing amplifiers is sought for the bestselling linethat comprises of three specification levels of cheap that sells at£75,Average that sells at £ 150 and premium that sells at £375. Eachamplifier goes through forming, machining, assembling and testingprocesses.

The results are presented in the table below.
ProcessMachine 
Cheap 
Average 
Premium 
Cost per hour 
Profit 
Max. Available 
Forming 
0.5 hours 
0.75 hours 
2.25 hours 
£4.50 
2,250 hours 

Machining 
1.75 hours 
3.5 hours 
9 hours 
£6.25 
8,750 hours 

Assembly 
0.25 hours 
0.75 hours 
2 hours 
£7.75 
3,250 hours 

Testing 
0.25 hours 
0.5 hours 
1.5 hours 
£10.00 
2,375 hours 
Objective
MaximizeProfits
Constraints

Hours of forming not to exceed 2,250hrs

Hours of machining not to exceed 8,750hrs

Hours of Assembly not to exceed 3,250hrs

Hours of testing not to exceed 2,375hrs
Thedecision variables representing actual decisions can be defined as

C for Cheap

A for Average

P for Premium
ProcessMachine 
Cheap (Hours) 
Average (Hours) 
Premium (Hours) 
Cost per hour(£) 
Max. Available (Hours) 
Forming 
0.5 
0.75 
2.25 
4.5 
2,250 
Machining 
1.75 
3.5 
9 
6.25 
8,750 
Assembly 
0.25 
0.75 
2 
7.75 
3,250 
Testing 
0.25 
0.5 
1.5 
10 
2,375 
Selling Price (£) 
75 
159 
375 
  
  
Cost of Production (£) 
17.625 
36.0625 
96.875 
  
  
Profit (£) 
57.34 
122.94 
278.13 
  
  

Cost of production per specification (£) = (Forming hours x 4.5) + (Machining hours x 6.25) + (Assembly hours x 7.75) + (Testing hours x 10)

Profit per Specification (£) = Selling price/specification (£) – Cost of Production (£)
Creatingan LP objective function in terms of C, A and P.
Maximizeprofit = 57.34C + 122.94A + 278.13P
Developmentof Mathematical Relationships
Inthis case, the amount of resource used should be less than or equalto the resource available.

Forming time used:

Machining time used:

Assembly Time:

Testing Time:

Using Excel Solver to solve the LP and calculating the maximum profit
Cheap 
Average 
Premium 
Total Profits/Hours 
Maximum Capacity 

Decision variable 
0 
2500 
0 

Profits 
57.34 
122.94 
278.13 
307350 

Forming 
0.5 
0.75 
2.25 
1875 
2250 
Machining 
1.75 
3.5 
9 
8750 
8750 
Assembly 
0.25 
0.75 
2 
1875 
3250 
Testing 
0.25 
0.5 
1.5 
1250 
2375 
Byusing solver, maximum profit will be realized when 2,500 of cheap areproduced to give £307,350 and the maximum operating hours forforming are 1,875hours, machining will take 8,750 hours assembly willtake 1,875hours and testing will take 1,250hours.

Changes when:

Maximum available Machining hours were 10,500
Cheap 
Average 
Premium 
Total Profits 
Maximum Capacity 

Decision variable 
0 
3000 
0 

Profits 
57.34 
122.94 
278.13 
368820 

Forming 
0.5 
0.75 
2.25 
2250 
2250 
Machining 
1.75 
3.5 
9 
10500 
10500 
Assembly 
0.25 
0.75 
2 
2250 
3250 
Testing 
0.25 
0.5 
1.5 
1500 
2375 
Whenmaximum available hours for machining are 10,500, maximum profit willbe realized by producing 3000 pieces of average amplifiers to realizea profit of £368,000. The maximum hours for forming will be 2,250,assembly will take 2,250 hours and testing will take 1,500 hours.This is an increase of 500 pieces of average amplifier at a value of£60,500.

If £200 is charged for average amplifier, then the profit realized will be £163.94
Cheap 
Average 
Premium 
Total Profits 
Maximum Capacity 

Decision variable 
0 
2500 
0 

Profits 
57.34 
163.94 
278.13 
409850 

Forming 
0.5 
0.75 
2.25 
1875 
2250 
Machining 
1.75 
3.5 
9 
8750 
8750 
Assembly 
0.25 
0.75 
2 
1875 
3250 
Testing 
0.25 
0.5 
1.5 
1250 
2375 
Byincreasing the profit value of average amplifiers to £200, profitrealized will increase from £307,500 to £409,850, an increment of£102,350. The hours for the operations processes will remainunchanged at 1,875 for forming, 8,750 for machining, 1,875 forassembly and 1,250 for testing.

Marketing Plan and Production Mix
Forthe LP analysis, it is clear that increasing the price of theproducts results to increase in profits. Also, the increase inmaximum operating hours enhances profitability. As a result, I wouldrecommend that the company consider reviewing their selling prices.This should be on the higher side but realistic. Also, I recommend anincrease in maximum operational hours for machining to be increasedwhile those for forming, testing and assembly should be reduced.
Question3: Mortgage
CurrentSituation
Adam’scurrent salary= £49,000 p.a.
Eve’s current salary is£42,500 p.a. + approximate bonus of £7,500
Adam and Eve totalsavings=£45,000
Value of the flat= £185,000
Mortgage outstanding on flat=£124,000
Mortgage duration=25years
Price for the flats: studios£220,000 1bedroom £330,000 2bedroom £440,000 3bedroom£575,000 4bedroom £750,000
Calculation Assumption
Monthly saving for Adam andEve= £500

Offset Mortgage: Case Barclays Bank, UK
(http://www.barclays.co.uk/Mortgage/Ourmortgagerates/P1242562266844accessed 4th March 2015)
Anoffset mortgage refers to the concept of calculating and charginginterest where the mortgage funds are offset by the savings availablefor an individual. Normally, in mortgage, the lender calculates theinterest based on the amount borrowed but in the offset mortgage, thereduction of the amount is based on the amount that is held withinthe linked accounts.

Lowest charges currently by Barclays for Offset mortgage
Barclaysbank is charging the lowest offset rate of 1.69% and 1.19% for twoyears.

Lowest rate, application fees (if applicable) and the respective period the bank is charging for a fixed rate mortgage
Thelowest rate is 1.79% with follow at interest rate of 3.99% that isinclusive of Barclays Bank Base Rate (BBBR) and 3.49%, variable forremaining term. The application fee is £999.

Amount that Adam and Eve should borrow and the size of the flat to buy
Byusing Barclays Bank mortgage calculator, Adam is the first applicantwhose annual income is £49,000 and Eve, the second applicant has anannual salary of £42,500 plus an additional £7,500 that makes it£50,000.
Therefore,Adam and Eve can buy a flat of value £444,510, this a twobedroomedflat from the specifications given. The cost of 2bedroomed flat is£440,000

The best mortgage for Adam and Eve based on either the offset or fixed rate. The two rates identified in a) are 1.69% and 1.79% by taking £400,000 recommended in b).
Usingthe mortgage calculator
OffsetRate: 1.69%
Byusing the offset lowest mortgage rate of 1.69%, the mortgage can berepaid in 4yrs seven months, and the Adam and Eve will save anaverage of £151.18 on their monthly payments.
FixedRate: 1.79%
Using1.79%, Adam and Eve will pay £1,659 monthly while for 1.69% theywill be able to save £151.18. Therefore, they should consider theoffset mortgage as their option as opposed to fixed rate mortgage.

By increasing/decreasing the interest rates by 3%
Afterreducing the interest rate from 3.99% to 3 %, for 1.69% mortgage, therates do not change much for the offset mortgage but increase to£1,902 for fixed rate mortgage result in average savings
Question4
Dataanalysis for 20 branches of a retailing company based on theirprofits, sales, the size and number of product lines.

Distribution of profits of the twenty branches.
Usingthe Excel frequency function and assuming the intervals of £5, thebelow frequency distribution table was obtained.
Toget the impression on distribution, the below histogram has beengenerated from the data above.
Thereare three branches of their profit range between £5 and £0, 5 ofthe branches have their profit between £0 and £5, 5 branches havethe profit range between £10 and 15, 1branch has profit rangebetween £15 and £20, 2 branches between £20 and £25, 1 branchbetween £25 and £30, no branch with profit range between 30 and 35,1 branch has a range of between £35 and 40, and 1 branch has profitrange between 40 and 45.

Average number of lines stocked per store is significantly different from 78
Usingthis formula in Excel = AVERAGE (G2: G21), the returned value isvalue return is 100.5. Therefore, the average number of lines stockedper store, 100.5 lines, is significantly different from 78.

Division of branches based on sales
Fromthe two graphs, huge difference in profits can be perceived for thesales above £150,000 and the sales below £150,000. For instance,for the sales less than £150,000, the highest number of branches, 8,while five branches with sales over £150,000 have their profitsbelow £15,000. Cumulatively, it can be observed that more profit isrealized for the sites that have their sales less than £150,000.

99% Confidence interval on the Profits
Usingthe Excel descriptive statistics option of data analysis group fromData tab in the menu bar, the below statistics were generated.
Statistics 

Mean 
11.729 
Standard Error 
2.959079773 
Median 
6.8 
Mode 
#N/A 
Standard Deviation 
13.23340705 
Sample Variance 
175.1230621 
Kurtosis 
0.592914083 
Skewness 
1.236672727 
Range 
44.82 
Minimum 
2.69 
Maximum 
42.13 
Sum 
234.58 
Count 
20 
Confidence Level (99.0%) 
8.465733727 
Lower Confidence 
3.263 
Average Confidence 
11.729 
Upper confidence 
20.195 
The99% confidence level value is 8.465733727,the lower confidence level is the mean, less the 99% confidencevalue, while the upper confidence level is the sum of the mean and99% confidence value.
Thelower confidence limit is 3.263 while the upper confidence limit is20.195. Therefore, we are 99% confident that true profit mean liesbetween 3.263 and 20.195.

Correlation between Profit and Other Variables

Correlation between profits and sales
Thiscan be obtained from the correlation curve below
Fromthe correlation equation above, the correlation coefficient, R^{2},is 0.9344. This is closer to 1 hence we can conclude that sales andprofits are directly correlated. The scatter about the best curveindicates that increase in sales results to increase in profits.
Correlationbetween profits and size
Theregression analysis below represents the correlation of size andprofits
Fromthe correlation equation above, the correlation coefficient, R^{2},is 0.0002. This is considerably lower than one. Therefore, it can beconcluded that the scatter about the best curve illustrates thatthere is no direct correlation between profits and the size of thebranch.
Correlationbetween profits and number of Lines
Fromthe correlation equation above, the correlation coefficient, R^{2},is 0.8418. This is closer to 1 hence we can conclude that the numberof lines and profits are directly correlated. The scatter about thebest curve indicates that increase in number of lines results toincrease in profits.
Therefore,from the above correlation analysis of sales, lines and size from 20branches, it is worth concluding that the consideration to increasesales and number of lines will result to increase profitability ofthe branch. On the other hand, the size of the branch is notcorrelated at all to the amount of profits received.
Question5: My Velo

Investment Appraisal Techniques
Investmentappraisal refers to the assessment of the feasibility of aninvestment project such as purchase of a manufacturing plant (Bull2008, p. 34)
Themajor methods used in business appraisal include payback period,Internal Rate of Return, accounting rate of return, profitabilityindex and net present value of discounted cash flow (PateCornell& Dillon 2006, p. 220).

Accounting Rate of Return (ARR)
ARRis a nondiscounted appraisal technique that compares profit expectedfrom the proposed project to the initial investment capital requiredfor the project (Schenker & Gentleman 2001, p. 182). A projectwith high ARR has higher is preferred to that with low ARR value.
Advantagesof ARR
Beloware the advantages of ARR

Appropriate method of evaluating the profitability of an investment

Enhances comparison of the investment options

No requirement for special reports for determining ARR since it is based on the accounting information
Disadvantagesof ARR
Thedisadvantage of ARR include

It ignores the time value of money as it is based on discounted cash flow

Ignores the terminal project value

Ignores the cash flow from an investment

Internal Rate of Return (IRR)
IRRis the discount rate that gives zero value to net present value. ForMy Velo, IRR will be very effective in appraising the capitalinvestment as it measures the efficiency of the investment. When thecost of investment is higher than the IRR value, then the projectmight not be profitable and should be rejected. Unlike ARR, IRR takesinto consideration time value of money within the life cycle of theproject(Ragsdale 2004, p. 31).
Advantagesof IRR
Beloware the advantages of IRR

It considers the time value of money

It is simple to interpret after calculation, hence managers can easily visualize make decision

Does not require hurdle rate, hence mitigating the complexity and subjective nature of the hurdle rate determination.

IRR is not completely dependent on required rate of return. Hence, the managers can safely decide on the appropriate measure on either side after IRR is compared with the hurdle rate.
Disadvantagesof IRR
Thedisadvantages of IRR include:

Ignores the economies of scale, hence dependent on the amount invested

Assumes reinvestment rate of the positive cash flows in future. The assumption of high IRR rate for high reinvestment rate is invalid.

IRR is dependent on contingent projects

Payback Period
Theinvestment appraisal based on the payback period is dependent on thetime that the project would take to get back the initial capitalinvested. This is among the easiest methods of appraising aninvestment. Normally, projects with short payback periods tend to bemore preferred to those with longer periods(Pogue 2004, p. 565).
Advantagesof payback period
Theadvantages of Payback period include:

It is easy to calculate and understand

Relevant in projects with cash flow issues

Effective for the markets that are changing rapidly
Disadvantagesof Payback method in investment appraisal
Beloware the disadvantages of payback period

Money received after payback is ignored

Difficult in establishing target payback period

Ignores future value of the money

Can only be used for shortterm analysis

Net Present Value (NPV)
Thisis the investment appraisal method used in measuring the cash inflow, whether shortfall or excess, after meeting the routine financerequirements. The project in My Velo should aim at driving towards apositive NPV. In calculations, NPV entails the net cash flow for aspecific time at discounted rate within the same time, hence relatedinversely to discount the rate. High discount rate implies reducedNPV value of the investment. Highinterest rates result in increaseddiscount rates and many investment appraisals should be cautious ofsuch increases (Estrada2011, p. 21).
Advantagesof NPV
Theadvantages of NPV include:

It considers the time value of money that changes over time, particularly during deflation and inflation periods

The value reflects absolute terms of decrease or increase

All the cash flows are considered
Disadvantagesof NPV
Beloware disadvantages of NPV

The process of arriving at the NPV value is complex and requires performance of extensive calculations

The process entails the use of many assumptions, and as such, inaccurate assumptions may result in inaccurate values and consequently inaccurate decisions.

Comments on the any effects if the company wants to pay back in 3years
Year 1 
Year 2 
Year 3 
Year 4 

Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 

Buying option 
50,000 

Land Buying and Building Factory 
12,000 
3,000 

Sales 
14,000 
15,400 

Cost of Sales 
7,000 
6,650 
6,317.5 

Administration and Selling Cost 
1,400 
1,540 
2,000,000 

Refurbishing 

Sell the factory for £6,000,000 

Cost of Capital 8.75% 

Initialinvestment = Landbuying option + Land acquisition and Factory Building + Sales Cost +Administration and Selling Cost
MyVelo will pay back in 2.76.years.Therefore, this project will be accepted since the payback timerequirement by My Velo is three years. From the calculations, thecompany would have

Using NPV and IRR to determine the feasibility of the project
  
Year 1 
  
Year 2 
Year 3 
Year 4 
Year 5 
Year 6 
Year 7 
Year 8 
Year 9 
Year 10 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 
Amount (£) 

Buying option 
50,000 
  
  
  
  
  
  
  
  
  
  
Land Buying and Building Factory 
12,000 
3,000 
  
  
  
  
  
  
  
  
  
Sales 
  
  
14,000 
15,400 
16940 
18634 
18634 
18634 
18634 
18634 
18634 
Cost of Sales 
  
  
7,000 
6,650 
6,318 
6317.5 
6317.5 
6317.5 
6317.5 
6317.5 
6317.5 
Administration and Selling Cost 
  
  
1,400 
1,540 
1,694 
1,863 
1,863 
1,863 
1,863 
1,863 
1,863 
Refurbishing 
  
  
  
  
2,000,000 
  
  
  
2,000,000 
  
  
Sell the factory for £6,000,000 
  
  
  
  
  
  
  
  
  
  
  
Cost of Capital 8.75% 
  
  
  
  
  
  
  
  
  
  
  
NPVand IRR
Interest Rate 
Year 
Cash Flow 
Present Value 
8.75% 
0 
(50,000.00) 
(50,000.00) 
  

(15,000.00) 
(13,793.10) 
  

5,600.00 
4,735.10 
  

7,210.00 
5,605.93 
  

(1,991,071.50) 
(1,423,540.39) 
  

10,453.10 
6,872.25 
  

4,454.10 
2,692.68 
  

1,863.40 
1,035.86 
  

(1,989,546.90) 
(1,016,997.90) 
  

10,453.10 
4,913.40 
  

6,010,453.10 
2,597,852.90 
  
  Net Present Value 
119,376.72 

  
119,376.72 

  
IRR 
9.89542% 
NetPresent Value
NPVis the sum of discounted cash flows less the original investment
Where
,=£50,000
=Cash flows for years 1 to 10
=8.75
=10years
Forthe case of My Velo, NPV= £119,376.72
IRR
Isthe rate of return at which NPV is equivalent to zero, when projectcash flows are same as the cost (Payton & Schenker 2003, p. 34).
Thisis expressed using the formula below
Fromthe calculations using Excel and present in Table above, IRR isequivalent to 9.89542%(Thishas taken into consideration the £6milion as cash flow in the 10^{th}year)
Therefore,when £6 million in included in calculations, the NPV is £119,376.72and IRR are 9.89542%.Since NPV is positive, then the project is feasible. My Velo shouldconsider going on with the project. Also, since the discount rate is8.75% and IRR 9.89542%, then IRR is higher than the discount rate,hence the project is feasible.
ReferenceList
Bull, R. (2008). Financial Ratios: How to Use Financial Ratios to Maximise Value and Success for Your Business. Oxford: CIMA.
Estrada, J. (2011). The Financial Times Guide to Understanding Finance a Nononsense Companion to Financial Tools and Techniques. Harlow: Pearson/Prentice Hall.
Götze, U., Deryl, N., & Peter, S. (2008). Investment Appraisal: Methods and Models. Berlin: Springer.
PateCornell, M., & Dillon, R. L. (2006). The Respective Roles of Risk and Decision Analyzes in Decision Support. Decision Analysis 3(4), 22032.
Payton, M., & Schenker, N. (2003). Overlapping confidence intervals or standard error intervals: what do they mean in terms of statistical significance? Journal of Finance, 34.
Pogue, M. (2004). Investment Appraisal: A New Approach. Managerial Auditing Journal 19(4), 56569.
Ragsdale, C. (2004). Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Management Science. Mason, OH: Thomson/SouthWestern.
Schenker, N., & Gentleman, F. (2001). On judging the significance of differences by examining the overlap between confidence intervals. American Statistician 55, 182186.