Gilcrest Patio Furnishings, Inc. (GPF), manufactures patio furniture from PVC pipe. The company was founded in 1976 as one of the first manufac- turers to utilize plastic pipe products and construction. Since then, the com- pany founder and president, Mark Gilcrest, has believed that the key to his success is his ability to translate planned company growth into forecasted financial statements. Specifically, Gilcrest had always found his banker to be receptive to his loan requests when he could demonstrate an under- standing of the financial implications of his decisions. This was particularly important because the company's sales were quite seasonal and were fi- nanced by a $5 million line of credit. Although Gilcrest did not completely understand accounting income statements and balance sheets, he had always found a forecasted cash bud- get to be critical. This statement helped him to understand exactly what his cash position was and where the balance was expected to be. Therefore, he had always insisted that the vice president of finance, Jeff Reilly, prepare a 12-month rolling cash budget. Reilly also prepared projected income state- ments and balance sheets to correspond to the cash budget. This forecast was revised throughout the year as new information was received. In November of each year, Reilly began the preparation of the sub- sequent year's forecast. His objective this year was to determine if the cur- rent $5 million line of credit would be adequate to meet the financial needs of the firm in 19x1. Reilly always started the process by asking other managers in the com- pany to provide much of the needed information because he found that this approach built confidence in the resulting budget and forecast. It also allowed all department managers to formulate their own plans. Since each manager participated in the formulation of the budget and forecast, each felt responsible for making the actual results match the forecasts. Thus, the resulting forecast turned out to be much more accurate than a single point estimate provided by a computerized model. After receiving the managers' plans, Reilly summarized them into a large spreadsheet model that in- cluded 12 monthly cash budgets, income statements, and balance sheets. The marketing department provided Reilly with the sales forecasts shown in Exhibit 1. Historically, the marketing department's actual results have varied 10 percent above or below their forecasts. GPF's credit policy was 1/10, net 30. Previous experience indicated that 10 percent of the customers paid within the discount period, 42 per- cent paid within a month (one-month lag), and the remaining customers paid late, usually within 60 days (two-month lag). The marketing depart- ment forecasts did not include any discounts taken by customers for early payment on account. The production department provided information of forecasted manufacturing expenses. The manufacturing process was a "level" pro- duction strategy that is, product was produced at a level amount for the entire year. Thus, production/sales mismatches caused inventory to in- crease as production exceeded sales, and then to decrease as sales exceeded Year 19x0 19x1 19x2 -EXHIBIT 1 Gilcrest Patio Furnishings, Inc. Monthly Forecasted Sales Month November December January February March April May June July August September October November December January February Sales Forecast $ 4,717,372 (actual) 4,832,952 (actual) 4,900,000 5,000,000 5,100,000 8,200,000 10,600,000 10,500,000 9,800,000 9,200,000 7,100,000 5,900,000 5,600,000 5,200,000 5,300,000 5,500,000 production. This permitted GPF to keep a constant number of manufac- turing employees on payroll, even though monthly sales forecasts varied. In 19x1, production was estimated to run at a monthly sales level of $7,300,000, up substantially from the $6,325,000 per month produced in 19x0. The manufacturing process was only two days long and material could be ordered and received within five days. Materials used in the man- ufacture of the product represented approximately half of the cost of goods sold, or 36 percent of the selling price of the product (or 36 percent of revenue). GPF's accounting department claims that 60 percent of the material bills are paid in the month in which the materials are received. The remainder of the bills are paid in the subsequent month. The production process also requires manufacturing labor. Produc- tion labor costs represented the other half of cost of goods sold, or 36 per- cent of the forecasted selling price (or 36 percent of the forecasted reve- nue). These costs are incurred when the product is manufactured. Labor expenses are paid over two months-50 percent are paid in the month incurred and 50 percent are paid the following month. The accounting department provided summarized monthly general, administrative, advertising, and selling expenses from the previously pre- pared departmental budgets. Exhibit 2 details these expenses. These ex- penses were assumed to be paid and expensed in the same month. GPF expensed all overhead-that is, the company did not attach overhead to the manufactured product. The accounting department reminded Reilly that the company had a $15,000,000 mortgage outstanding with a 12.5 percent coupon rate. The company was expected to pay semiannual interest in March and Septem- ber. No principal payments were expected this year. Interest expense is assumed to be accrued and paid in the same month. The manufacturing vice president planned to upgrade the company's computerized inventory system for an estimated $3,500,000. The account- ants assumed that the system would have a five-year useful life and that the company would depreciate the system using straight-line depreciation. EXHIBIT 2 Gilcrest Patio Furnishings, Inc. Forecasted Monthly Expenses Expenses General Administrative Advertising Selling Other Amount $415,000 230,000 4% of revenue 7% of revenue 40,000 The accountants also forecasted that the company would have to make quarterly tax payments of $100,000, payable in April, June, Septem- ber, and December. Furthermore, the company is expected to maintain its dividend payments of $100,000 in February, May, August, and November. The accounting department also provided an estimated income state- ment and balance sheet for the 19x0 fiscal year-end. These statements (provided in Exhibits 3 and 4) are believed to accurately reflect Gilcrest's financial position on December 31, 19x0. Mark Gilcrest has always insisted on a minimum cash balance of $5,000,000. Company policy was to invest any excess cash in a short-term money market account. Reilly estimated that the 19x1 average pretax rate of return on such funds would be 7.5 percent. Interest income would be recognized when the cash was received. Sales EXHIBIT 3- Gilcrest Patio Furnishings, Inc. Income Statement (thousands) 19x0 Less: Discounts Cost of goods sold Gross profit Operating expenses General Administrative Selling Advertising Depreciation Other Total operating expenses Income from operations Other income Income before interest and taxes Interest expense Long-term interest Short-term interest Total interest expense Income before taxes Income taxes $75.894 (76) (55,403) $20,415 4,437 2,487 5,234 2,985 1,739 450 $17,332 $3,083 150 $3,233 1,875 405 $2,280 953 362 Income before taxes Income taxes Income after taxes Common dividends Retained earnings 953 362 $591 $370 $221 Current assets Cash & equivalents Accounts receivable Inventory Other current assets Total current assets Fixed assets Plant Equipment Accumulated depreciation Net fixed assets Other assets EXHIBIT 4 Gilcrest Patio Furnishings, Inc. Total assets Balance Sheet (thousands) 19x0 $7,367 6,777 5,495 141 $19,780 19,564 9,240 (9,552) $19,252 35 $39,067 Current liabilities Accounts payable Accrued labor Accrued taxes Line of credit Other current liabilities Total current liabilities Long-term liabilities Mortgage payable Deferred taxes Other long-term debt Total long-term liabilities Stockholders' equity Common stock Retained earnings Total equity Total liabilities and equity $1,092 1,152 185 2,054 25 $4,508 15,000 294 42 $15,336 1,300 17,923 $19,223 $39,067 GPF's line of credit agreement calls for a 2 percent risk premium over the existing prime rate, which Reilly guessed would average 8.5 percent in 19x1. Interest would be paid and expensed on the previous month's loan requirements. The loan also required a "cleanup" provision of two months that is, for two successive months, the outstanding loan balance must be $0. Reilly felt that he had all the information he needed to complete the forecasts, including the projected tax rate of 38 percent (34 percent federal tax and 4 percent state tax net of federal deduction). QUESTIONS* 1. Prepare a 12-month forecasted cash budget for GPF. 2. Will the current line of credit meet GPF's financial needs for 19x1? If not, how large a line of credit will the company need? 3. Prepare 12 months of forecasted income statements and balance sheets. *The computerized model is named "GILCRESTA.wkl." Gilcrest Patio Furnishings, Inc. 63 4. What information is provided by each of the statements (cash budget, income statement, and balance sheet)? How is the information provided by each statement different? 5. Prepare an annual forecasted income statement and balance sheet for 19x1. 6. Analyze the consistency between the company's historical financial per- formance and it's projected financial performance. (Hint: Compute rele- vant financial ratios and percentage relationships to revenue.) Revise your forecasts to correct any discrepancies between historical relation- ships and forecast assumptions. 7. If you are using the computerized model, perform a sensitivity analysis on revenue. Vary revenue from - 10 percent to +10 percent of forecast in increments of 5 percent. Obtain all monthly funds need using the data table command. 8. Is GPF's $5,000,000 minimum cash balance a reasonable amount? If not, how might a sufficient minimum cash balance be established? What do you recommend as a minimum cash balance? 9. Based on your analysis, if you were a loan officer, would you recommend making an additional loan to Gilcrest? Why or why not? If you do not recommend that all of the company's needs be financed with debt, how would you suggest that GPF restructure its finances?