Model Facts

Page 1

Please note:

Copyright © 2001 by the Trustees of the University of Virginia Darden School Foundation.

This spreadsheet supports student analysis of the case, "Kota Fibres, Ltd." (UVA -F-1359 v. 1.1)

1) All worksheets have been protected to prevent any inadvertent errors by students. To unprotect the worksheet press Control "u" and to protect worksheet press Control "p" (use lower case).

2) This is a working model. Assumptions / Inputs presented can be changed to vary the results.

3) As long as the default spreadsheet calculation is set as "automatic", the impact of changing assumptions will be computed in real time. Alternatively, the F9 function key may need to be invoked to recalculate results. To set numerical calculation settings to automatic look under tools, options, calculations menu.

Model Facts

Page 2

Exhibit 1KOTA FIBRES, LTD.Summary of monthly sales: Actual for 2000 and Forecast for 2001

2000 2001(historical) (forecast)

January 2,012,400 2,616,120 February 2,314,260 2,892,825 March 3,421,080 4,447,404 April 7,043,400 8,804,250 May 12,074,400 13,885,560 June 15,294,240 17,588,376 July 14,187,420 16,315,533 August 7,144,020 8,572,824 September 4,024,800 5,031,000 October 3,421,080 4,447,404 November 2,716,740 3,531,762 December 2,213,640 2,767,050

Year 75,867,480 90,900,108

Supporting Calculations for Exhibit 1

HISTORICAL CYCLE 100 115 170FORECAST CYCLE 130 125 130

TOTAL2000 (historical) 75,867,480 2,012,400 2,314,260 3,421,080

2001 (forecast) 90,900,108 2,616,120 2,892,825 4,447,404

GROSS UP RATIO 1

350 600 760 705 355 200 170 135 110125 115 115 115 120 125 130 130 125

7,043,400 12,074,400 15,294,240 14,187,420 7,144,020 4,024,800 3,421,080 2,716,740 2,213,640

8,804,250 13,885,560 17,588,376 16,315,533 8,572,824 5,031,000 4,447,404 3,531,762 2,767,050

Exhibit 2KOTA FIBRES, LTD.Historical Annual Income Statements ( in Rupees)

1999 2000 2001(Actual) (Actual) (Forecast)

Gross Sales 64,487,358 75,867,480 90,900,108 Excise Tax 9,673,104 11,380,122 13,635,016 Net Sales 54,814,254 64,487,358 77,265,092 Cost of Goods 44,496,277 53,865,911 66,993,380 Gross Profits 10,317,978 10,621,447 10,271,712 Operating Expenses 3,497,305 4,828,721 5,454,006 Depreciation 769,103 908,608 1,073,731 Interest Expense 910,048 1,240,066 1,835,620 Profit Before Tax 5,141,521 3,644,052 1,908,355 Income Tax 1,542,456 1,093,216 572,506 Net Profit 3,599,065 2,550,837 1,335,848

Exhibit 3KOTA FIBRES, LTD.Historical Balance Sheets (in Rupees)

2000 2001

(Actual) (Forecast)

Cash 762,323 750,000 Accounts Receivable 2,672,729 3,715,152 Inventories 1,249,185 2,225,373 Total Current Assets 4,684,237 6,690,525 Gross PP&E 10,095,646 11,495,646 Accumulated Depreciation 1,484,278 2,558,009 Net PP&E 8,611,368 8,937,637 Total Assets 13,295,604 15,628,161

Accounts Payable 759,535 1,157,298 Notes to Bank (Deposits at Bank) 684,102 3,463,701 Accrued Taxes 0 (180,654) Total Current Liabilities 1,443,637 4,440,345 Owners' Equity 11,851,967 11,187,816 Total Liabilities and Equity 13,295,604 15,628,161

EXHIBIT 8KOTA FIBRES, LTD.Monthly Forecast of Income Statements (in Rupees)

Pro Forma

January February March April May June July August September October November December The Year

