Discounted Cash Flow Analysis And Linear Programming Optimization Model

Discounted Cash Flow Analysis of Shambles Retailer’s Investment

1. Use Excel to set up a discounted cash flow analysis. The table below shows the results of discounted cash flow analysis of Shambles Toy retailer, discount rate range from 10% to 50%.

				Discount Rate Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper
				10%	20%	30%	40% Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	50%
Year	Return R	Cost C	Net Return= R-C	PV	PV	PV	PV	PV
2011	-£50,000.00	£0.00	-£50,000.00	-£50,000.00	-£50,000.00	-£50,000.00	-£50,000.00	-£50,000.00
2012	£24,000.00	£12,000.00	£12,000.00	£10,909.09	£10,000.00	£9,230.77	£8,571.43	£8,000.00
2013	£29,000.00	£12,000.00	£17,000.00	£14,049.59	£11,805.56	£10,059.17	£8,673.47	£7,555.56
2014	£32,000.00	£12,000.00	£20,000.00	£15,026.30	£11,574.07	£9,103.32	£7,288.63	£5,925.93
2015	£35,000.00	£12,000.00	£23,000.00	£15,709.31	£11,091.82	£8,052.94	£5,987.09	£4,543.21
2016	£40,000.00	£12,000.00	£28,000.00	£17,385.80	£11,252.57	£7,541.21	£5,206.16	£3,687.24
			NPV	£23,080.08	£5,724.02	-£6,012.58	-£14,273.22	-£20,288.07

Below is table to show the print out of Excel cell formula for the discounted cash flow analysis.

		Discount Rate
				0.1	0.2	0.3	0.4	0.5
Year	Return R	Cost C	Net Return= R-C	PV
2011	-50000	0	=C6-D6	=E6/(1+$F$4)^A5	=E6/(1+$G$4)^A5	=E6/(1+$H$4)^A5	=E6/(1+$I$4)^A5	=E6/(1+$J$4)^A5
2012	24000	12000	=C7-D7	=E7/(1+$F$4)^A6	=E7/(1+$G$4)^A6	=E7/(1+$H$4)^A6	=E7/(1+$I$4)^A6	=E7/(1+$J$4)^A6
2013	29000	12000	=C8-D8	=E8/(1+$F$4)^A7	=E8/(1+$G$4)^A7	=E8/(1+$H$4)^A7	=E8/(1+$I$4)^A7	=E8/(1+$J$4)^A7
2014	32000	12000	=C9-D9	=E9/(1+$F$4)^A8	=E9/(1+$G$4)^A8	=E9/(1+$H$4)^A8	=E9/(1+$I$4)^A8	=E9/(1+$J$4)^A8
2015	35000	12000	=C10-D10	=E10/(1+$F$4)^A9	=E10/(1+$G$4)^A9	=E10/(1+$H$4)^A9	=E10/(1+$I$4)^A9	=E10/(1+$J$4)^A9
2016	40000	12000	=C11-D11	=E11/(1+$F$4)^A10	=E11/(1+$G$4)^A10	=E11/(1+$H$4)^A10	=E11/(1+$I$4)^A10	=E11/(1+$J$4)^A10
			NPV	=SUM(F6:F11)	=SUM(G6:G11)	=SUM(H6:H11)	=SUM(I6:I11)	=SUM(J6:J11)

Decising whether or not this is a worthwhile investment, giving your reasons.

According to Francis (2004) and DCA2C (2017), investment is said to be viable if NPV > 0. Therefore, Shambles’ investment is worthwhile, as the NPV (23,080.08) is greater than zero. This shows that investment earns more than the discount rate (Murthy 2011).

1. Explaining the effect of discounting in this case upon future inflows, outflows, and the NPV.

The NPV at a discount rate of 10% is positive. This suggests that the internal rate of return is greater than the rate of return (De Reyck, Degraeve &Vandenborre, 2008).

As result, discounting future cash flows will give will produce better results, hence acceptable NPV.

1. Calculation of the NPV for discount rates of 20%, 30%, 40% and 50%, giving your answers to two decimal places.

 The NPV of Shamble returns and cost at the discounted rates are £ 5,724.02, -£ 6,012.58, -£ 14,273.22 and -£ 20,288.07, respectively. These results are as shown in the tables above. Computations were done on the Microsoft Excel application. 

1. Using Excel and the data generated from part (b), to plot NPV against the discount rate in the range 10% to 50%.

Below is a graphical representation of  NPV against discount rate 

1. Using the above graph to estimate the discount rate that would give NPV of zero and indicate its significance

