Financial Analysis and Modeling - Essay Example

 

SUMMARY OUTPUT

 

   

Regression Statistics

Multiple R

0.992398792

R Square

0.984855361

Adjusted R Square

0.977283042

Standard Error

1249.867779

Observations

10

   

ANOVA

 

 

df

Regression

3

Residual

6

Total

9

   

 

Coefficients

Intercept

-10170.24332

Clothing Sales (tens of thousands of pounds)

0.002703812

Non-Clothing Sales (tens of thousands of pounds)

0.009705235

Store Size (Thousands of Square Feet)

0.524675168

   

Regression Equation

 

Profit =

-10170.2+0.002704*Clothing Sales+0.009705*Non-Clothing Sales+0.524675*Store Size

   

f) Profit =

15,830.55

 

1. The above-calculated Regression Analysis clearly illustrates that the Significance level is within the limit of significance i.e. 0.05 shown in the P-Value, which means that all the variables are significantly related to one another, as the value is (7.55516E-06). Whereas staring at the individual P-Values, illustrates that the Non-clothing Sales value and the Store Size value are significantly related to the profits. The value of Clothing sales is higher than the significant value (0.066 rounded off). (The F value is 130.0599, the MS value is 203175661.1 and the SS value is 609526983.2), The Co-efficient values illustrate that all the variables are directly related that means with the increase in Store Size the Profits will also increase, and this is the case with Clothing Sales and Non-clothing Sales. It can also be stated that Clothing Sales are the least related variable among the rest of the two variables and can be neglected in order to achieve more accurate and realistic results.
2. The R-Square value represents the closeness of the values to the regression line; in the result of this regression analysis, the R-Square value is 0.985 which illustrates that the values are closely fitted. This also shows that the variations in any one of the variables (i.e. No-clothing Sales and Store Size) may cause a positive or negative effect of 98.5%. The value of Clothing Sales is ignored as it is not in the significant region and will not affect the overall outcome.

 

1. The P-values indicate the significance values that whether the variable(s) (the independent ones) affect the dependent one(s) or the overall regression analysis. In the regression analysis, the P-Values of Non-clothing Sales and Store Size are significant at the 5% significance level.

 

Clothing Sales P-Value: 0.065 > 0.05

Non-clothing Sales P-Value: 0.018 < 0.05

Store Size P-Value: 0.000 < 0.05

Part C

1. Net Present Value

The net present value is the sum of all net cash flows expected from a project over a period of projection (Needles, Powers, & Crosson, 2010). In this case, three years' projections have been considered for two projects X and Y.

Project X

         

0

1

2

3

Cash Flows

-2,500,000

 900,000

 900,000

 900,000

Discount Factor

 1.0000

 0.8547

 0.7305

 0.6244

PV of Cash Flows

-2,500,000

 769,231

 657,462

 561,934

NPV

-511,374

     

 

 

 

 

Project Y

         

0

1

2

3

Cash Flows

-2,000,000

 950,000

 950,000

 950,000

Discount Factor

 1.0000

 0.8547

 0.7305

 0.6244

PV of Cash Flows

-2,000,000

 811,966

 693,988

 593,152

NPV

 99,106

     

 

The NPV of Project X is negative i.e. less than zero, therefore, this project should not be accepted. On the other hand, Project Y has a positive NPV value i.e. more than zero over a three-year projection period, therefore, Project Y should be accepted (Moyer, McGuigan, Rao, & Kretlow, 2011).

 

1. Monte Carlo Simulation

 

Triangular Distribution

                     

Sales

Min

Most Likely

Max

Project X

1.9

2.1

2.3

       

Project Y

1.5

1.9

2.3

 

Pert Distribution

             

Materials

Min

Most Likely

Max

Project X

0.6

0.8

1

       

Project Y

0.1

0.2

0.3

 

Discrete Probability Distribution

                 

Labour

Project X

     

x-values

           200,000

          300,000

          400,000

                                500,000

p-values

0.3

0.5

0.1

0.1

 

Project Y

     

       500,000

           700,000

          800,000

       900,000

0.3

0.5

0.1

0.1

 

Normal Distribution

         

Overheads

Mean

Standard Deviation

Project X

           100,000

              5,000

Project Y

             50,000

              1,000

 

 

Project X

         

0

1

2

3

         

Initial Investment

       (2,500,000)

     

Sales

 

       2,131,373

       2,200,942

                             1,964,944

Material

 

        (678,317)

        (786,427)

                               (784,406)

Labour

 

        (300,000)

        (390,000)

                               (590,000)

Overheads

 

        (106,871)

          (98,542)

                                 (99,521)

   

       1,046,185

          925,973

                                491,017

 

             1.0000

            0.8547

            0.7305

                                  0.6244

 

       (2,500,000)

          894,175

          676,436

                                306,577

NPV

          (622,812)

               

NPV

          (905,678)

       

          (954,580)

       

          (581,083)

       

          (622,812)

     

 

By performing Monte Carlo Simulation, different NPV has been obtained. The NPV of Project X under different what-if scenarios remains negative therefore the project should not be accepted.

 

Project Y

         

0

1

2

3

         

Initial Investment

       (2,000,000)

     

Sales

 

       1,919,762

    1,532,990

   1,798,348

Material

 

        (178,289)

     (170,453)

     (214,399)

Labour

 

        (670,000)

     (520,000)

     (170,000)

Overheads

 

            49,590

         50,110

        48,606

   

       1,121,063

       892,648

   1,462,555

 

             1.0000

            0.8547

         0.7305

        0.6244

 

       (2,000,000)

          958,174

       652,091

      913,176

NPV

           523,441

               

 NPV

        1,111,925

       

           745,201

       

           537,918

       

           523,441

     

Monte Carlo Simulation generates the above NPV from Project Y. These values indicate that the NPV of Project Y is expected to remain positive and therefore the project should be accepted.

Sales – Triangular Distribution

Materials – PERT Distribution

Overheads – Normal Distribution

Project Y

Sales – Triangular Distribution

Materials – PERT Distribution

Overheads – Normal Distribution

1. Monte Carlo Simulation allows multiples values of an asset or investment based on certain sensitivity or scenario analysis. It allows businesses to prepare better by evaluating the outcome of a project or investment by knowing the possible output values and responding to them in accordance with the situation faced by the business. (McLeish, 2011). It is a better approach than deterministic analysis as the latter is based on single-point estimates and considers only the number of discrete results and ignores other possible outcomes. Moreover, it assigns equal weight to different scenarios or situations considered irrespective of the possibility of their occurrence.

Read More