Gross Sales 2,616,120 2,892,825 4,447,404 8,804,250 13,885,560 17,588,376 16,315,533 8,572,824 5,031,000 4,447,404 3,531,762 2,767,050 90,900,108 Excise Taxes 392,418 433,924 667,111 1,320,638 2,082,834 2,638,256 2,447,330 1,285,924 754,650 667,111 529,764 415,058 13,635,016 Net Sales 2,223,702 2,458,901 3,780,293 7,483,613 11,802,726 14,950,120 13,868,203 7,286,900 4,276,350 3,780,293 3,001,998 2,351,993 77,265,092 Cost of Goods Sold 1,928,080 2,132,012 3,277,737 6,488,732 10,233,658 12,962,633 12,024,548 6,318,171 3,707,847 3,277,737 2,602,909 2,039,316 66,993,380 Gross Profit 295,622 326,889 502,557 994,880 1,569,068 1,987,486 1,843,655 968,729 568,503 502,557 399,089 312,677 10,271,712 Operating Expenses 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 5,454,006 Depreciation 84,130 84,130 87,047 87,047 87,047 89,964 89,964 89,964 92,880 92,880 92,880 95,797 1,073,731 Interest Expense (Income) (1) 11,058 24,825 70,867 158,210 268,352 362,187 363,212 259,568 145,898 80,686 50,025 40,731 1,835,620 Profit Before Taxes (254,068) (236,566) (109,858) 295,123 759,168 1,080,835 935,979 164,697 (124,776) (125,510) (198,317) (278,352) 1,908,355 Income Taxes (76,220) (70,970) (32,957) 88,537 227,751 324,251 280,794 49,409 (37,433) (37,653) (59,495) (83,506) 572,506 Net Profit (177,847) (165,596) (76,900) 206,586 531,418 756,585 655,185 115,288 (87,343) (87,857) (138,822) (194,847) 1,335,848

(1) Interest expense = Notes Payable * 14.5%/12 months.

Forecasted Monthly Balance Sheets for 2001 (in Rupees)

January February March April May June July August September October November December

AssetsCash (1) 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 Accounts Receivable (2) 2,773,349 3,291,542 5,012,144 10,301,737 17,997,155 24,748,757 25,697,603 17,191,189 9,003,739 6,295,049 5,029,249 3,715,152 Inventories (3) 2,308,135 5,850,125 11,855,841 17,637,315 19,666,227 14,469,652 6,815,272 3,883,970 2,950,257 1,854,837 1,639,892 2,225,373 Total Current Assets 5,831,484 9,891,667 17,617,985 28,689,052 38,413,382 39,968,409 33,262,875 21,825,159 12,703,996 8,899,886 7,419,142 6,690,525 Net Prop. Plant & Equip. (4) 8,527,237 8,443,107 8,706,060 8,619,013 8,531,966 8,792,002 8,702,038 8,612,075 8,869,194 8,776,314 8,683,434 8,937,637 Total Assets 14,358,721 18,334,774 26,324,045 37,308,065 46,945,348 48,760,411 41,964,914 30,437,233 21,573,190 17,676,200 16,102,575 15,628,161

Liabilities and Owners' EquityAccounts Payable (5) 1,614,553 4,010,818 6,805,539 8,842,088 8,142,024 3,883,534 1,935,531 1,614,553 1,110,950 690,358 1,039,007 1,157,298 Note Payable- Bank (6) 1,146,268 2,962,622 8,767,030 17,419,379 26,997,556 32,950,665 27,167,192 15,795,793 8,352,899 5,002,010 3,278,054 3,463,701 Accrued Taxes (7) (76,220) (147,190) (180,148) (91,611) 136,140 0 280,794 330,203 0 (37,653) (97,148) (180,654) Total Current Liabilities 2,684,601 6,826,250 15,392,421 26,169,856 35,275,720 36,834,199 29,383,517 17,740,548 9,463,849 5,654,715 4,219,913 4,440,345 Shareholders' Equity (8) 11,674,120 11,508,524 10,931,623 11,138,209 11,669,627 11,926,212 12,581,397 12,696,685 12,109,341 12,021,484 11,882,662 11,187,816 Total Liabilities & Equity 14,358,721 18,334,774 26,324,045 37,308,065 46,945,348 48,760,411 41,964,914 30,437,233 21,573,190 17,676,200 16,102,575 15,628,161

(1) See Exhibit 9.(2) See panel 1, Exhibit 10.(3) See panel 2, Exhibit 10. (4) See panel 6, Exhibit 10.(5) See panel 3, Exhibit 10.(6) Plug figure.(7) See panel 5, Exhibit 10.(8) See panel 4, Exhibit 10.

EXHIBIT 9KOTA FIBRES, LTD.Schedule of Cash Receipts and Disbursements (in Rupees)

January February March April May June July August September October November December January February

Assume: Sales 2,616,120 2,892,825 4,447,404 8,804,250 13,885,560 17,588,376 16,315,533 8,572,824 5,031,000 4,447,404 3,531,762 2,767,050 3,400,956 3,616,031 Purchases (1) 2,446,072 4,842,338 7,637,058 9,673,607 8,973,543 4,715,053 2,767,050 2,446,072 1,942,469 1,521,878 1,870,526 1,988,817 Debt Outstanding 1,146,268 2,962,622 8,767,030 17,419,379 26,997,556 32,950,665 27,167,192 15,795,793 8,352,899 5,002,010 3,278,054 3,463,701