On the graph above, NPV of zero is located where the interpolation line intersects with the . The discount rate at this point is 28%.   This is the internal rate of return (IRR) of the investment, which is attained when (Benninga, Benninga &Czaczkes 2000). IRR will help Shambles Retailer to measure its profitability. The IRR of Shambles Retailer is greater than 10 % ( rate of return giving a positive NPV), this suggest is likely to make higher a profit if the rate of return is kept  below 28% but above 10%.

Exercise 2: Linear Programming Optimization Model

1. Mathematical Calculation And Graphical Representation

Let  be number of Pegasus Toys and  be number of Phoenix Toys

First, the number of hours needs to sew or stuff per day is computed, which will be given by,  

 To conduct optimization, the graph of the above constraints is plotted. Here,    and      of the inequalities  are determined. They are determined by substitution,

where is replaced with 0 when   is computed, and  is replaced with 0 when  is computed (Berenson e ta l 2012). Below is a list of intercepts for the two inequalities.

.  Below is a graphical representation of the four constraint inequalities.

R is the feasible region. The points that bound the feasible region are . These are points below the two lines and   but above .  To obtain the points that optimize profit,  the trial and error method will be applied,  where points , furthest away from the origin are considered (Francis 2004). The values of  are substituted in the profit objective function, , to compute the profits. The point that leads to the highest profit value is considered   as the profit optimal

	0	5	6	10	7
	12	7	6	0	4.4
Profit value	120	110	108	80	100

Comparing the five profit values, profit is optimized when are  . Therefore to maximize profit, Shambles will have to maintain zero production of   Pegasus and produce 12 units of Phoenix toy.

1. Using  Microsoft Excel solver, to solve a linear programming problem. Following are the results obtained when the problem was solved in
2. Print out of Cell values

		Pegasus	Phoenix	Total	Max Capacity
Decision Variable	0	12
Profit		£                8	£              10	£        120
Constraint
Sewing		60	40	480	600
Stuffing		40	50	600	600

Print out of Cell formula

		Pegasus	Phoenix	Total	Max Capacity
Decision Variable		0	12
Profit		8	10	=D37*D36+E37*E36
Constraint
Sewing		60	40	=D40*D36+E40*E36	600
Stuffing		40	50	=D41*D36+E41*E36	600

A print-out of the Solver dialogue box with optimization conditions and constraints.

The following is a diagram Excel Solver dialogue. 

1. Answer Report

Linear Programming Optimization Model for Toy Production

Optimal profit value is 120, 

This is attained when the company fails to produce Pegasus toys but maximize profit by producing 12 units of Phoenix.

 Exercise Three: GPSS Simulation Model

The sales counter next to the soft toy display in Shambles receives a customer every 3-5 minutes.
Most of these customers (90%) are buying toys and are dealt with by the cashier in 2-4 minutes. The remaining 20% of customers come to open accounts that require an account manager. These
customers wait for the account manager, who spends 20-30 minutes serving them.

1. You are required to simulate an 8-hour day in this department using GPSS. To begin with, construct a flowchart describing the above events using suitable GPSS blocks.

1. Now carry out the simulation in GPSS, giving your programme (code) and simulation report.

Solution 

GENERATE       4,0                            

                    QUEUE      system                      

                    QUEUE      Arrival                      

                    SEIZE         Cashier                     

                    DEPART    Arrival                      

                    ADVANCE 4,2                            

                    RELEASE  Cashier                     

                    DEPART    Arrival                      

                    TRANSFER                                 0.9,Cashier,Manager

Cashier        TERMINATE                               1           

Manager          TERMINATE 1

1. Describe the results of your simulation using the simulation report, giving as much detail as possible. [8 marks]

There are 3 entries and eleven block type

Exercise Four: Association Rule Mining

Mining all the association rules for the data in the table below, given that support, 66% (0.66)

Tid	Items bought
10
20
30

To work out this problem data is analyzed first, to determine the frequencies of occurrence of sets of items bought (Han, Pei & Kamber 2011).  The table below shows the sets of items bought with their respective frequencies.

Set of  items bought	Frequency
	2
	2
	3
	1
	2
	2
	1

These frequencies are used to determine the degree of support and confident of items set

( Agrawal& Srikant1994). Second, the possible association rules are determined. Finally, with the degree of support and confidence are subjected to the association rule conditions. “Given the set of transactions T, the goal of association rule is to find all the rules that satisfy,  support ≥ min sup threshold and a confidence ≥ min conf threshold”( Tan, Steinbach& Kumar 2005). For case in question, the conditions, , will have to be met.   Below are possible Association rules, and computation of their Support and Confidence.From the above possibilities, the association rules that meet the requirements are:Their computed degree of support and confidence are greater than the minimum threshold of support (0.66) and confidence (0.34). 

