INR (Crores) 2020A 2021A 2022E 2023E 2024E 2025E

Income Statement
Revenue
COGS
Gross Profit
Selling & Adm Expenses
EBITDA
Depreciation
Interest
EBT
Taxes
Net Income

INR (Crores) 2020A 2021A 2022E 2023E 2024E 2025E

Income Statement
Revenue 20,000.0 22,500.0 24,750.0 27,225.0 29,947.5 32,942.3
COGS 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0 13,176.9
Gross Profit 12,000.0 13,500.0 14,850.0 16,335.0 17,968.5 19,765.4
Selling and Adm. Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0 2,500.0
EBITDA 10,000.0 11,250.0 12,350.0 13,835.0 15,468.5 17,265.4
Depreciation 800.0 900.0 1,237.5 1,361.3 1,497.4 1,647.1
Interest 200.0 225.0 250.0 250.0 250.0 250.0
EBT 9,000.0 10,125.0 10,862.5 12,223.8 13,721.1 15,368.2
Taxes 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3 4,610.5
Net Income 6,300.0 7,087.5 7,603.8 8,556.6 9,604.8 10,757.8

Assumption Drivers
Revenue Growth NA 12.50% 10.00% 10.00% 10.00% 10.00%
COGS % of Revenue 40.00% 40.00% 40.00% 40.00% 40.00% 40.00%
S&G Expenses 2,000.0 2,250.0 2,500.0 250000.00% 250000.00% 250000.00%
Depreciation % Sales 4.00% 4.00% 5.00% 5.00% 5.00% 5.00%
Interest 200.0 225.0 250.0 25000.00% 25000.00% 25000.00%
Taxes 30.00% 30.00% 30.00% 30.00% 30.00% 30.00%
 Practical Exercise you need to do
1. Plot Years till 2026 using formula (No
hardcording please)
2026E
2. Use Formatting option to reflect Actuals and
Estimate years (2020A/2024E)

3. Color the top row, change font colors and

bold them - Try not to use mouse.

4. You can see that width of column D to J is not

aligned. Adjust column width to 12 without
using mouse. (Alt+H+O+W)
5. Ensure that you freeze row 6 so its always
visible. Use freeze pane without using mouse
(Alt+W+F+F)

6. Plot income statement from row 6 with

Revenue, COGS, Gross Profit, Gross Profit,
Selling & General Expenses, EBITDA,
Depreciation, Interest, EBT, Taxes, Net Income.

2026E

36,236.5
14,494.6
21,741.9
2,500.0
19,241.9
1,811.8
250.0
17,180.1
5,154.0
12,026.0

10.00%
40.00%
250000.00%
5.00%
25000.00%
30.00%
INR (Crores) 2020A 2021A 2022E 2023E

Income Statement
Revenue 20,000.0 22,500.0
COGS 8,000.0 9,000.0
Gross Profit 12,000.0 13,500.0
Selling & General Expenses 2,000.0 2,250.0
EBITDA 10,000.0 11,250.0
Depreciation 800.0 900.0
Interest 200.0 225.0
EBT 9,000.0 10,125.0
Taxes 2,700.0 3,037.5
Net Income 6,300.0 7,087.5
 Practical Exercise you need to do
2024E 2025E 2026E
1. Plot the numbers of Income statement using the data
provided in sheet "Basic Data"
2. Redo it once more using transpose function (convert
vertical data to horizontal)

3. Align formatting of numbers - Comma, one place

decimal, and parathesis (brackets) for negative numbers

4. Remove hardcoding and input formulae wherever

possible - like - Gross Profit, EBITDA, EBT, and Net Income.

5. Implement color convention for hardcoded numbers

6. Ensure that you freeze row 6 so its always visible. Use

freeze pane without using mouse (Alt+W+F+F)

7. Plot income statement from row 6 with Revenue, COGS,

Gross Profit, Gross Profit, Selling & General Expenses,
EBITDA, Depreciation, Interest, EBT, Taxes, Net Income.
INR (Crores) 2020A 2021A 2022E 2023E

Income Statement
Revenue 20000 22500
COGS 8000 9000
Gross Profit 12000 13500
Selling & General Expenses 2000 2250
EBITDA 10000 11250
Depreciation 800 900
Interest 200 225
EBT 9000 10125
Taxes 2700 3038
Net Income 6300 7088

