Steel Corporation SA, or SteelCo, is a longestablished company in the steel sector but has been severely hit by the

economic d
The steel sector is a capital-intensive sector, both in terms of production facilities and working capital needs.

Income Statement 2014 2015 2016

Long Products (MT) 200000 170000 150000
Flat Products (MT) 70000 60000 50000
270000 230000 200000
Long Products (Rs./MT) 700 670 650
Flat Products (Rs./MT) 650 680 700

Sales-Long Products (Rs. In 000's) 140000 113900 97500

Sales-Flat Products 45500 40800 35000
Total ₹ 185,500 ₹ 154,700 ₹ 132,500
Cost of Sales ₹ 170,289 ₹ 139,696 ₹ 119,912
Gross Margin ₹ 15,211 ₹ 15,004 ₹ 12,588
Other Operating Income ₹ 3,555 ₹ 3,493 ₹ 2,539
Total Operating Income ₹ 18,766 ₹ 18,497 ₹ 15,127

Selling, General & Admn. Expenses ₹ 8,912 ₹ 8,516 ₹ 8,182

Other Operatin Expenses ₹ 707 ₹ 985 ₹ 663
Depreciation ₹ 2,195 ₹ 2,067 ₹ 2,235
Total Operating Expenses ₹ 11,814 ₹ 11,568 ₹ 11,080

EBIT ₹ 6,952 ₹ 6,929 ₹ 4,047

Net Interest Expense ₹ 4,755 ₹ 4,470 ₹ 6,357

Pre-Tax Income ₹ 2,197 ₹ 2,459 -₹ 2,310

Income Taxes ₹ 725 ₹ 811 ₹0

Net Income ₹ 1,472 ₹ 1,648 -₹ 2,310

Balance Sheet
Assets 2014 2015 2016
Cash and Marketable Securities ₹ 15,579 ₹ 12,290 ₹ 7,000
Accounts Receivable ₹ 69,969 ₹ 57,466 ₹ 47,918
Inventories ₹ 39,984 ₹ 34,962 ₹ 28,210
Other Current Assets ₹ 3,736 ₹ 2,483 ₹ 4,230
Total Current Assets ₹ 129,268 ₹ 107,201 ₹ 87,358

Property, Plant and Equipment, Gross ₹ 58,850 ₹ 61,440 ₹ 61,840

(-) Accumulated Depreciation -₹ 15,000 -₹ 17,067 -₹ 19,302
Property, Plant and Equipment, Net ₹ 43,850 ₹ 44,373 ₹ 42,538
Other Non-Current Assets ₹ 18,840 ₹ 18,840 ₹ 18,840
Total Non-Current Assets ₹ 62,690 ₹ 63,213 ₹ 61,378

Total Assets ₹ 191,958 ₹ 170,414 ₹ 148,736

Liabilities and Shareholders' Equity
Accounts Payable ₹ 19,129 ₹ 14,542 ₹ 9,856
Other Current Liabilities ₹ 4,547 ₹ 3,347 ₹ 4,146
Short term debt ₹ 76,167 ₹ 61,795 ₹ 49,347
Total Current Liabilities ₹ 99,843 ₹ 79,684 ₹ 63,349

Long-Term Debt ₹ 59,383 ₹ 56,350 ₹ 53,317

Total Non Current Liabilities ₹ 59,383 ₹ 56,350 ₹ 53,317

Share Capital ₹ 31,260 ₹ 31,260 ₹ 31,260

Retained earnings ₹ 1,472 ₹ 3,120 ₹ 810
Total Shareholders' Equity ₹ 32,732 ₹ 34,380 ₹ 32,070

Total Liabilities and Shareholders' Equity ₹ 191,958 ₹ 170,414 ₹ 148,736

been severely hit by the economic downturn.
king capital needs.

Four year forecasted financials are required for the company. Below information is given for that purpose:

1. SteelCo’s revenues can be grouped into 2 product groups. The “long” products group and the “flat” products group.
The quantity for the forecast period is to be calculated by using Linear forecast on the historical rate of change
2. The Selling price for the forecast period is to be calculated as minimum of historical prices
3. For the purpose of forecasting the Gross Profit, average of historical GP margin will be used
4. Other Operating Income & Other Operating Expenses will be calculated by using linear forecast on the historical percentage
5. 70% of operating expenses as fixed, which are increased in line with the annual inflation rate, and 30% of them as variable,
assuming constant inflation over the years equal to 2%. Operating Expenses forecaset is to be made by keeping 2016 as bas
6. Working capital components to be estimated using Linear forecast on the operating cycle
7. The interest expense is comprised of the interest that the company pays for its short-term debt plus the interest that it pays
long-term debt. Interest cost on the long term and short term debts are 5.7% p.a. and 6.7% p.a (to be calculated on closing
8. Interest income from the cash deposits of the firm is 1% (to be calculated on closing balance)
9. CAPEX will be kept to a minimal Rs. 300k in each of the years
10. Depreciation will be based on the average rate of depreciation on closing gross PPE
11. The company has to repay the principal lof long term debt in annual installemts of Rs. 6033,000
12. The minimum cash balance for SteelCo is set at Rs. 6,500k.
13. Short term debt to be used as the plug
14. Other Non current assets is assumed as constant

Also you are required to :

Construct a control panel allowing the user to change the below mentioned inputs (with developer function) inorder to obtain
Input
Inflation rate
Interest on Long term Debt
Interest on Short term Debt
Long term debt repayment
Cash Requirement

Output
Gross Profit
Net Profit
Debt Equity ratio
Long term & Short term debt amounts
Revenue Forcast
Years 2014 2015 2016 2017 2018
Long Products (MT) 200000 170000 150000 137206 129942.042
Growth rate -0.15 -0.117647 -0.0853 -0.05294118

Flat Products (MT) 70000 60000 50000 40476.2 31802.7211

Growth rate -0.14286 -0.166667 -0.1905 -0.21428571

Long Products (Rs./MT) 700 670 650 650 650

Flat Products (Rs./MT) 650 680 700 650 650

Sales-Long Products (Rs. In 000's) 140000 113900 97500 89183.8 84462.327

Sales-Flat Products 45500 40800 35000 26309.5 20671.7687
TOTAL SALES 185500 154700 132500 115493 105134.096

2014 2015 2016 2017 2018

Gross Profit 15211 15004 12588 10548.1 9601.94924
GPM 0.082 0.096988 0.095004 0.09133 0.0913305

2014 2015 2016 2017 2018

Other Operating Income 3555 3493 2539 2344.49 2134.0878
As a % of sales 0.019164 0.022579 0.019162 0.0203 0.02029872

2014 2015 2016 2017 2018

Other Operatin Expenses 707 985 663 722.204 720.108781
As a % of sales 0.003811 0.006367 0.005004 0.00625 0.00684943

2016 2017 2018

Selling, General & Admn. Expenses 8182 8022.64 7943.88043
Fixed 70% 5727.4 5841.95 5958.78696
Variable 30% 2454.6 2180.69 1985.09347
Inflation rate 2% 2 0.012273

Working Capital
No of days 365
Current Assets 2014 2015 2016 2017 2018
Cash 15579 12290 7000 6500 6500
Accounts Receivables 69969 57466 47918 48052.8 44819.4749
Sales 185500 154700 132500 115493 105134.096
DTR 2.651174 2.427904 2.514613
ACP 137.6749 150.3354 145.1515 151.864 155.602312
Inventories 39984 34962 28210 29814.4 28506.7261
COGS 170289 139696 119912 104945 95532.1465
ITR 4.258929 3.727911 3.796365
IHP 85.70231 97.91007 96.14459 103.695 108.915746

2014 2015 2016 2017 2018

Other Current Assets 3736 2483 4230 3983.3 4245.48159
As a % Sales 0.02014 0.01605 0.031925 0.03449 0.04038159

2014 2015 2016 2017 2018

Accounts Payable 19129 14542 9856 10591.8 9135.49854
COGS 170289 139696 119912 104945 95532.1465
CTR 8.902138 8.297704 9.829658
CPP 41.00139 43.98807 37.13252 36.8385 34.9040307

2014 2015 2016 2017 2018

Other Current Liabilities 4547 3347 4146 3808.02 3842.55
As a % COGS 0.026702 0.023959 0.034575 0.03629 0.0402226

2014 2015 2016 2017 2018

Debt
LTD 59383 56350 53317 47284 41251
STD 76167 61795 49347 58689.3 65787.3007
Repayment of LTD 6033 6033

2017 2018
Interest Exp LTD 2695.19 2351.307
STD 3932.18 4407.74914
Total 6627.37 6759.05614

Interest income 2017 2018

65 65
2014 2015 2016 2017 2018
Net Interest exp 4755 4470 6357 6562.37 6694.05614

2017 2018
Capex 300 300

2014 2015 2016 2017 2018

Gross PPE 58850 61440 61840 62140 62440
Depreciation 2195 2067 2235 2218.03 2228.74272
As a % of gross PPE 0.037298 0.033643 0.036142 0.03569 0.03569415

Effective Tax rate 2015

Income tax 811
PBT 2459
Effective tax rate 0.329809
 2019 2020
127266.8 128764
-0.020588 0.011765

24230.64 17884.52
-0.238095 -0.261905

650 650
650 650

82723.4 83696.61
15749.92 11624.94
98473.32 95321.55

2019 2020
8993.617 8705.765
0.09133 0.09133

2019 2020
1998.776 1934.7
0.020298 0.020297

2019 2020
733.1986 766.5649
0.007446 0.008042

2019 2020
7937.29 7999.34
6077.963 6199.522
1859.328 1799.818

2019 2020
6500 6500
42988.5 42588.89
98473.32 95321.55

159.3407 163.079
27980.64 28324.08
89479.7 86615.79

114.1369 119.358

2019 2020
4556.732 4972.54
0.046274 0.052166

2019 2020
8082.493 7364.755
89479.7 86615.79

32.9696 31.03517

2019 2020
3951.37 4165.90
0.044159 0.048096

2019 2020

35218 29185
75711.9 88565.63
6033 6033

2019 2020
2007.426 1663.545 LTD 5.70% 57
5072.697 5933.897 STD 6.70% 67
7080.123 7597.442
Interest ra 1.00% 1
2019 2020
65 65
2019 2020
7015.123 7532.442

2019 2020
300 300

2019 2020
62740 63040
2239.451 2250.159
0.035694 0.035694
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Income Statement 2014 2015 2016 2017(P)
Long Products (MT) 200000 170000 150000 137205.88235
Flat Products (MT) 70000 60000 50000 40476.190476
270000 230000 200000 177682.07283
Long Products (Rs./MT) 700 670 650 650
Flat Products (Rs./MT) 650 680 700 650

Sales-Long Products (Rs. In 000's) 140000 113900 97500 89183.823529

Sales-Flat Products 45500 40800 35000 26309.52381
Total ₹ 185,500 ₹ 154,700 ₹ 132,500 ₹ 115,493
Cost of Sales ₹ 170,289 ₹ 139,696 ₹ 119,912 ₹ 104,945
Gross Margin ₹ 15,211 ₹ 15,004 ₹ 12,588 10548.064841
Other Operating Income ₹ 3,555 ₹ 3,493 ₹ 2,539 2344.4918967
Total Operating Income ₹ 18,766 ₹ 18,497 ₹ 15,127 ₹ 12,893

Selling, General & Admn. Expenses ₹ 8,912 ₹ 8,516 ₹ 8,182 8022.6400798

Other Operatin Expenses ₹ 707 ₹ 985 ₹ 663 722.20358011
Depreciation ₹ 2,195 ₹ 2,067 ₹ 2,235 2218.0344771
Total Operating Expenses ₹ 11,814 ₹ 11,568 ₹ 11,080 ₹ 10,963

EBIT ₹ 6,952 ₹ 6,929 ₹ 4,047 ₹ 1,930

Net Interest Expense ₹ 4,755 ₹ 4,470 ₹ 6,357 6562.3723741

Pre-Tax Income ₹ 2,197 ₹ 2,459 -₹ 2,310 -₹ 4,633

Income Taxes ₹ 725 ₹ 811 ₹0 0

Net Income ₹ 1,472 ₹ 1,648 -₹ 2,310 -₹ 4,633
2018(P) 2019(P) 2020(P)
129942.04152 127266.7642 128764.02025
31802.721088 24230.644639 17884.523424
161744.76261 151497.40884 146648.54367
650 650 650
650 650 650

84462.32699 82723.396728 83696.61316

20671.768707 15749.919015 11624.940226
₹ 105,134 ₹ 98,473 ₹ 95,322
₹ 95,532 ₹ 89,480 ₹ 86,616
9601.9492379 8993.6168927 8705.7648693
2134.0878041 1998.7763114 1934.7001884
₹ 11,736 ₹ 10,992 ₹ 10,640

7943.8804315 7937.2903978 7999.3395296

720.10878066 733.19861704 766.56490094
2228.7427221 2239.4509671 2250.1592121
₹ 10,893 ₹ 10,910 ₹ 11,016

₹ 843 ₹ 82 -₹ 376

6694.0561444 7015.1230052 7532.4418895

-₹ 5,851 -₹ 6,933 -₹ 7,908

0 0 0
-₹ 5,851 -₹ 6,933 -₹ 7,908
Balance Sheet
Assets 2014 2015 2016 2017(P)
Cash and Marketable Securities ₹ 15,579 ₹ 12,290 ₹ 7,000 6500
Accounts Receivable ₹ 69,969 ₹ 57,466 ₹ 47,918 48052.815229
Inventories ₹ 39,984 ₹ 34,962 ₹ 28,210 29814.409838
Other Current Assets ₹ 3,736 ₹ 2,483 ₹ 4,230 3983.2966358
Total Current Assets ₹ 129,268 ₹ 107,201 ₹ 87,358 ₹ 88,351

Property, Plant and Equipment, Gross ₹ 58,850 ₹ 61,440 ₹ 61,840 62140

(-) Accumulated Depreciation -₹ 15,000 -₹ 17,067 -₹ 19,302 -₹ 21,520
Property, Plant and Equipment, Net ₹ 43,850 ₹ 44,373 ₹ 42,538 ₹ 40,620
Other Non-Current Assets ₹ 18,840 ₹ 18,840 ₹ 18,840 ₹ 18,840
Total Non-Current Assets ₹ 62,690 ₹ 63,213 ₹ 61,378 ₹ 59,460

Total Assets ₹ 191,958 ₹ 170,414 ₹ 148,736 ₹ 147,810

Liabilities and Shareholders' Equity

Accounts Payable ₹ 19,129 ₹ 14,542 ₹ 9,856 10591.843435
Other Current Liabilities ₹ 4,547 ₹ 3,347 ₹ 4,146 3808.02
Short term debt ₹ 76,167 ₹ 61,795 ₹ 49,347 58689.319016
Total Current Liabilities ₹ 99,843 ₹ 79,684 ₹ 63,349 ₹ 73,089

Long-Term Debt ₹ 59,383 ₹ 56,350 ₹ 53,317 47284

Total Non Current Liabilities ₹ 59,383 ₹ 56,350 ₹ 53,317 ₹ 47,284

Share Capital ₹ 31,260 ₹ 31,260 ₹ 31,260 ₹ 31,260

Retained earnings ₹ 1,472 ₹ 3,120 ₹ 810 -₹ 3,823
Total Shareholders' Equity ₹ 32,732 ₹ 34,380 ₹ 32,070 ₹ 27,437

Total Liabilities and Shareholders' Equity ₹ 191,958 ₹ 170,414 ₹ 148,736 ₹ 147,810

Diff ₹0 ₹0 ₹0 ₹0
2018(P) 2019(P) 2020(P)
6500 6500 6500
44819.474916 42988.501587 42588.888485
28506.726132 27980.642112 28324.081803
4245.4815897 4556.7315525 4972.5396904
₹ 84,072 ₹ 82,026 ₹ 82,386

62440 62740 63040

-₹ 23,749 -₹ 25,988 -₹ 28,238
₹ 38,691 ₹ 36,752 ₹ 34,802
₹ 18,840 ₹ 18,840 ₹ 18,840
₹ 57,531 ₹ 55,592 ₹ 53,642

₹ 141,603 ₹ 137,618 ₹ 136,027

9135.4985429 8082.4925263 7364.7546781

3842.55 3951.37 4165.90
65787.300663 75711.8956 88565.625216
₹ 78,765 ₹ 87,746 ₹ 100,096

41251 35218 29185

₹ 41,251 ₹ 35,218 ₹ 29,185

₹ 31,260 ₹ 31,260 ₹ 31,260

-₹ 9,673 -₹ 16,606 -₹ 24,514
₹ 21,587 ₹ 14,654 ₹ 6,746

₹ 141,603 ₹ 137,618 ₹ 136,027

₹0 ₹0 ₹0
Cash Flow statement 2017(P) 2018(P) 2019(P) 2020(P)
Cash flow from Operating Activity
PBT -4632.69 -5850.75 -6932.67 -7908.04
Adjustments:
Depreciation 2218.034 2228.743 2239.451 2250.159
Net Interest exp 6562.372 6694.056 7015.123 7532.442
Changes in WC -1094.66 2857.03 1101.62 -862.85
CFO 3053.053 5929.074 3423.528 1011.712
Tax Paid 0 0 0 0
Net CFO 3053.053 5929.074 3423.528 1011.712

Cash flow from Investing Activity

Capex -300 -300 -300 -300
Interest income 65 65 65 65
Net CFI -235 -235 -235 -235

Cash flow from financing activity

LTD -6033 -6033 -6033 -6033
Interest exps -6627.37 -6759.06 -7080.12 -7597.44
CFF -12660.4 -12792.1 -13113.1 -13630.4

OB 7000 6500 6500 6500

Net cash during the year -9842.32 -7097.98 -9924.59 -12853.7
CB 6500 6500 6500 6500

STD 9342.319 7097.982 9924.595 12853.73

Output 2017(P) 2018(P)
Gross Profit 10548.065 9601.9492
Net Profit -4632.6938 -5850.751
Debt Equity ratio 3.8623806 4.9585633
Long term & Short term debt amounts 105973.32 107038.3
2019(P) 2020(P)
8993.6169 8705.7649
-6932.6698 -7908.0405
7.5699988 17.455282
110929.9 117750.63
Inflation 2

Interest on Long term Debt 57

Interest on Short term Debt 67

Long term debt repayment 6033

Cash Requirement 6500