Exhibit 5
GUNA FIBRES, LTD.
Monthly Financial Statement Forecast (in thousands of Indian rupees)
Assumptions
Excise Tax Rate
Cost of Goods Sold / Gr Sales
Annual Operating Expenses / Annual Gr Sales
Depreciation / Gross PP&E
Interest Rate on Borrowings (and Deposits)
Income Tax Rate
Dividends Paid (000s in March, June, Sep, Dec)
Gross Sales (1)
Excise Taxes (2)
Net Sales
Cost of Goods Sold
Gross Profit
Operating Expenses (3)
Depreciation (4)
Interest Expense (5)
Profit Before Taxes
Income Taxes
Net Profit
Dividend
Minimum Cash Balance (000s)
Accounts Receivable Collection
In One Month
In Two Months
Purchases / Gr Sales in Two Months
Direct Labour / Purchases Last Month
Capital Expenditures (every third month)
Accounts payable / Purchases
750
40%
60%
55%
34%
350
50%
Full year
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12
2012 Jan-13 Feb-13
2,717 2,214 2,616 2,892 4,447 8,804 13,885 17,588 16,315 8,576 5,031 4,447 3,531 2,767 90,899 3,401 3,616
392
434
667 1,321
2,083 2,638 2,447 1,286
755
667
530
415 13,635
2,224 2,458 3,780 7,483 11,802 14,950 13,868 7,290 4,276 3,780 3,001 2,352 77,264
1,928 2,131 3,277 6,489 10,233 12,962 12,024 6,321 3,708 3,277 2,602 2,039 66,993
296
327
503
995
1,569 1,987 1,844
969
569
503
399
313 10,272
454
454
454
454
454
454
454
454
454
454
454
454 5,454
84
84
87
87
87
90
90
90
93
93
93
96 1,074
10
21
57
144
261
372
417
336
198
105
62
44 2,026
-253
-233
-96
309
767 1,071
882
88
-177
-150
-211
-281 1,717
-76
-70
-29
93
230
321
265
26
-53
-45
-63
-84
515
-177
-163
-67
217
537
750
617
62
-124
-105
-147
-197 1,202
500
500
500
500
Dec-11 Jan-12
Cash
762
750
Accounts Receivable (6)
2,673 2,773
Inventory
3,450 4,509
Total Current Assets
6,885 8,032
Gross Plant, Property, and Equip (7) 10,096 10,096
Accumulated Depreciation
1,484 1,568
Net Plant, Property, and Equipment
8,612 8,527
Total Assets
15,497 16,559
Accounts Payable (8)
Note Payable (9)
Accrued Taxes (10)
Total Current Liabilities
Shareholders' Equity (11)
Total Liabilities & Equity
15%
73.7%
6.0%
10%
14.5%
30%
500
Feb-12
750
3,291
8,051
12,092
10,096
1,652
8,443
20,535
Mar-12
750
5,011
14,057
19,818
10,446
1,739
8,706
28,524
Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
750
750
750
750
750
750
10,301 17,996 24,748 25,697 17,194 9,006
19,838 21,867 16,672 9,019 6,085 5,151
30,889 40,613 42,170 35,466 24,029 14,907
10,446 10,446 10,796 10,796 10,796 11,146
1,826
1,913 2,003 2,093 2,183 2,276
8,619
8,532 8,792 8,702 8,612 8,869
39,508 49,146 50,963 44,168 32,641 23,776
822 1,223 2,421
798 1,712 4,722
-90
-166
-236
1,530 2,769 6,908
13,967 13,790 13,627
15,497 16,559 20,535
3,818
11,910
-265
15,464
13,060
28,524
4,837
21,567
-172
26,232
13,277
39,508
4,487
30,787
58
35,332
13,813
49,146
2,358
34,541
0
36,899
14,063
50,963
1,384
27,840
265
29,488
14,680
44,168
Oct-12 Nov-12 Dec-12
750
750
750
6,295 5,028 3,715
4,056 3,841 4,427
11,100 9,619 8,891
11,146 11,146 11,496
2,369 2,462 2,558
8,777 8,684 8,938
19,877 18,303 17,829
1,223
971
761
935
994
16,385 8,687 5,147 3,610 3,858
291
0
-45
-108
-192
17,899 9,658 5,863 4,437 4,660
14,742 14,118 14,013 13,866 13,169
32,641 23,776 19,877 18,303 17,829
Inventory Detail
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12
2012
Purchases (12)
1,439 1,591 2,446 4,842 7,637 9,673
8,973 4,717 2,767 2,446 1,942 1,522 1,871 1,989 50,824
Direct Labour & Other Mftg Costs (13) 489
541
832 1,646 2,596
3,289 3,051 1,604
941
832
660
517
636 17,145
Cost of Goods Sold
1,928 2,131 3,277 6,489 10,233 12,962 12,024 6,321 3,708 3,277 2,602 2,039
Inventory (14)
3,450
4,509
8,051 14,057 19,838
21,867 16,672
Notes:
(1) Follows forecast in Exhibit 2
(2) Gross Sales * Exercise Tax Rate
(3) Annual Operating Expenses / 12
(4) Gross PPE * Depreciation Rate / 12
(5) Notes Payable (t - 1) * Interest Rate / 12
(6) AR(t - 1) + GSales(t) - 40% * GSales(t - 2) - 60% x GSales(t - 2)
(7) GPPE(t -1) + Capex(t)
(8) 50% * Purchases(t)
(9) Total Assets - AP - AccTax - ShrEquity
(10) AccTax(t - 1) + IncTax(t) or 0 if positive balance and month of quarterly payment
(11) ShrEquity(t - 1) + NetProfit(t) - Dividend(t)
(12) 55% * GSales(t + 2)
(13) 35% * Purchases(t-1)
(14) Inventory(t - 1) + Purchases(t) + Direct Labour(t) - COGS(t)
9,019
6,085
5,151
4,056
3,841
4,427
This spreadsheet supports STUDENT analysis of the case “Guna Fibres, Ltd.” (Case 12).
This spreadsheet was prepared by Michael J. Schill, Associate Professor of Business Administration. Copyright © 2013 by the University of Virginia Darden School
Foundation, Charlottesville, VA. All rights reserved. For customer service inquiries, send an e-mail to sales@dardenbusinesspublishing.com. No part of this
publication may be reproduced, stored in a retrieval system, posted to the Internet, or transmitted in any form or by any means—electronic, mechanical, photocopying,
recording, or otherwise—without the permission of the Darden School Foundation.
Feb. 4, 2013
Exhibit 1
GUNA FIBRES, LTD.
Guna Fibres Annual Income Statements (in thousands of Indian rupees)
Gross Sales
Excise Tax
Net Sales
Cost of Goods
Gross Profits
Operating Expenses
Depreciation
Interest Expense
Profit Before Tax
Income Tax
Net Profit
2010
64,487
9,673
54,814
44,496
10,318
3,497
769
910
5,142
1,545
3,597
2011
75,867
11,380
64,487
53,866
10,621
4,829
909
1,240
3,644
1,093
2,551
Cash
Accounts Receivable
Inventory
Total Current Assets
Gross Plant, Property, and Equipment
Accumulated Depreciation
Net Plant, Property, and Equipment
Total Assets
895
2,390
2,974
6,259
8,868
1,170
7,698
13,957
762
2,673
3,450
6,885
10,096
1,484
8,612
15,497
Accounts Payable
Notes to Bank
Accrued Taxes
Total Current Liabilities
Owners' Equity
Total Liabilities and Equity
603
0
-62
541
13,416
13,957
822
798
-90
1,530
13,967
15,497
Exhibit 2
GUNA FIBRES, LTD.
Guna Fibres Monthly Sales, 2011 Actual and 2012 Forecast
(in thousands of rupees)
January
February
March
April
May
June
July
August
September
October
November
December
Year
2011
2012
(Actual) (Forecast)
2,012
2,616
2,314
2,892
3,421
4,447
7,043
8,804
12,074
13,885
15,294
17,588
14,187
16,315
7,144
8,576
4,025
5,031
3,421
4,447
2,717
3,531
2,214
2,767
75,867
90,899 Too optimistic, about 20% sales growth
Exhibit 5
GUNA FIBRES, LTD.
Monthly Financial Statement Forecast (in thousands of Indian rupees)
Assumptions
Excise Tax Rate
Cost of Goods Sold / Gr Sales
Annual Operating Expenses / Annual Gr Sales
Depreciation / Gross PP&E
Interest Rate on Borrowings (and Deposits)
Income Tax Rate
Dividends Paid (000s in March, June, Sep, Dec)
Gross Sales (1)
Excise Taxes (2)
Net Sales
Cost of Goods Sold
Gross Profit
Operating Expenses (3)
Depreciation (4)
Interest Expense (5)
Profit Before Taxes
Income Taxes
Net Profit
Dividend
Minimum Cash Balance (000s)
Accounts Receivable Collection
In One Month
In Two Months
Purchases / Gr Sales in Two Months
Direct Labour / Purchases Last Month
Capital Expenditures (every third month)
Accounts payable / Purchases
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
2,717 2,214 2,616 2,892 4,447 8,804 13,885 17,588 16,315 8,576 5,031
392
434
667 1,321
2,083 2,638 2,447 1,286
755
2,224 2,458 3,780 7,483 11,802 14,950 13,868 7,290 4,276
1,577 1,744 2,682 5,309
8,373 10,606 9,838 5,171 3,034
646
714 1,098 2,175
3,430 4,344 4,030 2,118 1,243
454
454
454
454
454
454
454
454
454
84
84
87
87
87
90
90
90
93
10
17
43
114
206
289
335
241
94
98
159
514 1,519
2,682 3,511 3,150 1,333
601
29
48
154
456
805 1,053
945
400
180
69
111
359 1,063
1,877 2,458 2,205
933
421
500
500
500
Dec-11 Jan-12
Cash
762
750
Accounts Receivable (6)
2,673 2,773
Inventory
3,450 4,316
Total Current Assets
6,885 7,839
Gross Plant, Property, and Equip (7) 10,096 10,096
Accumulated Depreciation
1,484 1,568
Net Plant, Property, and Equipment
8,612 8,527
Total Assets
15,497 16,367
Accounts Payable (8)
Note Payable (9)
Accrued Taxes (10)
Total Current Liabilities
Shareholders' Equity (11)
Total Liabilities & Equity
15%
60.3%
6.0%
10%
14.5%
30%
500
Feb-12
750
3,291
7,214
11,255
10,096
1,652
8,443
19,698
Mar-12
750
5,011
12,128
17,889
10,446
1,739
8,706
26,596
Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
750
750
750
750
750
750
10,301 17,996 24,748 25,697 17,194 9,006
16,858 18,518 14,268 8,006 5,606 4,842
27,910 37,265 39,766 34,453 23,550 14,597
10,446 10,446 10,796 10,796 10,796 11,146
1,826
1,913 2,003 2,093 2,183 2,276
8,619
8,532 8,792 8,702 8,612 8,869
36,529 45,797 48,559 43,155 32,162 23,467
822 1,001 1,981 3,124 3,957
798 1,392 3,584 9,465 17,047
-90
-61
-13
0
456
1,530 2,331 5,552 12,589 21,460
13,967 14,036 14,147 14,006 15,069
15,497 16,367 19,698 26,596 36,529
3,671
23,919
1,260
28,850
16,947
45,797
1,930
27,725
0
29,654
18,904
48,559
Inventory Detail
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12
Purchases (12)
1,177 1,301 2,001 3,962 6,248 7,915
7,342 3,859
Direct Labour & Other Mftg Costs (13) 400
442
680 1,347 2,124
2,691 2,496
Cost of Goods Sold
1,577 1,744 2,682 5,309
8,373 10,606
750
40%
60%
45% changed from 55%
34%
350
50%
Full year
Oct-12 Nov-12 Dec-12
2012 Jan-13 Feb-13
4,447 3,531 2,767 90,899 3,401 3,616
667
530
415 13,635
3,780 3,001 2,352 77,264
2,682 2,129 1,669 54,812
1,098
872
683 22,452
454
454
454 5,454
93
93
96 1,074
9
-41
-65 1,252
542
365
198 14,672
163
110
60 4,402
380
256
139 10,271
500
Oct-12 Nov-12 Dec-12
750
750
750
6,295 5,028 3,715
3,945 3,770 4,249
10,990 9,548 8,714
11,146 11,146 11,496
2,369 2,462 2,558
8,777 8,684 8,938
19,767 18,232 17,652
1,132 1,001
794
623
19,969 7,775
709 -3,362
945 1,345
0
163
22,046 10,120 1,503 -2,576
21,110 22,042 21,963 22,343
43,155 32,162 23,467 19,767
765
-5,404
272
-4,367
22,599
18,232
814
-5,400
0
-4,586
22,238
17,652
Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12
2012
2,264 2,001 1,589 1,245 1,530 1,627 41,584
1,312
770
680
540
423
520 14,028
9,838 5,171 3,034 2,682 2,129 1,669
Inventory (14)
3,450
4,316
7,214 12,128 16,858
18,518 14,268
Notes:
(1) Follows forecast in Exhibit 2
(2) Gross Sales * Exercise Tax Rate
(3) Annual Operating Expenses / 12
(4) Gross PPE * Depreciation Rate / 12
(5) Notes Payable (t - 1) * Interest Rate / 12
(6) AR(t - 1) + GSales(t) - 40% * GSales(t - 2) - 60% x GSales(t - 2)
(7) GPPE(t -1) + Capex(t)
(8) 50% * Purchases(t)
(9) Total Assets - AP - AccTax - ShrEquity
(10) AccTax(t - 1) + IncTax(t) or 0 if positive balance and month of quarterly payment
(11) ShrEquity(t - 1) + NetProfit(t) - Dividend(t)
(12) 55% * GSales(t + 2)
(13) 35% * Purchases(t-1)
(14) Inventory(t - 1) + Purchases(t) + Direct Labour(t) - COGS(t)
8,006
5,606
4,842
3,945
3,770
4,249
Exhibit 5
GUNA FIBRES, LTD.
Monthly Financial Statement Forecast (in thousands of Indian rupees)
Assumptions
Excise Tax Rate
Cost of Goods Sold / Gr Sales
Annual Operating Expenses / Annual Gr Sales
Depreciation / Gross PP&E
Interest Rate on Borrowings (and Deposits)
Income Tax Rate
Dividends Paid (000s in March, June, Sep, Dec)
Gross Sales (1)
Excise Taxes (2)
Net Sales
Cost of Goods Sold
Gross Profit
Operating Expenses (3)
Depreciation (4)
Interest Expense (5)
Profit Before Taxes
Income Taxes
Net Profit
Dividend
Minimum Cash Balance (000s)
Accounts Receivable Collection
In One Month
In Two Months
Purchases / Gr Sales in Two Months
Direct Labour / Purchases Last Month
Capital Expenditures (every third month)
Accounts payable / Purchases
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
2,717 2,214 2,616 2,892 4,447 8,804 13,885 17,588 16,315 8,576 5,031
392
434
667 1,321
2,083 2,638 2,447 1,286
755
2,224 2,458 3,780 7,483 11,802 14,950 13,868 7,290 4,276
1,856 2,052 3,155 6,246
9,851 12,479 11,575 6,085 3,569
368
406
625 1,237
1,951 2,471 2,292 1,205
707
454
454
454
454
454
454
454
454
454
84
84
87
87
87
90
90
90
93
10
20
55
139
251
356
401
317
176
-181
-152
29
557
1,159 1,571 1,347
344
-17
-54
-46
9
167
348
471
404
103
-5
-127
-106
20
390
811 1,099
943
241
-12
500
500
500
Dec-11 Jan-12
Cash
762
750
Accounts Receivable (6)
2,673 2,773
Inventory
3,450 4,501
Total Current Assets
6,885 8,024
Gross Plant, Property, and Equip (7) 10,096 10,096
Accumulated Depreciation
1,484 1,568
Net Plant, Property, and Equipment
8,612 8,527
Total Assets
15,497 16,552
Accounts Payable (8)
Note Payable (9)
Accrued Taxes (10)
Total Current Liabilities
Shareholders' Equity (11)
Total Liabilities & Equity
15%
71.0%
6.0%
10%
14.5%
30%
500
Feb-12
750
3,291
8,001
12,041
10,096
1,652
8,443
20,485
Mar-12
750
5,011
13,887
19,648
10,446
1,739
8,706
28,354
Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
750
750
750
750
750
750
10,301 17,996 24,748 25,697 17,194 9,006
19,528 21,455 16,296 8,855 6,019 5,101
30,579 40,202 41,794 35,302 23,963 14,856
10,446 10,446 10,796 10,796 10,796 11,146
1,826
1,913 2,003 2,093 2,183 2,276
8,619
8,532 8,792 8,702 8,612 8,869
39,199 48,734 50,586 44,004 32,575 23,726
822 1,223 2,421
798 1,633 4,519
-90
-144
-190
1,530 2,711 6,751
13,967 13,840 13,734
15,497 16,552 20,485
3,818
11,463
-181
15,100
13,254
28,354
4,837
20,732
-14
25,555
13,644
39,199
4,487
29,459
333
34,279
14,455
48,734
2,358
33,173
0
35,531
15,055
50,586
1,384
26,219
404
28,007
15,997
44,004
750
40%
60%
55%
29% changed from 34%
350
50%
Full year
Oct-12 Nov-12 Dec-12
2012 Jan-13 Feb-13
4,447 3,531 2,767 90,899 3,401 3,616
667
530
415 13,635
3,780 3,001 2,352 77,264
3,155 2,505 1,963 64,493
625
496
389 12,771
454
454
454 5,454
93
93
96 1,074
85
41
21 1,870
-7
-92
-183 4,374
-2
-28
-55 1,312
-5
-64
-128 3,062
500
Oct-12 Nov-12 Dec-12
750
750
750
6,295 5,028 3,715
4,030 3,837 4,405
11,075 9,615 8,870
11,146 11,146 11,496
2,369 2,462 2,558
8,777 8,684 8,938
19,852 18,299 17,808
1,223
971
761
935
994
14,607 7,028 3,372 1,737 1,869
507
0
-2
-30
-85
16,337 7,999 4,131 2,642 2,779
16,238 15,726 15,721 15,656 15,029
32,575 23,726 19,852 18,299 17,808
Inventory Detail
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12
2012
Purchases (12)
1,439 1,591 2,446 4,842 7,637 9,673
8,973 4,717 2,767 2,446 1,942 1,522 1,871 1,989 50,824
Direct Labour & Other Mftg Costs (13) 417
461
709 1,404 2,215
2,805 2,602 1,368
802
709
563
441
542 14,624
Cost of Goods Sold
1,856 2,052 3,155 6,246
9,851 12,479 11,575 6,085 3,569 3,155 2,505 1,963
Inventory (14)
3,450
4,501
8,001 13,887 19,528
21,455 16,296
Notes:
(1) Follows forecast in Exhibit 2
(2) Gross Sales * Exercise Tax Rate
(3) Annual Operating Expenses / 12
(4) Gross PPE * Depreciation Rate / 12
(5) Notes Payable (t - 1) * Interest Rate / 12
(6) AR(t - 1) + GSales(t) - 40% * GSales(t - 2) - 60% x GSales(t - 2)
(7) GPPE(t -1) + Capex(t)
(8) 50% * Purchases(t)
(9) Total Assets - AP - AccTax - ShrEquity
(10) AccTax(t - 1) + IncTax(t) or 0 if positive balance and month of quarterly payment
(11) ShrEquity(t - 1) + NetProfit(t) - Dividend(t)
(12) 55% * GSales(t + 2)
(13) 35% * Purchases(t-1)
(14) Inventory(t - 1) + Purchases(t) + Direct Labour(t) - COGS(t)
8,855
6,019
5,101
4,030
3,837
4,405
Exhibit 5
GUNA FIBRES, LTD.
Monthly Financial Statement Forecast (in thousands of Indian rupees)
Assumptions
Excise Tax Rate
Cost of Goods Sold / Gr Sales
Annual Operating Expenses / Annual Gr Sales
Depreciation / Gross PP&E
Interest Rate on Borrowings (and Deposits)
Income Tax Rate
Dividends Paid (000s in March, June, Sep, Dec)
Gross Sales (1)
Excise Taxes (2)
Net Sales
Cost of Goods Sold
Gross Profit
Operating Expenses (3)
Depreciation (4)
Interest Expense (5)
Profit Before Taxes
Income Taxes
Net Profit
Dividend
Minimum Cash Balance (000s)
Accounts Receivable Collection
In One Month
In Two Months
Purchases / Gr Sales in Two Months
Direct Labour / Purchases Last Month
Capital Expenditures (every third month)
Accounts payable / Purchases
750
40%
60%
55%
34%
350
50%
Full year
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12
2012 Jan-13 Feb-13
2,717 2,214 2,616 2,892 4,447 8,804 13,885 17,588 16,315 8,576 5,031 4,447 3,531 2,767 90,899 3,401 3,616
392
434
667 1,321
2,083 2,638 2,447 1,286
755
667
530
415 13,635
2,224 2,458 3,780 7,483 11,802 14,950 13,868 7,290 4,276 3,780 3,001 2,352 77,264
1,928 2,131 3,277 6,489 10,233 12,962 12,024 6,321 3,708 3,277 2,602 2,039 66,993
296
327
503
995
1,569 1,987 1,844
969
569
503
399
313 10,272
454
454
454
454
454
454
454
454
454
454
454
454 5,454
84
84
87
87
87
90
90
90
93
93
93
96 1,074
10
21
57
144
261
372
417
336
198
105
62
44 2,026
-253
-233
-96
309
767 1,071
882
88
-177
-150
-211
-281 1,717
-76
-70
-29
93
230
321
265
26
-53
-45
-63
-84
515
-177
-163
-67
217
537
750
617
62
-124
-105
-147
-197 1,202
500
500
500
500
Dec-11 Jan-12
Cash
762
750
Accounts Receivable (6)
2,673 2,773
Inventory
3,450 4,509
Total Current Assets
6,885 8,032
Gross Plant, Property, and Equip (7) 10,096 10,096
Accumulated Depreciation
1,484 1,568
Net Plant, Property, and Equipment
8,612 8,527
Total Assets
15,497 16,559
Accounts Payable (8)
Note Payable (9)
Accrued Taxes (10)
Total Current Liabilities
Shareholders' Equity (11)
Total Liabilities & Equity
15%
73.7%
6.0%
10%
14.5%
30%
500
Feb-12
750
3,291
8,051
12,092
10,096
1,652
8,443
20,535
Mar-12
750
5,011
14,057
19,818
10,446
1,739
8,706
28,524
Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
750
750
750
750
750
750
10,301 17,996 24,748 25,697 17,194 9,006
19,838 21,867 16,672 9,019 6,085 5,151
30,889 40,613 42,170 35,466 24,029 14,907
10,446 10,446 10,796 10,796 10,796 11,146
1,826
1,913 2,003 2,093 2,183 2,276
8,619
8,532 8,792 8,702 8,612 8,869
39,508 49,146 50,963 44,168 32,641 23,776
822 1,223 2,421
798 1,712 4,722
-90
-166
-236
1,530 2,769 6,908
13,967 13,790 13,627
15,497 16,559 20,535
3,818
11,910
-265
15,464
13,060
28,524
4,837
21,567
-172
26,232
13,277
39,508
4,487
30,787
58
35,332
13,813
49,146
2,358
34,541
0
36,899
14,063
50,963
1,384
27,840
265
29,488
14,680
44,168
Oct-12 Nov-12 Dec-12
750
750
750
6,295 5,028 3,715
4,056 3,841 4,427
11,100 9,619 8,891
11,146 11,146 11,496
2,369 2,462 2,558
8,777 8,684 8,938
19,877 18,303 17,829
1,223
971
761
935
994
16,385 8,687 5,147 3,610 3,858
291
0
-45
-108
-192
17,899 9,658 5,863 4,437 4,660
14,742 14,118 14,013 13,866 13,169
32,641 23,776 19,877 18,303 17,829
Inventory Detail
Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12
2012
Purchases (12)
1,439 1,591 2,446 4,842 7,637 9,673
8,973 4,717 2,767 2,446 1,942 1,522 1,871 1,989 50,824
Direct Labour & Other Mftg Costs (13) 489
541
832 1,646 2,596
3,289 3,051 1,604
941
832
660
517
636 17,145
Cost of Goods Sold
1,928 2,131 3,277 6,489 10,233 12,962 12,024 6,321 3,708 3,277 2,602 2,039
Inventory (14)
3,450
4,509
8,051 14,057 19,838
21,867 16,672
Notes:
(1) Follows forecast in Exhibit 2
(2) Gross Sales * Exercise Tax Rate
(3) Annual Operating Expenses / 12
(4) Gross PPE * Depreciation Rate / 12
(5) Notes Payable (t - 1) * Interest Rate / 12
(6) AR(t - 1) + GSales(t) - 40% * GSales(t - 2) - 60% x GSales(t - 2)
(7) GPPE(t -1) + Capex(t)
(8) 50% * Purchases(t)
(9) Total Assets - AP - AccTax - ShrEquity
(10) AccTax(t - 1) + IncTax(t) or 0 if positive balance and month of quarterly payment
(11) ShrEquity(t - 1) + NetProfit(t) - Dividend(t)
(12) 55% * GSales(t + 2)
(13) 35% * Purchases(t-1)
(14) Inventory(t - 1) + Purchases(t) + Direct Labour(t) - COGS(t)
9,019
6,085
5,151
4,056
3,841
4,427
Exhibit 6
GUNA FIBRES, LTD.
Forecast of Accounts by Month
40
35
Millions of Rupees
30
25
20
15
10
5
0
Jan-12
Feb-12
Net Sales
Net Sales
Accounts Receivable
Inventory
Accounts Payable
Notes Payable
Jan-12
2
3
5
1
2
Mar-12
Apr-12
May-12
Accounts Receivable
Feb-12 Mar-12
2
4
3
5
8
14
2
4
5
12
Jun-12
Jul-12
Inventory
Apr-12 May-12
7
12
10
18
20
22
5
4
22
31
Jun-12
15
25
17
2
35
Aug-12
Sep-12
Accounts Payable
Jul-12 Aug-12
14
7
26
17
9
6
1
1
28
16
Sep-12
Oct-12
Accounts Payable
Sep-12
4
9
5
1
9
Nov-12
Dec-12
Notes Payable
Oct-12 Nov-12 Dec-12
4
3
2
6
5
4
4
4
4
1
1
1
5
4
4
CASE 9
Guna Fibres, Ltd.
Surabhi Kumar, managing director and principal owner of Guna Fibres, Ltd. (Guna),
discovered the problem when she arrived at the parking lot of the company's plant
one morning in early January 2012. Customers for whom rolls of fiber yarn were
intended had been badgering Kumar to fill their orders in a timely manner, yet
trucks that had been loaded just the night before were being unloaded because the
government tax inspector, stationed at the company's warehouse, would not clear
the trucks for departure. The excise tax had not been paid; the inspector required a
cash payment, but in seeking to draw funds that morning, Vikram Malik, the book-
keeper, discovered that the company had overdrawn its bank account—the third
time in as many weeks. The truck drivers, independent contractors,cursed loudly as
they unloaded the trucks, refusing to wait while the company and government set-
tled their accounts.
This shipment would not leave for at least another two days, and angry customers
would no doubt require an explanation. Before granting a loan with which to pay the
excise tax, the branch manager of the All-India Bank & Trust Company had requested
a meeting with Kumar for the next day to discuss Guna's financial condition and its
plans for restoring the firm's liquidity.
Kumar told Malik, “This cash problem is most vexing. I don't understand it. We're
a very profitable enterprise, yet we seem to have to depend increasingly on the bank.
Why do we need more loans just as our heavy selling season begins? We can't repeat
this blunder.”
Company Background
Guna was founded in 1972 to produce nylon fiber at its only plant in Guna, India,
about 500 km south of New Delhi. By using new technology and domestic raw mate-
rials, the firm had developed a steady franchise among dozens of small, local textile
weavers. It supplied synthetic fiber yarns used to weave colorful cloths for making
This case was written by Thien T. Pham (MBA '90), Robert F. Bruner, and Michael J. Schill as a basis
for class discussion. The names and institutions in this case are fictitious. The financial support of
the Batten Institute is gratefully acknowledged. Copyright © 2013 by the University of Virginia Darden
School Foundation, Charlottesville, VA. All rights reserved. To order copies, send an e-mail to
sales@dardenbusinesspublishing.com. No part of this publication may be reproduced, stored in a
retrieval system, used in a spreadsheet, or transmitted in any form or by any means-electronic,
mechanical, photocopying, recording, or otherwise-without the permission of the Darden School
Foundation.
133
134
Part Two Financial Analysis and Forecasting
saris, the traditional women's dress of India. On average, each sari required eigen
female population at around 600 million, the demand for saris accounted for more
yards of cloth. An Indian woman typically would buy three saris a year. With India's
than 14 billion yards of fabric. This demand was currently being supplied entirely
from domestic textile mills that, in turn, filled their yarn requirements from suppliers
Case 9 Guna Fibres, Ltd.
135
Company Performance
Guna had experienced consistent growth and profitability (see Exhibit 9.1 for firm's
recent financial statements). In 2011, sales had grown at an impressive rate of 18%.
Recent profits were INR25 million, down from INR36 million in 2010.' Kumar ex-
pected Guna's growth to continue with gross sales reaching more than INR900 million
in 2012 (Exhibit 9.2).?
Synthetic Textile Market
able seasonal fluctuations. Unit demand increased with both population and na-
The demand for synthetic textiles was stable, with year-to-year growth and predict-
tional income. In addition, India's population celebrated hundreds of festivals each
most important festival, the Diwali celebration in midautumn, caused a seasonal
year, in deference to a host of deities, at which saris were traditionally worn. The
peak in the demand for new saris, which in turn caused a seasonal peak in demand
for nylon textiles in late summer and early fall. Thus the seasonal demand for nylon
yarn would peak in midsummer. Unit growth in the industry was expected to be
Consumers purchased saris and textiles from cloth merchants located in villages
15% per year.
throughout the country. A cloth merchant usually was an important local figure who
was well known to area residents and who generally granted credit to support consumer
purchases. Merchants maintained relatively low levels of inventory and built stocks of
goods only shortly before and during the peak selling season.
Competition was keen among those merchants' suppliers (the many small textile-
weaving mills) and was affected by price, service, and the credit they could grant to the
merchants. The mills essentially produced to order, building their inventories of woven
cloth shortly in advance of the peak selling season and keeping only maintenance stocks
at other times of the year.
The yarn manufacturers competed for the business of the mills through responsive
service and credit. The suppliers to the yarn manufacturers provided little or no trade
credit. Being near the origin of the textile chain in India, the yarn manufacturers essen-
tially banked the downstream activities of the industry.
Reassessment
After the episode in the parking lot, Kumar and her bookkeeper went to her office to
analyze the situation. She pushed aside two items on her desk to which she had intended
to devote her morning: a message from the transportation manager regarding a possible
change in the inventory policy (Exhibit 9.3) and a proposal from the operations man-
ager for a scheme of level annual production (Exhibit 9.4).
To prepare a forecast on a business-as-usual basis, Kumar and Malik agreed on vari-
ous parameters. Cost of goods sold would run at 73.7% of gross sales—a figure that was
up from recent years because of increasing price competition. Annual operating ex-
penses would be about 6% of gross annual sales. Operating expenses were up from recent
years to include the addition of a quality-control department, two new sales agents, and four
young nephews in whom Kumar hoped to build allegiance to the family business. Kumar
had long felt pressure to hire family members to company management. The four new fellows
would join 10 other family members on her team. Although the company's income tax rate
of 30% accrued monthly, positive balances were paid quarterly in March, June, September,
and December. The excise tax (at 15% of sales) was different from the income tax and was
collected at the factory gate as trucks left to make deliveries to customers and the regional
warehouses. Kumar expected to pay dividends of INR5.0 million per quarter to the 11
members of her extended family who owned the entirety of the firm's equity. For years,
Guna had paid substantial dividends. The Kumar family believed that excess funds left in
the firm were at greater risk than if the funds were returned to shareholders.
Accounts receivable collections in any given month had been running steadily at
the rate of 48 days, comprised of 40% of the previous month's gross sales plus 60% of
the gross sales from the month before that. The cost of the raw materials for Guna's yarn
production ran about 55% of the gross sale price. To ensure sufficient raw material on
hand, it was Guna's practice each month to purchase the amount of raw materials ex-
pected to be sold in two months. The suppliers Guna used had little ability to provide
credit such that accounts payable were generally paid within two weeks. Monthly direct
labor and other direct costs associated with yarn manufacturing were equivalent to
about 34% of purchases in the previous month." Accounts payable ran at about half of
the month's purchases. As a matter of policy, Kumar wanted to see a cash balance of at
Dividendo
AIR
Production and Distribution System
Thin profit margins had prompted Kumar to adopt policies against overproduction and
overstocking, which required Guna to carry inventories through the slack selling sea-
son. She had adopted a plan of seasonal production, which meant that the yarn plant
would operate at peak capacity for two months of the year and at modest levels the rest
of the year. That policy imposed an annual ritual of hirings and layoffs.
To help ensure prompt service, Guna maintained two distribution warehouses, but
getting the finished yarn quickly from the factory in Guna to the customers was a chal-
lenge. The roads were narrow and mostly in poor repair. A truck was often delayed
negotiating the trip between Kolkata and Guna, a distance of about 730 km. Journeys
were slow and dangerous, and accidents were frequent.
Challenge
INR - Indian rupees.
-At the time, the rupee exchange rate for U.S. dollars was roughly at the rate of INR50 per
The 73.7% COGS rate assumption was determined based on these purchases and direct cost figures:
73.7% = 55% + 55% X 34%.
dollar.
25
Part Two Financial Analysis and Forecasting
26
cash
Case 9 Guna Fibres, Ltd.
EXHIBIT 9.1 1 Guna's Annual Income Statements
(in millions of Indian rupees)
137
least INR7.5 million. To sustain company expansion, capital expenditures were antici-
pated to run at INR3.5 million per quarter.
Guna had a line of credit at the All-India Bank & Trust Company, where it also
maintained its cash balances. All-India's short-term interest rate was currently 14.5%,
but Kumar was worried that inflation and interest rates might rise in the coming year. By
terms of the bank, the seasonal line of credit had to be reduced to a zero balance for at
least 30 days each year. The usual cleanup month had been October, but last year Guna
had failed to make a full repayment at that time. Only after strong assurances by Kumar
that she would clean up the loan in November or December had the lending officer re-
luctantly agreed to waive the cleanup requirement in October. Unfortunately, the credit
needs of Guna did not abate as rapidly as expected in November and December, and
although his protests increased each month, the lending officer had agreed to meet
credit until Kumar presented a reasonable financial plan for the company that demon-
strated its ability to clean up the loan by the end of 2012.
Guna's cash requirements with loans. Now he was refusing to extend any more seasonal
2010 2011
Gross Sales
644.8 758.7
Excise Tax
96.7 113.8
Net Sales
548.1 644.9
Cost of Goods
445.0 538.6
Gross Profits
103.1 106.3
Operating Expenses
35.0 48.3
Depreciation
7.7 9.1
Interest Expense
9.1 12.4
Profit Before Tax
51.4 36.5
Income Tax
15.5 10.9
Net Profit
35.9 25.6
Cash
9.0
7.6
Accounts Receivable
23.9 26.7
Inventory
29.7 34.5
Total Current Assets
62.6 68.8
Gross Plant, Property, and Equipment (PPE) 88.7 100.9
Accumulated Depreciation
11.7 14.8
Net PPE
77.0 86.1
Total Assets
139.6 154.9
Financial Forecast
With some experience in financial modeling, Malik used the agreed-upon assumptions
to build out a monthly forecast of Guna’s financial statements (Exhibit 9.5). To sum-
marize the seasonal pattern of the model, Malik handed Kumar a graph showing the
projected monthly sales and key balance sheet accounts (Exhibit 9.6). After studying
the forecasts for a few moments, Kumar expostulated:
The loan officer will never accept this forecast as a basis for more credit. We need a new
plan, and fast. Maintaining this loan is critical for us to scale up for the most important
part of our business season. Please go over these assumptions in detail and look for any
opportunities to improve our debt position.
6.0
Accounts Payable
Notes to Bank
Accrued Taxes
Total Current Liabilities
Owners' Equity
Total Liabilities and Equity
0.0
8.2
8.0
-0.6
Then looking toward the two proposals she had pushed aside earlier, she muttered, “Perhaps
these proposals will help.”
5.4
134.2.
139.6
-0.9
15.3
139.6
154.9
Source: All exhibits created by case writer.
25
et Food
23 de
PS I DUE
Renew Coc Probus
Bota tiverad
Financial Analysis and Forecasting
DB3
washing
26 saturday
EXHIBIT 9.2 I Guna's Monthly Sales, 2011 Actual
and 2012 Forecast (in millions of Indian rupees)
Case 9 Guna Fibres, Ltd.
139
EXHIBIT 9.4 | Message from Operations Manager
2011
(Actual)
2012
(Forecast)
January 7, 2012
20.1
26.2
23.1
28.9
To:
From:
S. Kumar
L. Gupta
34.2
January
February
March
April
May
June
44.5
88.0
70.4
120.7
138.9
175.9
152.9
141.9
163.2
85.8
71.4
You asked me to estimate the production efficiencies arising from a scheme of level
annual production where our production rate is level over the year rather than highly seasonal
as it currently is. To provide for the estimated production needs in 2012 and 2013 with level
production, I recommend that purchases shift to INR50 million per month.
There are significant operating advantages to be gained withthis operating scenario:
Seasonal hirings and layoffs would no longer be necessary, permitting us to cultivate a
stronger work force and, perhaps, to suppress labor unrest. You will recall that the unions
have indicated that reducing seasonal layoffs will be one of their major negotiating
objectives this year.
Level production entails lower manufacturing risk. With the load spread throughout the
year, we would suffer less from equipment breakdowns and could better match the
routine maintenance with the demand on the plant and equipment.
.
40.2
July
August
September
October
November
December
Year
50.3
34.2
44.5
27.2
35.3
22.1
27.7
758.7
909.0
With level production my team believes that direct labor and other direct manufacturing costs
could be reduced from a forecasted 34% of purchases down to 29% of purchases.
EXHIBIT 9.3 | Message from Transportation Manager
January 2, 2012
To:
S. Kumar
From:
R. Sikh
As you asked me to, I have been tracking our supply shipments over the past year.
I have observed a substantial improvement in the reliability of the shipments. As a result,
I would propose that we reduce our raw-material inventory requirement from 60 days to
30 days. This would reduce the amount of inventory we are carrying by one month, and
should free up a lot of space in the warehouse. I am not sure if that will affect any other
department since we will be buying the same amount of material, but it would make inventory
tracking a lot easier for me. Please let me know so we can implement this in January such
that I don't purchase any additional raw material this month.
Purchase answer to see full
attachment