Transpose Exercise
Revenue COGS Gross Profit Selling & General Expenses EBITDA
2020A 20000 8000 12000 2000 10000
2021A 22500 9000 13500 2250 11250
2022E
2023E
2024E
2025E
2026E
 2024E 2025E 2026E

Depreciation Interest EBT Taxes Net Income

800 200 9000 2700 6300
900 225 10125 3038 7088
INR (Crores) 2020A 2021A 2022E 2023E 2024E

Income Statement
Revenue 20,000.0 22,500.0 24,750.0 27,225.0 31,308.8
COGS 8,000.0 9,000.0 9,900.0 10,890.0 12,523.5
Gross Profit 12,000.0 13,500.0 14,850.0 16,335.0 18,785.3
Selling & General Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
EBITDA 10,000.0 11,250.0 12,350.0 13,835.0 16,285.3
Depreciation 800.0 900.0 1,237.5 1,361.3 1,565.4
Interest 200.0 225.0 250.0 250.0 250.0
EBT 9,000.0 10,125.0 10,862.5 12,223.8 14,469.8
Taxes 2,700.0 3,037.5 3,258.8 3,667.1 4,340.9
Net Income 6,300.0 7,087.5 7,603.8 8,556.6 10,128.9

Assumptions Drivers
Revenue Growth NA 12.50% 10.00% 10.00% 15.00%
COGS % of Revenue 40.00% 40.00% 40.00% 40.00% 40.00%
S&G Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
Depreciation % Sales 4.00% 4.00% 5.00% 5.00% 5.00%
Interest 200.0 225.0 250.0 250.0 250.0
Taxes 30.00% 30.00% 30.00% 30.00% 30.00%
 Practical Exercise you need to do
1. Calculate Revenue growth for year 2021. Do
percentage formatting - two places decimal.
2025E 2026E 2. Calculate COGS % of Revenue for 2020 and 2021

3. S&G Expense- Link D23 and E23 in absolute terms

with D12 andE12.
36,005.1 41,405.8
14,402.0 16,562.3 3. Calculate Depreciation % of Revenue for 2020 and
2021
21,603.0 24,843.5
2,500.0 2,500.0 4. For Interest - Do same as S&G Expense in point 3
above.
19,103.0 22,343.5
1,800.3 2,070.3 5. Taxes will be constant at 30% (for now)
250.0 250.0 6. Plot Assumptions in for 2022 onwards (we will
17,052.8 20,023.2 keep assumptions constant across forecasting period
5,115.8 6,007.0 - ONLY FOR NOW)
Revenue - 10%
11,936.9 14,016.2 COGS%Sales - 40%
S&G Exp - 2500
Dep%Sales - 5%
Interest - 250
15.00% 15.00% Taxes - 30%
40.00% 40.00% 7. Its important to check if you are consistent with
2,500.0 2,500.0 hardcoding color convention
5.00% 5.00% 8. Highlight the headings
250.0 250.0
30.00% 30.00%
Tata Steels
INR (Crores) 2020A 2021A 2022E 2023E 2024E 2025E

Income Statement - Tata Steels

Revenue 20,000.0 22,500.0 24,750.0 27,225.0 29,947.5 32,942.3
COGS 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0 13,176.9
Gross Profit 12,000.0 13,500.0 14,850.0 16,335.0 17,968.5 19,765.4
Selling and Adm. Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0 2,500.0
EBITDA 10,000.0 11,250.0 12,350.0 13,835.0 15,468.5 17,265.4
Depreciation 800.0 900.0 1,237.5 1,361.3 1,497.4 1,647.1
Interest 200.0 225.0 250.0 250.0 250.0 250.0
EBT 9,000.0 10,125.0 10,862.5 12,223.8 13,721.1 15,368.2
Taxes 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3 4,610.5
Net Income 6,300.0 7,087.5 7,603.8 8,556.6 9,604.8 10,757.8

Assumption Drivers - Tata Steels

Common Size Statement - Tata Steels

Change Analysis Statement - Tata Steels

Time Periods 45581 0 1 2 3 4 5

Monthly Data 1/30/1900 1/31/1900 2/28/1900 3/31/1900 4/30/1900 5/31/1900
Annual Data 12/31/1900 12/31/1900 12/31/1901 12/31/1902 12/31/1903 12/31/1904

Monthly Period 0.08611111 0.08333333 0.16111111 0.25 0.33333333 0.41666667

Annual Period 1.00277778 1 2 3 4 5
Stub or Full Year

Costing Analysis

COGS 0.1 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0 13,176.9

