Homework answers / question archive / 500 or more Words with good explanation With calculation in Excel 1

500 or more Words with good explanation With calculation in Excel 1

Finance

Share With

---

500 or more Words with good explanation

With calculation in Excel

1.( focus on real 2020 data) = 2021-2025 Calculate EPS, Dividends per share, Average price per share

Earning Available to Common stockholders

Outstanding shares of common stock

 

 

how does the company compare 5 years into the future as it does today. ( 2021 real - to project data2021- 2025)

2. develop a stress test scenario analysis in case of ADVERSITY

Ø 25% Pessimistic Scenario: Not likely to meet Original Plan

Ø 50% Most Likely: Chance that Projection will take place accordingly

Ø 25% Optimistic Scenario: Results from Strategic Decisions perform better than planned

 

 

What does look like both in revenue and earnings. real 2020 data revenue and earnings.

Ø  YEAR 2021- 2025.

 

 

3. Target optimal capital structure

Do an assessment of what the more optimal capital structure should be

2025 revenue $654,183.20 (2021-2025)

Total debt:            $588,764.88           90%

Total Equity:          $65,418.32

Total capital:          $654,183.20

 

 

 

 

 

 

 

 

 

Data for feature 2021-2025

 

 

 

 

Future projection data (2021-2025)

2021

2022

2023

2024

2025

Average change

Sales

 $ 544,922.56

 $          568,354.23

 $ 593,930.17

 $   622,438.82

 $          654,183.20

 

Growth Rate

4%

4.30%

4.5%

4.80%

5.10%

5%

Cost of Sales

 $ 408,691.92

 $          425,128.96

 $ 444,853.70

 $   464,339.36

 $          487,366.48

 

% change from revenue

75.00%

74.8%

74.90%

75%

75%

75%

Gross margin

 $ 136,230.64

 $          143,225.27

 $ 149,076.47

 $   158,099.46

 $          166,816.72

 

% change from revenue

24.50%

24.50%

25%

24.80%

25%

25%

SG$A

 $ 114,433.74

 $          113,670.85

 $ 115,816.38

 $   124,487.76

 $          130,836.64

 

% change from revenue

21%

20%

19.50%

20%

20%

20%

Operating income

 $   21,796.90

 $            29,554.42

 $   33,260.09

 $     33,611.70

 $            35,980.08

 

% change from revenue

4.00%

5.20%

5.60%

5.40%

6%

5%

Interest expense

 $      2,724.61

 $               2,841.77

 $      2,969.65

 $        3,112.19

 $              3,270.92

 

% change from revenue

0.50%

0.50%

0.50%

0.50%

0.50%

0.5%

Profit before tax

 $   19,072.29

 $            26,712.65

 $   30,290.44

 $     30,499.50

 $            32,709.16

 

% change from revenue

3.50%

4.70%

5.10%

4.90%

5%

5%

Income taxes (21%)

$4,005.18

$5,609.66

$6,360.99

$6,404.90

$6,868.92

 

% change from revenue

1%

1%

1%

1%

1%

1%

Net income

 $   15,067.11

 $            21,102.99

 $   23,929.45

 $     24,094.61

 $            25,840.24

 

% change from revenue

2.8%

3.7%

4.0%

3.9%

4.0%

3.7%