hi, I need help doing the quarterly proforma for Comfort Zone, I have attached the case and the excel template for the proforma. Please, make sure to use the information from the case. thank you.

Comfort Zone Apr-89 Current Assets Cash Accounts & notes rec. Allow for bad debts Inventory Prepaid and other Total current assets Property, plant & equip. Accumulated depr. Net Goodwill Other Assets Total Current Liabilities: Notes payable-bank Trade accounts payable Current portion-LTD Accrued taxes Other Total current liab. Long-Term Debt Stockholders' Equity: Common Stock Paid-in capital Retained earnings Stockholders' equity Total Jul-89 Oct-89 Jan-90 Apr-90 Jul-90 Oct-90 Jan-91 Apr-91 Jul-91 1,983 10,356 -474 18,165 24 30,054 2,502 -867 1,635 1,185 87 32,961 1,488 12,855 -690 19,104 51 32,808 2,817 -906 1,911 1,182 66 35,967 1,353 12,798 -903 18,936 87 32,271 2,901 -948 1,953 1,149 54 35,427 1,677 12,285 -531 20,133 60 33,624 2,859 -831 2,028 1,131 72 36,855 2,031 14,256 -729 25,926 24 41,508 3,018 -873 2,145 1,116 123 44,892 1,794 17,790 -939 29,319 51 48,015 3,153 -918 2,235 1,098 72 51,420 1,197 16,908 -1,239 26,286 90 43,242 3,231 -972 2,259 1,080 75 46,656 960 16,887 -633 25,008 306 42,528 3,309 -1,029 2,280 1,062 18 45,888 1,692 16,815 -699 31,965 63 49,836 3,393 -1,086 2,307 1,044 93 53,280 1,248 20,136 -828 30,396 102 51,054 3,462 -1,128 2,334 1,026 66 54,480 1,002 8,601 639 897 1,113 12,252 5,628 2,334 9,666 669 582 1,023 14,274 5,499 1,641 8,649 882 912 1,128 13,212 5,091 1,563 8,550 891 1,176 1,707 13,887 5,157 2,400 14,727 954 1,383 1,416 20,880 5,499 3,000 17,178 618 852 1,110 22,758 8,778 2,700 11,304 618 771 1,680 17,073 8,772 2,400 10,245 612 690 2,007 15,954 8,454 2,400 16,971 612 939 1,866 22,788 8,445 4,200 16,137 606 495 1,359 22,797 8,175 333 3,165 11,583 15,081 32,961 333 3,171 12,690 16,194 35,967 333 3,171 13,620 17,124 35,427 333 3,171 14,307 17,811 36,855 333 3,171 15,009 18,513 44,892 333 3,171 16,380 19,884 51,420 333 3,171 17,307 20,811 46,656 333 3,171 17,976 21,480 45,888 333 3,171 18,543 22,047 53,280 333 3,171 20,004 23,508 54,480 Exhibit 3 Comfort Zone Quarterly Statements of Consolidated Income (Thousands of dollars) 3 months 6 months 9 months 12 months 3 months 6 months 9 months 12 months 3 months 6 months Apr-89 Jul-89 Oct-89 Jan-90 Apr-90 Jul-90 Oct-90 Jan-91 Apr-91 Jul-91 Net Sales Cost of Merchandise Sold Gross Profit on Sales Operating Expenses (note 1) Profit from Operations Other Income Interest Expense Income before Income Taxes Provision for Income Taxes Net Income 17,262 13,134 4,128 2,841 1,287 33 -108 1,212 603 609 41,061 31,266 9,795 6,210 3,585 72 -237 3,420 1,704 1,716 64,656 49,365 15,291 9,804 5,487 108 -324 5,271 2,625 2,646 85,362 64,632 20,730 13,644 7,086 195 -474 6,807 3,474 3,333 22,947 17,610 5,337 3,813 1,524 36 -132 1,428 726 702 54,186 41,715 12,471 8,169 4,302 219 -291 4,230 2,157 2,073 82,875 63,903 18,972 12,774 6,198 408 -486 6,120 3,120 3,000 108,255 83,211 25,044 17,358 7,686 600 -900 7,386 3,717 3,669 23,943 18,534 5,409 4,281 1,128 267 -243 1,152 585 567 57,147 43,752 13,395 9,177 4,218 450 -528 4,140 2,112 2,028 3 months 6 months 9 months 12 months 3 months 6 months 9 months 12 months 3 months 6 months Apr-89 Jul-89 Oct-89 Jan-90 Apr-90 Jul-90 Oct-90 Jan-91 Apr-91 Jul-91 Net Sales Cost of Merchandise Sold Gross Profit on Sales Operating Expenses (note 1) Profit from Operations Other Income Interest Expense Income before Income Taxes Provision for Income Taxes Net Income Incremental Income Statements 3 months 3 months 3 months 3 months 3 months 3 months 3 months 3 months 3 months 3 months Apr-89 Jul-89 Oct-89 Jan-90 Apr-90 Jul-90 Oct-90 Jan-91 Apr-91 Jul-91 Net Sales Cost of Merchandise Sold Gross Profit on Sales Operating Expenses (note 1) Profit from Operations Other Income Interest Expense Income before Income Taxes Provision for Income Taxes Net Income Oct-91 Jan-92 Apr-92 Jul-92 Oct-92 Jan-93 Oct-91 Jan-92 Apr-92 Jul-92 Oct-92 Jan-93 FY 92 FY 93 Oct-91 Jan-92 Apr-92 Jul-92 Oct-92 Jan-93 FY 92 FY 93 Oct-91 Jan-92 Apr-92 Jul-92 Oct-92 Jan-93 Assumptions Sales for FY 1/91 Annual sales growth-1992 Annual sales growth-1993 Seasonal index CGS/S Operating expense/S Other income/S Interest rate-LTD Interest rate-Notes payable Tax rate Min. cash %S ACP Inv. days Prepaids/S NFA increase Goodwill amortization Other assets/S Acc. pay. days LTD amortization Accrued taxes/Tax expense Other CL/S New stock Dividends Apr-89 Net Sales Cost of Merchandise Sold Gross Profit on Sales Operating Expenses (note 1) Profit from Operations Other Income Interest Expense Income before Income Taxes Provision for Income Taxes Net Income Jul-89 Oct-89 Jan-90 Apr-90 Jul-90 Oct-90 Jan-91 Apr-91 Jul-91 17,262 13,134 4,128 2,841 1,287 33 -108 1,212 603 609 23,799 18,132 5,667 3,369 2,298 39 -129 2,208 1,101 1,107 23,595 18,099 5,496 3,594 1,902 36 -87 1,851 921 930 20,706 15,267 5,439 3,840 1,599 87 -150 1,536 849 687 22,947 17,610 5,337 3,813 1,524 36 -132 1,428 726 702 31,239 24,105 7,134 4,356 2,778 183 -159 2,802 1,431 1,371 28,689 22,188 6,501 4,605 1,896 189 -195 1,890 963 927 25,380 19,308 6,072 4,584 1,488 192 -414 1,266 597 669 23,943 18,534 5,409 4,281 1,128 267 -243 1,152 585 567 33,204 25,218 7,986 4,896 3,090 183 -285 2,988 1,527 1,461 Common Size Income Statement Net Sales Cost of Merchandise Sold Gross Profit on Sales Operating Expenses (note 1) Profit from Operations Other Income Interest Expense Income before Income Taxes Provision for Income Taxes Net Income Current Assets Cash Accounts & notes rec. Inventory Prepaid and other Property, plant & equip. Accumulated depr. Net Goodwill Other Assets Total Current Liabilities: Notes payable-bank Trade accounts payable Current portion-LTD Accrued taxes Other Long-Term Debt Stockholders' Equity: Common Stock Paid-in capital Retained earnings Stockholders' equity Total 1,983 9,882 18,165 24 2,502 -867 1,635 1,185 87 32,961 1,488 12,165 19,104 51 2,817 -906 1,911 1,182 66 35,967 1,353 11,895 18,936 87 2,901 -948 1,953 1,149 54 35,427 1,677 11,754 20,133 60 2,859 -831 2,028 1,131 72 36,855 2,031 13,527 25,926 24 3,018 -873 2,145 1,116 123 44,892 1,794 16,851 29,319 51 3,153 -918 2,235 1,098 72 51,420 1,197 15,669 26,286 90 3,231 -972 2,259 1,080 75 46,656 960 16,254 25,008 306 3,309 -1,029 2,280 1,062 18 45,888 1,692 16,116 31,965 63 3,393 -1,086 2,307 1,044 93 53,280 1,248 19,308 30,396 102 3,462 (1,128) 2,334 1,026 66 54,480 1,002 8,601 639 897 1,113 5,628 2,334 9,666 669 582 1,023 5,499 1,641 8,649 882 912 1,128 5,091 1,563 8,550 891 1,176 1,707 5,157 2,400 14,727 954 1,383 1,416 5,499 3,000 17,178 618 852 1,110 8,778 2,700 11,304 618 771 1,680 8,772 2,400 10,245 612 690 2,007 8,454 2,400 16,971 612 939 1,866 8,445 4,200 16,137 606 495 1,359 8,175 333 3165 11583 15081 32,961 333 3171 12690 16194 35,967 333 3171 13620 17124 35,427 333 3171 14307 17811 36,855 333 3171 15009 18513 44,892 333 3171 16380 19884 51,420 333 3171 17307 20811 46,656 333 3171 17976 21480 45,888 333 3171 18543 22047 53,280 333 3,171 20,004 23,508 54,480 Comfort Zone Early in September 1991, Gerry Conley was starting his second week as Chief Financial Officer of Comfort Zone. An experienced financial manager, Mr. Conley was well aware that Mike Cousins, President of Comfort Zone, had hired him to "put the company's financial house in order." However, despite a week of non-stop discussions with other company executives, all he had found out about Comfort Zone's financial situation was that suppliers were clamoring for payment and that cash was available to satisfy only the most pressing needs. To help him understand Comfort Zone's financial situation, Mr. Conley had collected the summary financial information shown in Exhibits 1, 2, 3, and 4. As he was looking through some of the recent financial statements, the accounts payable clerk dropped a stack of overdue invoices on his desk. A note attached stated that cash was not available to pay the suppliers. Company Organization and History Comfort Zone was organized as a Florida corporation in 1961 with its headquarters in Jacksonville. Since that time, the company has become a major Southeastern distributor of heating, air conditioning and refrigeration equipment, parts, and supplies with 45 outlets in Florida, Georgia, Alabama, North Carolina, South Carolina and Mississippi. Through the addition of sales outlets to expand its market area and broadening of its product line, Comfort Zone increased sales from $23 million in 1985 to $108 million in its fiscal year ended January 31, 1991. Each sales outlet or "store" operated by Comfort Zone serves as a sales and distribution point for the full line of approximately 20,000 climate control and refrigeration products carried by the company. Each store is run by a manager who is responsible for the hiring and supervision of personnel and for sales, credit, purchasing, inventory, and cost control at the store level. Management feels that this delegation of authority and responsibility is a major contributor to the company's success and managers are paid on the basis of the net profit generated by their stores. Decisions affecting company policy, selection of senior management, capital expenditures, and the addition of product lines are reviewed by corporate management. Suppliers Products sold by the company are purchased on a regular basis from approximately 250 manufacturers and suppliers. Special order items are purchased occasionally from an additional 400 vendors. The largest single supplier of new air conditioning equipment and parts to the company provides approximately 15% of total purchases each year. The company has nonexclusive wholesale distributorship agreements with many of its suppliers. These agreements are generally informal and are subject to termination by either party on short notice. Most purchases are made on open account with terms of 2/10, net 30. Customers Sales are made to approximately 8,000 customers consisting of mechanical, heating, air conditioning and refrigeration contractors, institutions, governmental units and a variety of commercial users such as supermarket chains. Substantially all sales to customers are on open account and no single customer accounts for as much as 1 1/2% of total sales during any one __________________________ This case was originally written by Charles W. Young and revised by Peter C. Eisemann. It is intended as a basis for class discussion rather than to indicate either effective or ineffective handling of business situations. year. Terms typically are 2/10, net 30 but extended terms are sometimes offered to gain a competitive advantage. The company solicits sales from customers in both the new construction and the replacement and repair markets. New equipment sales provide relatively lower gross margins than sales of replacement parts. It is difficult for the company to monitor sales by market category; however, estimates of total sales contributed by products sold in the two markets during each of the past four fiscal years are as follows: 1988 1989 1990 1991 9% 91% 15% 85% 20% 80% 35% 65% New construction Replacement and repair Financial History In 1981 the Toxic Waste International Company acquired a 57% interest in Comfort Zone through purchase of common stock. Toxic's ownership was increased to 80.7% in 1984 when the company purchased a substantial block of common stock from another shareholder. On June 10, 1988, in an underwritten public offering, Toxic sold a large portion of its stock holdings and the company sold some new shares. The public sale plus a private sale to Comfort Zone's management personnel liquidated Toxic's holdings in the company. In mid-1991, the company had 3,328,722 shares of common stock issued and outstanding distributed among 588 holders of record. Selected high and low common stock prices are shown in Exhibit 5. Long-term debt outstanding on January 31, 1991 was as follows: Bank loan - 7 3/4% Insurance Co., loan - 8 1/2% Mortgage notes - 5 3/4% to 8% Other notes - 8% $2,430,000 6,000,000 305,157 330,000 $9,065,157 611,355 $8,453,802 Less: Current maturities Remainder The insurance company loan was closed on July 13, 1990 with part of the proceeds of $6 million applied to reduce the long-term bank loan from its previous high of $5.1 million. Principal payments on the insurance company loan are deferred until December 1, 1995 at which time payments of $300,000 will be due on each of the June 1 and December 1 interest payment dates until the note is paid in full on June 1, 2005. Covenants in the loan agreement require Comfort Zone to maintain consolidated net working capital of not less than $21 million and a consolidated current ratio of at least 2 to 1. Further, the company is precluded from incurring any funded or current debt except that short-term bank borrowing, not to exceed $3 million, is permitted providing that the company is out of the bank for sixty consecutive days in each fiscal year. A number of other negative covenants are also imposed by the loan agreement but none of them appear to materially constrain the company's ability to operate as it has in the past. However, violation of any of the negative covenants, in addition to failure to make payments of principal and interest when due, gives the insurance company the option to declare the loan immediately due and payable. 2 Current Situation While looking through the stack of reports and statements he had gathered on his desk, Mr. Conley notes that during the three years of public ownership the management of Comfort Zone has devoted most of its attention to promoting growth in both sales and earnings per share. In fact, growth was the major topic discussed by the president in his letter to shareholders in the fiscal year 1990 annual report. In this letter, the 33% increase in sales and 23% growth in earnings per share were highlighted and continued growth was predicted for 1991. Emphasis on growth was also apparent in the 1991 annual report. For example, the president's letter predicted: \"The Southeast, as a whole and Florida in particular, continues to be the nation's premier growth area. Management believes that exploitation of the market coverage we have achieved and continued efforts to control costs should result in another excellent year for the company.\" This letter also mentioned economic problems that had hit the country in late 1990, some product shortages, rising costs, and declining home construction as problems plaguing the industry in early 1991. The impact of these factors was predicted to be less severe in Florida because of the strength of the state's residential construction industry and because many expected construction to rise during the second half of 1991. (Data on building permits, as available in September 1991, are shown in Exhibit 7.) As Mr. Conley dug deeper into the financial information he had collected, he was surprised to find that the outstanding short-term loan was $2,100,000 over the limit imposed by the term loan debt covenant. Although a quick check provided information that the insurance company had agreed to the additional bank debt, their agreement had been obtained with the understanding that the loan would be reduced to $3 million by January 31, 1992. However, the insurance company had waived the requirement that Comfort Zone be out of the bank for sixty days during fiscal year 1992. Analysis of the July 31, 1991 accounts receivable balance provided the aging schedule shown in Exhibit 6. When questioned about the overdue amounts, the accounts receivable clerk reported that most accounts overdue in excess of 180 days represented sales of new equipment to construction contractors. Investigation of the inventory balance disclosed that the company had adopted a policy in 1991 to build inventory as a hedge against future shortages. About $4.8 million was added to inventory during fiscal year 1991 for this purpose and the build-up had continued into 1992. Further, it was found that there had been no significant inventory revaluations during the last several years to reflect obsolescence, deterioration or reductions of cost to market. Because purchases were initiated by store managers and all inventory was located at one of the 45 stores, there was little information available at the Jacksonville headquarters to identify either the unit composition of the inventory or its marketability. As Mr. Conley continued to peruse the information he had accumulated, he knew that Mr. Cousins expected him to convert his impressions of Comfort Zone's financial situation into an analysis that would clearly identify the magnitude and urgency of the company's problems and to propose a plan for their solution. 3 Exhibit 1 Comfort Zone Statements of Consolidated Income For Years Ended January 31 (Thousands of dollars) 1988 1989 1990 1991 Net Sales Cost of Merchandise Sold Gross Profit on Sales Operating Expenses (note 1) Profit from Operations Other Income Interest Expense Income before Income Taxes Provision for Income Taxes Net Income 45,852 34,626 11,226 7,931 3,295 27 -153 3,169 1,168 2,001 64,335 48,867 15,468 11,065 4,403 123 -303 4,223 1,616 2,607 85,362 64,632 20,730 14,644 6,086 195 -474 5,807 2,474 3,333 108,255 83,211 25,044 18,658 6,386 600 -900 6,086 2,417 3,669 Note 1: Includes Provision for doubtful accounts Depreciation expense N/A N/A N/A N/A N/A N/A For the years with N/A the numbers are included but the data are not available. 4 675 216 Exhibit 2 Comfort Zone Consolidated Balance Sheets January 31 (Thousands of dollars) 1988 1,755 8,037 12,975 15 22,782 1,266 507 66 24,621 1,677 11,754 20,133 60 33,624 2,028 1,131 72 36,855 960 16,254 25,008 306 42,528 2,280 1,062 18 45,888 636 3,696 2,376 6,708 3,576 1,563 891 8,550 2,883 13,887 5,157 2,400 612 10,245 2,697 15,954 8,454 150 714 8,367 9,231 20,619 5 1991 30 309 3,480 3,489 7,308 4,080 Current Liabilities: Notes payable to banks Current portion - LTD Trade accounts payable Accrued liabilities Total current liabilities Long-Term Debt Stockholders' Equity: Common stock - $.10 par Other paid-in capital Retained earnings Stockholders' equity Total 1990 945 6,015 11,739 63 18,762 1,257 543 57 20,619 Current Assets: Cash Accounts & notes rec. (net) Inventories Other current assets Total current assets Property, plant, & equip. (net) Goodwill Other Assets Total 1989 165 3,198 10,974 14,337 24,621 333 3,171 14,307 17,811 36,855 333 3,171 17,976 21,480 45,888 Exhibit 3 Comfort Zone Quarterly Statements of Consolidated Income (Thousands of Dollars) 3 mo. Apr-89 Net Sales Cost of Merchandise Sold Gross Profit on Sales Operating Expenses Profit from Operations Other Income Interest Expense Income before Income Taxes Provision for Income Taxes Net Income 6 mo. Jul-89 9 mo. Oct-89 12 mo. Jan-90 3 mo. Apr-90 6 mo. Jul-90 9 mo. Oct-90 12 mo. Jan-91 3 mo. Apr-91 6 mo. Jul-91 17,262 13,134 4,128 2,991 1,137 33 -108 41,061 31,266 9,795 6,630 3,165 72 -237 64,656 49,365 15,291 10,454 4,837 108 -324 85,362 64,632 20,730 14,644 6,086 195 -474 22,947 17,610 5,337 4,083 1,254 36 -132 54,186 41,715 12,471 8,969 3,502 219 -291 82,875 108,255 63,903 83,211 18,972 25,044 13,974 18,658 4,998 6,386 408 600 -486 -900 23,943 18,534 5,409 4,501 908 267 -243 57,147 43,752 13,395 9,977 3,418 450 -528 1,062 3,000 4,621 5,807 1,158 3,430 4,920 6,086 932 3,340 453 609 1,284 1,716 1,975 2,646 2,474 3,333 456 702 1,357 2,073 1,920 3,000 2,417 3,669 365 567 1,312 2,028 6 Exhibit 4 Comfort Zone Quarterly Consolidated Balance Sheets (Thousands of dollars) Apr-89 Current Assets Cash Accounts & notes rec. Allow for bad debts Inventory Prepaid and other Total current assets Property, plant & equip. Accumulated depr. Net Goodwill Other Assets Total Current Liabilities: Notes payable-bank Trade accounts payable Current portion-LTD Accrued taxes Other Total current liab. Long-Term Debt Stockholders' Equity: Common Stock Paid-in capital Retained earnings Stockholders' equity Total Jul-89 Oct-89 Jan-90 Apr-90 Jul-90 Oct-90 Jan-91 Apr-91 Jul-91 1,983 10,356 (474) 18,165 24 30,054 2,502 (867) 1,635 1,185 87 32,961 1,488 12,855 (690) 19,104 51 32,808 2,817 (906) 1,911 1,182 66 35,967 1,353 12,798 (903) 18,936 87 32,271 2,901 (948) 1,953 1,149 54 35,427 1,677 12,285 (531) 20,133 60 33,624 2,859 (831) 2,028 1,131 72 36,855 2,031 14,256 (729) 25,926 24 41,508 3,018 (873) 2,145 1,116 123 44,892 1,794 17,790 (939) 29,319 51 48,015 3,153 (918) 2,235 1,098 72 51,420 1,197 16,908 (1,239) 26,286 90 43,242 3,231 (972) 2,259 1,080 75 46,656 960 16,887 (633) 25,008 306 42,528 3,309 (1,029) 2,280 1,062 18 45,888 1,692 16,815 (699) 31,965 63 49,836 3,393 (1,086) 2,307 1,044 93 53,280 1,248 20,136 (828) 30,396 102 51,054 3,462 (1,128) 2,334 1,026 66 54,480 1,002 8,601 639 897 1,113 12,252 5,628 2,334 9,666 669 582 1,023 14,274 5,499 1,641 8,649 882 912 1,128 13,212 5,091 1,563 8,550 891 1,176 1,707 13,887 5,157 2,400 14,727 954 1,383 1,416 20,880 5,499 3,000 17,178 618 852 1,110 22,758 8,778 2,700 11,304 618 771 1,680 17,073 8,772 2,400 10,245 612 690 2,007 15,954 8,454 2,400 16,971 612 939 1,866 22,788 8,445 4,200 16,137 606 495 1,359 22,797 8,175 333 3,165 11,583 15,081 32,961 333 3,171 12,690 16,194 35,967 333 3,171 13,620 17,124 35,427 333 3,171 14,307 17,811 36,855 333 3,171 15,009 18,513 44,892 333 3,171 16,380 19,884 51,420 333 3,171 17,307 20,811 46,656 333 3,171 17,976 21,480 45,888 333 3,171 18,543 22,047 53,280 333 3,171 20,004 23,508 54,480 7 Exhibit 5 Comfort Zone Common Stock Prices Date High Low 1989 27.50 15.25 4/90 21.25 15.00 7/90 20.00 15.50 10/90 21.00 16.75 1/91 19.50 6.50 4/91 6.75 4.87 7/91 4.87 3.12 Quarter Ended Exhibit 6 Comfort Zone Aging of Accounts Receivable July 31, 1991 (Thousands of dollars) Amount Current Overdue (in days): 31-60 61-90 91-120 121-150 151-180 Over 180 Percent 8,055 2,415 2,013 1,410 1,209 1,008 4,026 20,136 July sales 40 12 10 7 6 5 20 100 10,329 8 Exhibit 7 Comfort Zone Private Residential Construction Authorized Number of Housing Units South Jan-89 Feb-89 Mar-89 Apr-89 May-89 Jun-89 Jul-89 Aug-89 Sep-89 Oct-89 Nov-89 Dec-89 Jan-90 Feb-90 Mar-90 Apr-90 May-90 Jun-90 Jul-90 Aug-90 Sep-90 Oct-90 Nov-90 Dec-90 Jan-91 Feb-91 U.S. 39,419 37,204 44,824 45,387 50,097 50,776 38,355 46,636 40,687 40,337 36,504 35,044 49,292 32,065 43,026 38,671 39,024 41,126 35,608 36,499 29,178 29,511 27,273 21,930 22,691 25,265 88,530 86,020 116,392 122,851 135,236 137,179 108,262 125,605 110,685 116,527 98,854 92,282 111,845 79,512 109,456 104,530 106,438 109,922 95,903 98,935 78,560 81,490 66,041 53,587 50,379 54,475 Source: Construction Review, Table C-1, January/February 1991, November/December 1991. 9 Proforma FI 4020 Why do Proforma? Evaluate alternative business strategies Identify profit and funding effects of alternative strategies Explicit measurement of risk Identify the amount of funds needed and appropriate structure for the funding Income Statement Sales: Past is a good indicator of the future Consider any momentous shift in the company Questions: Product Market condition Past growth rate. Prices, inflation. Industry growth and competition Market share of the company Cyclical Business cycle. Income Statement Expense Numbers: Common size income statement Is there a trend or do the numbers bounce around a particular value? Trend: Why the trend has occurred? Make an assumption Take into consideration of limits to improvements Some expense will not increase proportionally with sales: rent Interest rates Interest Expense = Amount borrowed * Interest rate A business can have multiple funding sources each with different interest rates. Compute: Average Interest rate = Interest Expense/Total amount borrowed. Total amount borrowed = average of beginning-ofperiod and end-of-period total outstanding debt from the balance sheet. Forecast the proforma interest rate based on the historical average interest rate. Tax Rate Compute the historical tax rate Example: 30% 31% 30.8% Tax rate = Tax expense/Earnings before Tax Take into account any expected changes in tax rate Apply the adjusted tax rate to the projected earnings before tax Balance Sheet For each Balance Sheet account: First identify its driver Compute historical values for this relationship Consider the historical pattern and the reasons for that pattern Incorporate any additional relevant information to arrive at an expected value Finally apply the linkage Cash Estimate the minimum cash requirement Two factors affect minimum cash level: Volatility: Higher minimum cash reserve required for higher volatility in day-to-day cash flow Scale of operation: Higher scale of operation requires higher cash reserve. Start with the minimum cash reserve Note: Cash is always positive Cash increases as sales increase Company borrows through notes payable even though it has cash Compute historical value of: Cash/Sales Example: Cash/Sales 1.2% 1.1% 1.4% Accounts Receivables Sales and collection period determines receivables AR = (Sales * ACP)/365 Proforma sales has already been estimated Proforma ACP: Compute historical ACP Example: 47 51 Identify: 49 Whether there is a trend. Is it due to: Relaxed credit policies with customers. Economic condition Competitive environment Or the pattern is stable around a number: Use average Estimate proforma ACP Finally compute proforma AR using proforma sales and ACP Inventory Inventory = (Cost of Goods Sold * Inventory days)/365 Proforma Cost of goods sold is obtained from proforma Income Statement. Proforma Inventory Days: Compute historical Inventory days. Example: 67 68 66 Indentify whether there is a trend or the number bounce around a certain value. Estimate proforma inventory days. Compute Proforma Inventory using proforma COGS and Inventory Days Other/Miscellaneous Balance Sheet Accounts Prepaids and other balance sheet accounts often move along with sales Compute historical: Other assets/Sales Example: 3.0% 3.1% 2.6% $change from year-to-year Use one of the above to estimate the proforma account Fixed Assets Fixed assets are lumpy The firm is likely to have no expenditure on fixed assets for certain years after a huge expansion FAT decreases when company acquires new FA FAT increase as Sales increases responding to the expansion The cycle continues The effect is greater in small firms as compared to large firms Fixed Assets Compute historical: FAT: 18.4 50 19.3 Example: 58 The proforma needs to consider the following: 17.5 Increase in GFA Example: What will be the typical annual amount of capital expenditure When will the next large increase occur? Apply to all GFA: Land, building, equipment, fixtures, and leasehold improvements Depreciation Depreciation expense is the driver of changes in accumulated depreciation A good approximation for the change in accumulated depreciation is depreciation expense Compute historical: Depreciation expense Example: 31 34 Depreciation expense/GFA Example: 6.2% 6.1% Estimate proforma Depreciation expense based on historical data Final Step: Proforma Accumulated Depreciation = Previous year accumulated depreciation + Proforma Depreciation expense Proforma NFA = Proforma GFA - Proforma Accum Dep Liabilities Notes Payable: Leave blank initially This is the balancing item We must estimate all other items before this Accounts Payable: Proforma COGS obtained from proforma Income Statement AP = (COGS * AP days)/365 Estimate proforma AP days based on historical AP days Example: 35 45 49 Accrued liabilities: Compute historical: Accruals/Sales Estimate proforma Accruals/Sales ratio LTD Determine whether there will be a new LTD issue and identify the amortization amount. LTD decreases when firm makes loan payments according to the amortization schedule. It is possible to infer the amount by looking at the past changes in LTD The current portion represents the principal payoff in the following year Example : 25 25 25 Each year decrease the LTD by the current portion of the LTD Common Stock and Paid-in-capital These accounts will only change when company issues new shares or repurchase shares For small companies these number remain more or less constant For large firms: employee stock options exercise increase common stock Retained Earnings Ending RE = Beginning RE + NI - Div Estimate proforma dividend payout ratio or $dividend Most companies maintain stable dividend policies Over a long period: the payout remains the same and $amount grows smoothly over time. Example: Div: Div payout: 45 17.5% 50 15.7% Notes Payables: Balancing the Balance Sheet A= Total Asset + Min Cash Estimate funds used L=Total Liabilities (except NP) + Equity Estimate funds provided Notes Payables: Balancing the Balance Sheet Let, Plug = A-L If Plug > 0, then funds used > funds provided Notes Payable = Plug Obtain additional external funding equal to plug Cash = Minimum Cash If Plug min cash Iterative Computation in Excel Click the Microsoft Office Button , click Excel Options, and then click the Formulas category. In the Calculation options section, select the Enable iterative calculation check box. To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time Excel will need to recalculate a worksheet. To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time Excel needs to recalculate a worksheet