S&G Exp. 0.2 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0 2,500.0
Depreciation 0.2 800.0 900.0 1,237.5 1,361.3 1,497.4 1,647.1
Interest 0.3 200.0 225.0 250.0 250.0 250.0 250.0
Taxes 0.2 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3 4,610.5
Total 1.0 13,700.0 15,412.5 17,146.3 18,668.4 20,342.7 22,184.5

Average 2,740.0 3,082.5 3,429.3 3,733.7 4,068.5 4,436.9

Weighted Average 1,960.0 2,205.0 2,464.3 2,669.7 2,895.6 3,144.2
Median 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0 2,500.0

Min 200.0 225.0 250.0 250.0 250.0 250.0

Max 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0 13,176.9
Small 3 2000 2250 2500 2500 2500 2500
Large 3 2000 2250 2500 2500 2500 2500

Total Expenses
if < 17500 17,500 13,700.0 15,412.5 17,146.3 0.0 0.0 0.0
if > 17500 17,500 0.0 0.0 0.0 18,668.4 20,342.7 22,184.5
Total 13,700.0 15,412.5 17,146.3 18,668.4 20,342.7 22,184.5

Use if for error ok ok ok ok ok ok

 2026E

36,236.5
14,494.6
21,741.9
2,500.0
19,241.9
1,811.8
250.0
17,180.1
5,154.0
12,026.0

6
6/30/1900
12/31/1905

0.5
6

14,494.6
2,500.0
1,811.8
 250.0
5,154.0
24,210.4

4,842.1
3,417.6
2,500.0

250.0
14,494.6
2500
2500

0.0
24,210.4
24,210.4

ok
Tata Steels
INR (Crores) 2020A 2021A 2022E 2023E 2024E

Income Statement - Tata Steels

Assumptions Drivers - Tata Steels

Revenue Growth NA 12.50% 10.00% 10.00% 10.00%
COGS % of Revenue 40.00% 40.00% 40.00% 40.00% 40.00%
S&G Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
Depreciation % Sales 4.00% 4.00% 5.00% 5.00% 5.00%
Interest 200.0 225.0 250.0 250.0 250.0
Taxes 30% 30% 30% 30% 30%

Common Size Statement - Tata Steels

Revenue 100.00% 100.00% 100.00% 100.00% 100.00%
COGS 40.00% 40.00% 40.00% 40.00% 40.00%
Gross Profit 60.00% 60.00% 60.00% 60.00% 60.00%
Selling & General Expenses 10.00% 10.00% 10.10% 9.18% 8.35%
EBITDA 50.00% 50.00% 49.90% 50.82% 51.65%
Depreciation 4.00% 4.00% 5.00% 5.00% 5.00%
Interest 1.00% 1.00% 1.01% 0.92% 0.83%
EBT 45.00% 45.00% 43.89% 44.90% 45.82%
Taxes 13.50% 13.50% 13.17% 13.47% 13.75%
Net Income 31.50% 31.50% 30.72% 31.43% 32.07%

Change Analysis Statement - Tata Steels

10.00%
Revenue 22,000.0 24,750.0 27,225.0 29,947.5 32,942.3
COGS 8,800.0 9,900.0 10,890.0 11,979.0 13,176.9
Gross Profit 13,200.0 14,850.0 16,335.0 17,968.5 19,765.4
Selling & General Expenses 2,200.0 2,475.0 2,750.0 2,750.0 2,750.0
EBITDA 11,000.0 12,375.0 13,585.0 15,218.5 17,015.4
Depreciation 880.0 990.0 1,361.3 1,497.4 1,647.1
Interest 220.0 247.5 275.0 275.0 275.0
EBT 9,900.0 11,137.5 11,948.8 13,446.1 15,093.2
Taxes 2,970.0 3,341.3 3,584.6 4,033.8 4,528.0
Net Income 6,930.0 7,796.3 8,364.1 9,412.3 10,565.3

Time Periods 10/16/2024 0 1 2 3 4

Monthly Data 10/31/2024 11/30/2024 12/31/2024 1/31/2025 2/28/2025
Annual Data 12/31/2024 12/31/2025 12/31/2026 12/31/2027 12/31/2028

Monthly Period 0.0416666667 0.1222222222 0.2083333333 0.2916666667 0.3666666667

Annual Period 0.2083333333 1.2083333333 2.2083333333 3.2083333333 4.2083333333
 2025E 2026E

Practical Exercise you need to do