Receipts: Accts Rcvble Collected 2,515,500 2,374,632 2,726,802 3,514,657 6,190,142 10,836,774 15,366,686 17,079,239 13,218,449 7,156,094 4,797,562 4,081,147 New Borrowings (Repayments) 462,166 1,816,354 5,804,408 8,652,349 9,578,178 5,953,108 (5,783,473) (11,371,400) (7,442,894) (3,350,889) (1,723,956) 185,647

Disburs.: Accounts Paid (2) 1,591,054 2,446,072 4,842,338 7,637,058 9,673,607 8,973,543 4,715,053 2,767,050 2,446,072 1,942,469 1,521,878 1,870,526 Capital Expenditures 0 0 350,000 0 0 350,000 0 0 350,000 0 0 350,000 Interest Payments 11,058 24,825 70,867 158,210 268,352 362,187 363,212 259,568 145,898 80,686 50,025 40,731 Excise Tax Paid 392,418 433,924 667,111 1,320,638 2,082,834 2,638,256 2,447,330 1,285,924 754,650 667,111 529,764 415,058 Operating Expenses 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 454,501 Accrued Income Tax Paid 0 0 0 0 0 460,390 0 0 292,770 0 0 0 Wages 540,958 831,665 1,646,395 2,596,600 3,289,026 3,051,005 1,603,118 940,797 831,665 660,439 517,438 635,979 Dividends 0 0 500,000 0 0 500,000 0 0 500,000 0 0 500,000 Subtotal: Disbursements 2,989,989 4,190,986 8,531,210 12,167,005 15,768,320 16,789,882 9,583,214 5,707,839 5,775,555 3,805,206 3,073,606 4,266,794

Receipts - Disbursements (12,323) 0 0 0 0 0 0 0 0 0 0 0

BOP Cash Balance 762,323 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 EOP Cash Balance 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 (1) Equal to 55 percent of sales in period (T+2).(2) Equal to purchases in period (T-1).

Exhibit 10KOTA FIBRES, LTD.Forecasted T-Accounts Supporting Financial Statements (in Rupees)

January February March April May June July August September October November December

1. Schedule of Accounts ReceivableBeginning of Period 2,672,729 2,773,349 3,291,542 5,012,144 10,301,737 17,997,155 24,748,757 25,697,603 17,191,189 9,003,739 6,295,049 5,029,249 Plus Sales 2,616,120 2,892,825 4,447,404 8,804,250 13,885,560 17,588,376 16,315,533 8,572,824 5,031,000 4,447,404 3,531,762 2,767,050 Less Collections, Last Month (1) 885,456 1,046,448 1,157,130 1,778,962 3,521,700 5,554,224 7,035,350 6,526,213 3,429,130 2,012,400 1,778,962 1,412,705 Less Collections, Month before Last (2) 1,630,044 1,328,184 1,569,672 1,735,695 2,668,442 5,282,550 8,331,336 10,553,026 9,789,320 5,143,694 3,018,600 2,668,442 End of Period 2,773,349 3,291,542 5,012,144 10,301,737 17,997,155 24,748,757 25,697,603 17,191,189 9,003,739 6,295,049 5,029,249 3,715,152 (1) 40% of sales in period (T-1).(2) 60% of sales in period (T-2).

2. Schedule of Inventories Beginning of Period 1,249,185 2,308,135 5,850,125 11,855,841 17,637,315 19,666,227 14,469,652 6,815,272 3,883,970 2,950,257 1,854,837 1,639,892 Plus Purchases (1) 2,446,072 4,842,338 7,637,058 9,673,607 8,973,543 4,715,053 2,767,050 2,446,072 1,942,469 1,521,878 1,870,526 1,988,817 Plus Labor 540,958 831,665 1,646,395 2,596,600 3,289,026 3,051,005 1,603,118 940,797 831,665 660,439 517,438 635,979 Less Shipments (COGS) 1,928,080 2,132,012 3,277,737 6,488,732 10,233,658 12,962,633 12,024,548 6,318,171 3,707,847 3,277,737 2,602,909 2,039,316 End of Period 2,308,135 5,850,125 11,855,841 17,637,315 19,666,227 14,469,652 6,815,272 3,883,970 2,950,257 1,854,837 1,639,892 2,225,373 (1) Equal to 55 percent of sales in period (T+2).

3. Schedule of Accounts PayableBeginning of Period 759,535 1,614,553 4,010,818 6,805,539 8,842,088 8,142,024 3,883,534 1,935,531 1,614,553 1,110,950 690,358 1,039,007 + Purchases (1) 2,446,072 4,842,338 7,637,058 9,673,607 8,973,543 4,715,053 2,767,050 2,446,072 1,942,469 1,521,878 1,870,526 1,988,817 - Payments (2) 1,591,054 2,446,072 4,842,338 7,637,058 9,673,607 8,973,543 4,715,053 2,767,050 2,446,072 1,942,469 1,521,878 1,870,526 End of Period 1,614,553 4,010,818 6,805,539 8,842,088 8,142,024 3,883,534 1,935,531 1,614,553 1,110,950 690,358 1,039,007 1,157,298 (1) Equal to 55 percent of sales in period (T+2).(2) Equal to purchases in period (T-1).

