1.0. Decision Analysis

14

  1. 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)

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

      1. 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%

      1. 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.

      1. 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

&nbspExpected 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%

&nbsp7

Best Decision

8

&nbsp

&nbsp

&nbsp

&nbsp

9

&nbsp

&nbsp

&nbsp

EVwPI

=SUMPRODUCT(B7:D7,B6:D6)

10

&nbsp

&nbsp

&nbsp

EV Best

=MAX(E3:E5)

&nbsp

&nbsp

&nbsp

EVPI

=E9-E10

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

&nbsp&nbspExpected 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%

&nbsp

&nbsp7

Best Decision

11,000

14,000

21,900

&nbsp

8

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

9

&nbsp

&nbsp

&nbsp

EVwPI

14830

10

&nbsp

&nbsp

&nbsp

EV Best

13845

&nbsp

&nbsp

&nbsp

EVPI

985

    1. 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 (EVBest)is 13,845, which falls under international expansion.

  1. 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.

    1. 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

  1. Hours of forming not to exceed 2,250hrs

  2. Hours of machining not to exceed 8,750hrs

  3. Hours of Assembly not to exceed 3,250hrs

  4. Hours of testing not to exceed 2,375hrs

Thedecision variables representing actual decisions can be defined as

  1. C for Cheap

  2. A for Average

  3. 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

&nbsp

&nbsp

Cost of Production (£)

17.625

36.0625

96.875

&nbsp

&nbsp

Profit (£)

57.34

122.94

278.13

&nbsp

&nbsp

  • 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:

  1. 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.

  1. Changes when:

  1. 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.

  1. 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.

  1. 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 1-bedroom £330,000 2-bedroom £440,000 3-bedroom£575,000 4-bedroom £750,000

Calculation Assumption

Monthly saving for Adam andEve= £500

    1. 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.

    1. Lowest charges currently by Barclays for Offset mortgage

Barclaysbank is charging the lowest offset rate of 1.69% and 1.19% for twoyears.

    1. 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.

  1. 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 two-bedroomedflat from the specifications given. The cost of 2-bedroomed flat is£440,000

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. Correlation between Profit and Other Variables

  1. Correlation between profits and sales

Thiscan be obtained from the correlation curve below

Fromthe correlation equation above, the correlation coefficient, R2,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, R2,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, R2,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

    1. 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 (Pate-Cornell&amp Dillon 2006, p. 220).

      1. Accounting Rate of Return (ARR)

ARRis a non-discounted appraisal technique that compares profit expectedfrom the proposed project to the initial investment capital requiredfor the project (Schenker &amp 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

      1. 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

      1. 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 short-term analysis

  1. 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. High-interest 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.

  1. 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

  1. Using NPV and IRR to determine the feasibility of the project

&nbsp

Year 1

&nbsp

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

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

Land Buying and Building Factory

12,000

3,000

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

Sales

&nbsp

&nbsp

14,000

15,400

16940

18634

18634

18634

18634

18634

18634

Cost of Sales

&nbsp

&nbsp

7,000

6,650

6,318

6317.5

6317.5

6317.5

6317.5

6317.5

6317.5

Administration and Selling Cost

&nbsp

&nbsp

1,400

1,540

1,694

1,863

1,863

1,863

1,863

1,863

1,863

Refurbishing

&nbsp

&nbsp

&nbsp

&nbsp

2,000,000

&nbsp

&nbsp

&nbsp

2,000,000

&nbsp

&nbsp

Sell the factory for £6,000,000

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

Cost of Capital 8.75%

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

&nbsp

NPVand IRR

Interest Rate

Year

Cash Flow

Present Value

8.75%

0

(50,000.00)

(50,000.00)

&nbsp

(15,000.00)

(13,793.10)

&nbsp

5,600.00

4,735.10

&nbsp

7,210.00

5,605.93

&nbsp

(1,991,071.50)

(1,423,540.39)

&nbsp

10,453.10

6,872.25

&nbsp

4,454.10

2,692.68

&nbsp

1,863.40

1,035.86

&nbsp

(1,989,546.90)

(1,016,997.90)

&nbsp

10,453.10

4,913.40

&nbsp

6,010,453.10

2,597,852.90

&nbsp

&nbsp

Net Present Value

119,376.72

&nbsp

119,376.72

&nbsp

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 &amp 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 10thyear)

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 No-nonsense Companion to Financial Tools and Techniques. Harlow: Pearson/Prentice Hall.

Götze, U., Deryl, N., &amp Peter, S. (2008). Investment Appraisal: Methods and Models. Berlin: Springer.

Pate-Cornell, M., &amp Dillon, R. L. (2006). The Respective Roles of Risk and Decision Analyzes in Decision Support. Decision Analysis 3(4), 220-32.

Payton, M., &amp 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), 565-69.

Ragsdale, C. (2004). Spreadsheet Modeling &amp Decision Analysis: A Practical Introduction to Management Science. Mason, OH: Thomson/South-Western.

Schenker, N., &amp Gentleman, F. (2001). On judging the significance of differences by examining the overlap between confidence intervals. American Statistician 55, 182-186.