1. Start plotting common size statement - it is nothing
but every item of income statement as % of Revenue.
10.00% 10.00% 2. Copy all items of Income statement in Column B
40.00% 40.00% and paste special as Value in cell B29. Ensure you do
2,500.0 2,500.0 partial anchoring correctly, we need to lock row 9
and not columns (rewatch the video if you stuck here)
5.00% 5.00%
250.0 250.0 3.Now, Copy all items of Income statement in Column
B and paste special as Value in cell 42. Ensure you do
30% 30% absolute anchoring correctly, we need to lock both
rows and column ($C$41) (rewatch the video if you
stuck here)
100.00% 100.00% 4. Group Common size statement and Change
40.00% 40.00% Analysis
60.00% 60.00%
7.59% 6.90%
52.41% 53.10%
5.00% 5.00%
0.76% 0.69%
46.65% 47.41%
14.00% 14.22%
32.66% 33.19%

36,236.5 39,860.1
14,494.6 15,944.0
21,741.9 23,916.1
2,750.0 2,750.0
18,991.9 21,166.1
1,811.8 1,993.0
275.0 275.0
16,905.1 18,898.1
5,071.5 5,669.4
11,833.5 13,228.6

5 6
3/31/2025 4/30/2025
12/31/2029 12/31/2030

0.4583333333 0.5388888889
5.2083333333 6.2083333333
 Tata Steels
INR (Crores) 2020A 2021A 2022E 2023E 2024E

# Income Statement of Tata Steels

Revenue 20,000.0 22,500.0 24,750.0 27,225.0 29,947.5
COGS 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0
Gross Profit 12,000.0 13,500.0 14,850.0 16,335.0 17,968.5
Selling & General Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
EBITDA 10,000.0 11,250.0 12,350.0 13,835.0 15,468.5
Depreciation 800.0 900.0 1,237.5 1,361.3 1,497.4
Interest 200.0 225.0 250.0 250.0 250.0
EBT 9,000.0 10,125.0 10,862.5 12,223.8 13,721.1
Taxes 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3
Net Income 6,300.0 7,087.5 7,603.8 8,556.6 9,604.8

# Assumptions Drivers of Tata Steels

Revenue Growth NA 12.50% 10.00% 10.00% 10.00%
COGS % of Revenue 40.00% 40.00% 40.00% 40.00% 40.00%
S&G Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
Depreciation % Sales 4.00% 4.00% 5.00% 5.00% 5.00%
Interest 200.0 225.0 250.0 250.0 250.0
Taxes 30% 30% 30% 30% 30%

# Common Size Statement of Tata Steels

Revenue 100.00% 100.00% 100.00% 100.00% 100.00%
COGS 40.00% 40.00% 40.00% 40.00% 40.00%
Gross Profit 60.00% 60.00% 60.00% 60.00% 60.00%
Selling & General Expenses 10.00% 10.00% 10.10% 9.18% 8.35%
EBITDA 50.00% 50.00% 49.90% 50.82% 51.65%
Depreciation 4.00% 4.00% 5.00% 5.00% 5.00%
Interest 1.00% 1.00% 1.01% 0.92% 0.83%
EBT 45.00% 45.00% 43.89% 44.90% 45.82%
Taxes 13.50% 13.50% 13.17% 13.47% 13.75%
Net Income 31.50% 31.50% 30.72% 31.43% 32.07%

# Change Analysis of Tata Steels

% Change 10%
Revenue 22000.0 24750.0 27225.0 29947.5 32942.3
COGS 8800.0 9900.0 10890.0 11979.0 13176.9
Gross Profit 13200.0 14850.0 16335.0 17968.5 19765.4
Selling & General Expenses 2200.0 2475.0 2750.0 2750.0 2750.0
EBITDA 11000.0 12375.0 13585.0 15218.5 17015.4
Depreciation 880.0 990.0 1361.3 1497.4 1647.1
Interest 220.0 247.5 275.0 275.0 275.0
EBT 9900.0 11137.5 11948.8 13446.1 15093.2
Taxes 2970.0 3341.3 3584.6 4033.8 4528.0
Net Income 6930.0 7796.3 8364.1 9412.3 10565.3
 2025E 2026E

Practical Exercise you need to do