Exercise Five: Artificial Neural Networks

There are four training input/target pairs for a two-class problem:The initial weight vector given is

A two-input perceptron with hard limit activation function is used to solve this classification problem. Write down the step-by-step solution to train the classifier by the basic perceptron learning.

Solution

Steps involved are:

1. Presentation of the above pattern in a table

Inputs	Desired Output
2	0	1
1	2	1
-1	3.5	0
0	-1	0

• Finding the bias ( )

According to (Yegnanarayana, 2009) output ( ) is given by  To determine the value of    , decision boundary is considered, the output is equated to 0. Therefore, the output will be given by Since the decision boundary passes through the point (0.5, 0) as illustrated in the chart above, then the computation of  will be determined by The hardline transfer function forces neural output a 1 if the input reaches a threshold, otherwise, its output is 0. This allows the neuron to make a decision (Mitchell 1997). 

Designing a DSS to help decision-makers run the 2012 London Olympics.

Linear programming optimization model will be the model of DSS to be utilized.   According to Dantzig ( 2016 ) and Zoutendijk (1960), is among the common decision making support model that is utilized by most decision-makers, in the production industry to make a production plan. Therefore, since Olympic competition will require equipment, mostly medals for the winners, this model will be critical as it will help Olympic in deciding the number of medals that can be produced at minimal cost and time, to meet the demand of the whole event. To design DSS, the following projections well made.     

Projections

• Fifteen thousand athletes from at least 200 countries will participate in about 650 events.
• Medals’ demand will be 4500.
• Each winner of  an event will   receive  medals, up to position three,
• Medals to be are Gold with 410g, Silver weighing 400g and Bronze weighing 350g
• All medals are made by the mint, where they under three stages of production, Pressing and Cleaning, Reheating and Engraving and Packaging.
• Cost of making the three types of medals varies.

• The Mint runs for 24 hours a

The following table shows the whole information concerning the production of medal for the Olympics.

		Time in minutes
Medal	Cost of Production ($ )	Pressing &Cleaning	Re-Heating	Engraving  and & Packaging	Time for producing One medal
Gold	600	180	60	120	360
Silver	500	150	60	90	300
Bronze	400	120	30	90	240

To find credible solutions for the above established Olympic project, Solver in the Ms excel is used to determine the cost-optimal point. 

References

Agrawal, R. and Srikant, R., 1994, September. Fast algorithms for mining association rules. In Proc. 20th int. conf. very large data bases, VLDB (Vol. 1215, pp. 487-499).

Berenson, M., Levine, D., Szabat, K.A. and Krehbiel, T.C., 2012. Basic business statistics: Concepts and applications. Pearson higher education AU.

Benninga, S., Benninga, S.Z. and Czaczkes, B., 2000. Financial modeling. MIT press.

DCA2C, D.D., 2017. BUSINESS MATHEMATICS AND STATISTICS.

De Reyck, B., Degraeve, Z. and Vandenborre, R., 2008. Project options valuation with net present value and decision tree analysis. European Journal of Operational Research, 184(1), pp.341-355.

Dantzig, G., 2016. Linear programming and extensions. Princeton university press.

Dantzig, G.B. and Thapa, M.N., 2006. Linear programming 1: Introduction. Springer Science & Business Media

      Francis, A., 2004. Business mathematics and statistics. Cengage Learning EMEA.

Han, J., Pei, J. and Kamber, M., 2011. Data mining: concepts and techniques. Elsevier.

Murthy, G., 2011. Project Appraisal Techniques.

Mitchell, T.M., 1997. Artificial neural networks. Machine learning, 45, pp.81-127.

Rosen, K.H., 2017. Handbook of discrete and combinatorial mathematics. Chapman and Hall/CRC

Russell, S.J. and Norvig, P., 2016. Artificial intelligence: a modern approach. Malaysia; Pearson Education Limited,

Remer, D.S. and Nieto, A.P., 1995. A compendium and comparison of 25 project evaluation techniques. Part 1: Net present value and rate of return methods. International journal of production economics, 42(1), pp.79-96.

Tan, P.N., Steinbach, M. and Kumar, V., 2005. Introduction to data mining. 1st.

Yegnanarayana, B., 2009. Artificial neural networks. PHI Learning Pvt. Ltd.

Zoutendijk, G., 1960. Methods of feasible directions: a study in linear and non-linear programming. Elsevier.