4. Schedule of Shareholder's EquityBeginning of Period 11,851,967 11,674,120 11,508,524 10,931,623 11,138,209 11,669,627 11,926,212 12,581,397 12,696,685 12,109,341 12,021,484 11,882,662 Plus Net Profit (177,847) (165,596) (76,900) 206,586 531,418 756,585 655,185 115,288 (87,343) (87,857) (138,822) (194,847) Less Dividends 0 0 500,000 0 0 500,000 0 0 500,000 0 0 500,000 End of Period 11,674,120 11,508,524 10,931,623 11,138,209 11,669,627 11,926,212 12,581,397 12,696,685 12,109,341 12,021,484 11,882,662 11,187,816

5. Schedule of Accrued TaxesBeginning of Period 0 (76,220) (147,190) (180,148) (91,611) 136,140 0 280,794 330,203 0 (37,653) (97,148) Plus Monthly Tax Expense (@ 30%) (76,220) (70,970) (32,957) 88,537 227,751 324,251 280,794 49,409 (37,433) (37,653) (59,495) (83,506) Less Quarterly Tax Payments 0 0 0 0 0 460,390 0 0 292,770 0 0 0 End of Period (76,220) (147,190) (180,148) (91,611) 136,140 0 280,794 330,203 0 (37,653) (97,148) (180,654)

6. Schedule of Property, Plant and EquipmentBeginning Gross PP&E 10,095,646 10,095,646 10,095,646 10,445,646 10,445,646 10,445,646 10,795,646 10,795,646 10,795,646 11,145,646 11,145,646 11,145,646 Plus Capital Expenditures 0 0 350,000 0 0 350,000 0 0 350,000 0 0 350,000 Ending Gross PP&E 10,095,646 10,095,646 10,445,646 10,445,646 10,445,646 10,795,646 10,795,646 10,795,646 11,145,646 11,145,646 11,145,646 11,495,646 Monthly Depreciation Expense 84,130 84,130 87,047 87,047 87,047 89,964 89,964 89,964 92,880 92,880 92,880 95,797 Less Cumulative Depr'n. 1,568,408 1,652,539 1,739,586 1,826,633 1,913,680 2,003,643 2,093,607 2,183,571 2,276,451 2,369,332 2,462,212 2,558,009 Ending Net PP&E 8,527,237 8,443,107 8,706,060 8,619,013 8,531,966 8,792,002 8,702,038 8,612,075 8,869,194 8,776,314 8,683,434 8,937,637

Exhibit 11KOTA FIBRES, LTD.

Assumptions

Debt Balance SummaryJan '01 1,146,268 June '01 32,950,665 Dec '01 3,463,701

Ratio of: Income Tax/Profit Before Tax 30% Excise Tax/Sales 15% This Month Collections of Last Month's Sales 40% This Month Collections of Month-before-Last Sales 60% Purchases/ Sales two months later 55% Wages/Purchases 34%Annual Operating Expenses/Annual Sales 6.00%Capital Expenditures (every third month) 350,000 Interest Rate on Borrowings (and deposits) 14.5%Minimum Cash Balance 750,000 Depreciation/Gross PP&E (per year) 10% (per month) 0.83%Dividends Paid (every third month) 500,000

Exhibit 12KOTA FIBRES, LTD.

Trend of Certain Financal Accounts by Month

January February March April May June July August September October November December

Sales 2,616,120 2,892,825 4,447,404 8,804,250 13,885,560 17,588,376 16,315,533 8,572,824 5,031,000 4,447,404 3,531,762 2,767,050

A/R 2,773,349 3,291,542 5,012,144 10,301,737 17,997,155 24,748,757 25,697,603 17,191,189 9,003,739 6,295,049 5,029,249 3,715,152

Inv 2,308,135 5,850,125 11,855,841 17,637,315 19,666,227 14,469,652 6,815,272 3,883,970 2,950,257 1,854,837 1,639,892 2,225,373

A/P 1,614,553 4,010,818 6,805,539 8,842,088 8,142,024 3,883,534 1,935,531 1,614,553 1,110,950 690,358 1,039,007 1,157,298

n/p 1,146,268 2,962,622 8,767,030 17,419,379 26,997,556 32,950,665 27,167,192 15,795,793 8,352,899 5,002,010 3,278,054 3,463,701

January February March April May June July August Sep-tember

October No-vember

De-cember

0

5,000,000

10,000,000

15,000,000

20,000,000

25,000,000

30,000,000

35,000,000

Sales

A/R

Inv

A/P

n/p