32,942.3 36,236.5 1. Before moving forward, you need to ensure if
13,176.9 14,494.6 everything is in order or not like linkages, formulas
and color conventions. To check this use 'Go To'
19,765.4 21,741.9 function (Ctrl+G) - Click on Special - Check formulas,
2,500.0 2,500.0 Constants, Errors.
17,265.4 19,241.9
2. Practice conditional formatting - Add a condition on
1,647.1 1,811.8 entire common size statement that every item less
250.0 250.0 than 10% should be in grey color.
15,368.2 17,180.1 3.Again, add a conditional formatiing on Change
4,610.5 5,154.0 analysis that EBITDA greater than 15000 should be
"highlighted" in green.
10,757.8 12,026.0
4. Practice Dynamic headers - Check video

10.00% 10.00%
40.00% 40.00%
2,500.0 2,500.0
5.00% 5.00%
250.0 250.0
30% 30%

100.00% 100.00%
40.00% 40.00%
60.00% 60.00%
7.59% 6.90%
52.41% 53.10%
5.00% 5.00%
0.76% 0.69%
46.65% 47.41%
14.00% 14.22%
32.66% 33.19%

36236.5 39860.1
14494.6 15944.0
21741.9 23916.1
2750.0 2750.0
18991.9 21166.1
1811.8 1993.0
 275.0 275.0
16905.1 18898.1
5071.5 5669.4
11833.5 13228.6
Basic Setup
Don’t forget to join our Free mentoring group on Telegram

Connect with Parth Sir on Linkedin

https://t.me/+hbk1ypcjgbhkMjk9

https://www.linkedin.com/in/caparthverma/
Tata Steels
INR (Crores) 2020A 2021A 2022E 2023E 2024E

Income Statement of Tata Steels

Revenue 20,000.0 22,500.0 24,750.0 27,225.0 29,947.5
COGS 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0
Gross Profit 12,000.0 13,500.0 14,850.0 16,335.0 17,968.5
Selling & General Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
EBITDA 10,000.0 11,250.0 12,350.0 13,835.0 15,468.5
Depreciation 800.0 900.0 1,237.5 1,361.3 1,497.4
Interest 200.0 225.0 250.0 250.0 250.0
EBT 9,000.0 10,125.0 10,862.5 12,223.8 13,721.1
Taxes 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3
Net Income 6,300.0 7,087.5 7,603.8 8,556.6 9,604.8

Assumptions Drivers of Tata Steels

Revenue Growth NA 12.50% 10.00% 10.00% 10.00%
COGS % of Revenue 40.00% 40.00% 40.00% 40.00% 40.00%
S&G Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
Depreciation % Sales 4.00% 4.00% 5.00% 5.00% 5.00%
Interest 200.0 225.0 250.0 250.0 250.0
Taxes 30% 30% 30% 30% 30%

Time Periods 8/15/2022 0 1 2 3 4

Monthly Data 8/31/2022 9/30/2022 10/31/2022 11/30/2022 12/31/2022
Annual Data 12/31/2022 12/31/2024 12/31/2025 12/31/2026

Monthly period 0.04444444 0.12500000 0.21111111 0.29166667 0.37777778

Annual period 0.37777778 122.62500000 2.37777778 3.37777778 4.37777778
 2025E 2026E

32,942.3 36,236.5
13,176.9 14,494.6
19,765.4 21,741.9
2,500.0 2,500.0
17,265.4 19,241.9
1,647.1 1,811.8
250.0 250.0
15,368.2 17,180.1
4,610.5 5,154.0
10,757.8 12,026.0

10.00% 10.00% Practical Exercise you need to do

40.00% 40.00%
2,500.0 2,500.0 1. To input todays date use =today(). you can
format it using formatting option (Ctrl+1)
5.00% 5.00%
250.0 250.0 2. Its very important to have strong grip on date
functions as this will be used in every financial
30% 30% model you build. Repeat what I have shown in
video.
5 6
1/31/2023 2/28/2023
12/31/2027 12/31/2028

0.46111111 0.53611111
5.37777778 6.37777778
Tata Steels
INR (Crores) 2020A 2021A 2022E 2023E 2024E

Income Statement of Tata Steels

Revenue 20,000.0 22,500.0 24,750.0 27,225.0 29,947.5
COGS 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0
Gross Profit 12,000.0 13,500.0 14,850.0 16,335.0 17,968.5
Selling & General Expenses 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
EBITDA 10,000.0 11,250.0 12,350.0 13,835.0 15,468.5
Depreciation 800.0 900.0 1,237.5 1,361.3 1,497.4
Interest 200.0 225.0 250.0 250.0 250.0
EBT 9,000.0 10,125.0 10,862.5 12,223.8 13,721.1
Taxes 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3
Net Income 6,300.0 7,087.5 7,603.8 8,556.6 9,604.8

Assumptions Drivers of Tata Steels

Time Periods 8/15/2022 0 1 2 3 4

Monthly Data 8/31/2022 9/30/2022 10/31/2022 11/30/2022 12/31/2022
Annual Data 12/31/2022 12/31/2023 12/31/2024 12/31/2025 12/31/2026

Monthly period 0.04444444 0.12500000 0.21111111 0.29166667 0.37777778

Annual period 0.37777778 1.37777778 2.37777778 3.37777778 4.37777778

Costing Analysis
COGS 0.10 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0
S&G Exp 0.20 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
Depreciation 0.20 800.0 900.0 1,237.5 1,361.3 1,497.4
Interest 0.30 200.0 225.0 250.0 250.0 250.0
Taxes 0.20 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3
Total 1.0 13,700.0 15,412.5 17,146.3 18,668.4 20,342.7

Average 2,740.0 3,082.5 3,429.3 3,733.7 4,068.5

Weighted Average 1,960.0 2,205.0 2,464.3 2,669.7 2,895.6
Median 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0

Min 200.0 225.0 250.0 250.0 250.0

Max 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0
Small 2 800.0 900.0 1,237.5 1,361.3 1,497.4
Large 3 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
 2025E 2026E

32,942.3 36,236.5
13,176.9 14,494.6
19,765.4 21,741.9
2,500.0 2,500.0
17,265.4 19,241.9
1,647.1 1,811.8
250.0 250.0
15,368.2 17,180.1
4,610.5 5,154.0
10,757.8 12,026.0

5 6
1/31/2023 2/28/2023
12/31/2027 12/31/2028

0.46111111 0.53611111
5.37777778 6.37777778

13,176.9 14,494.6
2,500.0 2,500.0 Practical Exercise you need to do
1,647.1 1,811.8
250.0 250.0 1. Practice Average, Weighted Average, Median,
Min, Max, Small, and Large functions as shown in
4,610.5 5,154.0 video.
22,184.5 24,210.4
Don't take this lightly, I have seen experienced
goofing up in these.
4,436.9 4,842.1
3,144.2 3,417.6
2,500.0 2,500.0

250.0 250.0
13,176.9 14,494.6
1,647.1 1,811.8
2,500.0 2,500.0
Tata Steels
INR (Crores) 2020A 2021A 2022E 2023E 2024E

Income Statement of Tata Steels

Assumptions Drivers of Tata Steels

Time Periods 8/15/2022 0 1 2 3 4

Monthly Data 8/31/2022 9/30/2022 10/31/2022 11/30/2022 12/31/2022
Annual Data 12/31/2022 12/31/2023 12/31/2024 12/31/2025 12/31/2026

Monthly period 0.04444444 0.12500000 0.21111111 0.29166667 0.37777778

Annual period 0.37777778 1.37777778 2.37777778 3.37777778 4.37777778
Stub or Full Year

Costing Analysis
COGS 0.10 8,000.0 9,000.0 9,900.0 10,890.0 11,979.0
S&G Expenses 0.30 2,000.0 2,250.0 2,500.0 2,500.0 2,500.0
Depreciation 0.15 800.0 900.0 1,237.5 1,361.3 1,497.4
Interest 0.20 200.0 225.0 250.0 250.0 250.0
Taxes 0.25 2,700.0 3,037.5 3,258.8 3,667.1 4,116.3
Total 1.00 13,700.0 15,412.5 17,146.3 18,668.4 20,342.7

Total Expenses
if < 17500
if >= 17500
Total

Use IF for error checking

 2025E 2026E

5 6
1/31/2023 2/28/2023
12/31/2027 12/31/2028

0.46111111 0.53611111
5.37777778 6.37777778

13,176.9 14,494.6
2,500.0 2,500.0 Practical Exercise you need to do - IFs
1,647.1 1,811.8
250.0 250.0 1. Practice If statement in row 34. Stub means less
than year.
4,610.5 5,154.0
22,184.5 24,210.4 2. Practice If statement in row 44 and 49.

Don't take this lightly, I have seen experienced

goofing up in IF statements.
Watch Session 3 for Advanced Functions

Connect with Parth Sir on Linkedin https://www.linkedin.com/

/www.linkedin.com/in/caparthverma/