Please Show work in Excel

Tire City, Inc. Income Statement - In Thousands ($000) Ratios: Cost of Sales % of Sales SG&A % of Sales Interest Expense % of Debt 0 Tax % of Taxable income 1 Cash % of Sales 2 Accounts Receivable % of Sales 3 Inventory % of Sales 4 Accounts Payable % of Sales 5 Taxes Payable % of Sales 6 7 1996 1997 $ $ $ 1993 16,230 $ 9,430 $ 6,800 $ 1994 20,355 $ 11,898 $ 8,457 $ 1995 23,505 13,612 9,893 $ $ 8 9 Revenue o Cost of Sales 1 Gross Margin 2 3 Sales, General, and Administrative Expenses 4 Depreciation Expense 5 Net Interest Expense 6 Total Operating Expenses 7 8 Pre-Tax Income 9 Income Taxes 0 Net Income 1 2 Dividends $ $ $ $ 5,195 $ 160 $ 119 $ 5,474 $ 6,352 $ 180 $ 106 $ 6,638 $ 7,471 213 94 7,778 $ $ $ $ $ 1,326 $ 546 $ 780 $ 1,819 $ 822 $ 997 $ 2,115 925 1,190 $ $ $ 155 $ 200 $ 240 3 Tire City, Inc. Balance Sheet In Thousands ($000) 1993 1994 1995 1996 1997 706 Current Assets Cash Accounts Receivable Inventory Total Current Assets $ $ $ $ 508 $ 2,545 $ 1,630 $ 4,683 $ 609 $ 3,095 $ 1,838 $ 5,542 $ 3,652 2,190 6,548 $ $ Gross Plant and Equipment Accumulated Depreciation Total Fixed Assets $ $ $ 3,232 $ (1,335) $ 1,897 $ 3,795 $ (1,515) $ 2,280 $ 4,163 (1,728) 2,435 $ $ Total Assets $ 6,580 $ 7,822 $ 8,983 $ $ Current Liabilities Accounts Payable Taxes Payable Total Current Liabilities $ $ $ 1,042 $ 1,145 $ 2,187 $ 1,325 $ 1,432 $ 2,757 $ 1,440 1,653 3,093 $ $ 875 Long-term Debt Total Liabilities $ $ 1,125 $ 3,312 $ 1,000 $ 3,757 $ 3,968 $ $ 1,135 $ 1,135 Stock Retained Earnings Total Equity $ $ $ 1,135 $ 2,133 $ 3,268 $ 1,135 $ 2,930 $ 4,065 $ 1,135 $ 3,880 5,015 $ 1,135 $ 1,135 Total Liabilities and Equity $ 6,580 $ 7,822 $ 8,983 $ 1,135 $ 1,135 Tire City, Inc. Jack Martin, Chief Financial Officer of Tire City, Inc., was preparing for a meeting with his company's bank later in the week. At that meeting, Mr. Martin intended to present a request that the bank grant Tire City a five-year loan to finance anticipated growth in the company and the expansion of the company's warehouse facilities. In preparation for his meeting, Martin had gathered some recent financial statements for Tire City (see Exhibit 1). Company Background Tire City, Inc. (TCI) was a rapidly growing retail distributor of automotive tires in northeastern United States. Tires were sold through a chain of 10 shops located throughout eastern Massachusetts, southern New Hampshire and northern Connecticut. These stores kept sufficient inventory on hand to service immediate customer demand, but the bulk of Tire City's inventory was managed at a central warehouse outside Worcester, Massachusetts. Individual stores could be easily serviced by this warehouse, which could usually fill orders from individual stores within 24 hours. For the year ended in December, 1995, TCI had sales of $23,505,000. Net income for that period was $1,190,000. During the previous three years, sales had grown at a compound annual rate in excess of 20%. This record was a reflection of Tire City's reputation for excellent service and competitive pricing, which yielded high levels of customer satisfaction. Past Relationship With MidBank In 1991, TCI had borrowed funds from MidBank to build a warehouse. This loan was being repaid in equal annual installments of $125,000. At the end of 1995, the balance due on the loan was $875,000. Also, in 1991 TCI established a line of credit at MidBank. The company had not yet borrowed any money under this credit arrangement. The Current Financial Need TCI had decided to expand its warehouse facilities to accommodate future growth. Indeed, the current warehouse facilities were practically bulging at the seams. During the next 18 months, TCI planned to invest $2,400,000 on its expansion, $2,000,000 of which would be spent during 1996 (no other capital expenditures were planned for 1996 and 1997). This expansion would fulfill the company's anticipated needs for several years. The warehouse construction project was expected to be completed in early 1997. Therefore, TCI would not be able to deduct any depreciation on the new building in 1996. However, Mr. Martin was told by his accountant that in 1997, Tire City could recognize a depreciation expense of 5% of the warehouse's total cost. The dollar value of TCI's depreciation expense on its other assets in 1996 and 1997 would be the same as it was in 1995. The warehouse expansion project was designed so that disruption of the company's current operations would be minimized. However, management expected that by the end of 1996, TCI would temporarily have to decrease its inventories to a level of $1,625,000, significantly lower than the $2,190,000 shown on the balance sheet at the end of 1995. This cutback in inventories was expected to last only until the warehouse construction project was completed in early 1997. Mr. Martin had estimated that, by the end of 1997, inventory would rise back to the same proportional relationship to sales that it had in 1995. Other than this temporary drop in inventory in 1996, the warehouse expansion was not expected to affect TCI's operations in any other material respects. Operating margins were expected to be consistent with recent past experience (the temporary drop in inventory would not affect cost of goods sold as a percent of sales, for example). Likewise, current accounts other than inventory were expected to maintain steady relationships to sales. Cash balances, for instance, would be maintained at a level of 3% of sales during the next two years. Although the Federal statutory marginal corporate tax rate was 35%, the average tax rate on TCI's pre-tax income had typically been higher than this due to miscellaneous local taxes. This higher overall level of taxation was expected to continue in the future at rates consistent with the most recent past experience. In view of this anticipated stability, Mr. Martin expected TCI's dividend payout policy to remain unchanged in the foreseeable future. TCI had preliminary discussions with MidBank about borrowing money to finance the warehouse expansion and the growth of the business. The proposed terms of the financing called for taking down (i.e., borrowing) the loan in two separate parts on an as-needed basis: one in 1996 and one in 1997. The loan would be repaid in four equal annual installments. The first installment payment would take place one year after the construction of the warehouse was completed (i.e., in 1998). The interest rate was set at 10% per year. Mr. Martin's Task In preparation for his meeting, Mr. Martin intended to develop a set of pro forma financial statements for the company. He and his staff had projected a 20% increase in sales each year in 1996 and in 1997, from $23,505,000 to $28,206,000 and $33,847,000, respectively. Mr. Martin's first priority was to predict what the rest of the income statement and the balance sheet for the firm would look like at the end of 1996 and 1997. For years ending 12/31 1993 1994 1995 INCOME STATEMENT $ 16,230 9,430 6,800 $ 20,355 11,898 8,457 $ 23,505 13,612 9,893 Net sales Cost of sales Gross profit Selling, general, and administrative expenses Depreciation Net interest expense Pre-tax income Income taxes Net income 5,195 160 119 1,326 546 780 6,352 180 106 1,819 822 997 7,471 213 94 2,115 925 $ 1,190 $ $ Dividends $ 155 $ 200 $ 240 BALANCE SHEET $ $ Assets Cash Accounts receivable Inventories Total current assets 508 2,545 1,630 4,683 609 3,095 1,838 5,542 $ 706 3,652 2,190 6,548 Gross plant & equipment Accumulated depreciation Net plant & equipment 3,232 1,335 1,897 3,795 1,515 2,280 4,163 1.728 2,435 Total assets $ 6,580 $ 7,822 $ 8,983 LIABILITIES $ Current maturities of long-term debt Accounts payable Accrued expenses Total current liabilities 125 1,042 1.145 2,312 $ 125 1,325 1,432 2,882 $ 125 1,440 1,653 3,218 Long-term debt 1,000 875 750 Common stock Retained earnings Total shareholders' equity 1,135 2,133 3,268 1,135 2,930 4,065 1,135 3.880 5,015 Total liabilities $ 6,580 $ 7,822 $ 8,983 What is the Net Income in 1997 (Use whole $ amount, do NOT include $ sign - for example, $12,345 would be entered as 12345). What is the Accounts Receivable Balance at the end of 1997 (Use whole $ amount, do NOT include $ sign - for example, $12,345 would be entered as 12345). Tire City, Inc. Income Statement - In Thousands ($000) Ratios: Cost of Sales % of Sales SG&A % of Sales Interest Expense % of Debt 0 Tax % of Taxable income 1 Cash % of Sales 2 Accounts Receivable % of Sales 3 Inventory % of Sales 4 Accounts Payable % of Sales 5 Taxes Payable % of Sales 6 7 1996 1997 $ $ $ 1993 16,230 $ 9,430 $ 6,800 $ 1994 20,355 $ 11,898 $ 8,457 $ 1995 23,505 13,612 9,893 $ $ 8 9 Revenue o Cost of Sales 1 Gross Margin 2 3 Sales, General, and Administrative Expenses 4 Depreciation Expense 5 Net Interest Expense 6 Total Operating Expenses 7 8 Pre-Tax Income 9 Income Taxes 0 Net Income 1 2 Dividends $ $ $ $ 5,195 $ 160 $ 119 $ 5,474 $ 6,352 $ 180 $ 106 $ 6,638 $ 7,471 213 94 7,778 $ $ $ $ $ 1,326 $ 546 $ 780 $ 1,819 $ 822 $ 997 $ 2,115 925 1,190 $ $ $ 155 $ 200 $ 240 3 Tire City, Inc. Balance Sheet In Thousands ($000) 1993 1994 1995 1996 1997 706 Current Assets Cash Accounts Receivable Inventory Total Current Assets $ $ $ $ 508 $ 2,545 $ 1,630 $ 4,683 $ 609 $ 3,095 $ 1,838 $ 5,542 $ 3,652 2,190 6,548 $ $ Gross Plant and Equipment Accumulated Depreciation Total Fixed Assets $ $ $ 3,232 $ (1,335) $ 1,897 $ 3,795 $ (1,515) $ 2,280 $ 4,163 (1,728) 2,435 $ $ Total Assets $ 6,580 $ 7,822 $ 8,983 $ $ Current Liabilities Accounts Payable Taxes Payable Total Current Liabilities $ $ $ 1,042 $ 1,145 $ 2,187 $ 1,325 $ 1,432 $ 2,757 $ 1,440 1,653 3,093 $ $ 875 Long-term Debt Total Liabilities $ $ 1,125 $ 3,312 $ 1,000 $ 3,757 $ 3,968 $ $ 1,135 $ 1,135 Stock Retained Earnings Total Equity $ $ $ 1,135 $ 2,133 $ 3,268 $ 1,135 $ 2,930 $ 4,065 $ 1,135 $ 3,880 5,015 $ 1,135 $ 1,135 Total Liabilities and Equity $ 6,580 $ 7,822 $ 8,983 $ 1,135 $ 1,135 Tire City, Inc. Jack Martin, Chief Financial Officer of Tire City, Inc., was preparing for a meeting with his company's bank later in the week. At that meeting, Mr. Martin intended to present a request that the bank grant Tire City a five-year loan to finance anticipated growth in the company and the expansion of the company's warehouse facilities. In preparation for his meeting, Martin had gathered some recent financial statements for Tire City (see Exhibit 1). Company Background Tire City, Inc. (TCI) was a rapidly growing retail distributor of automotive tires in northeastern United States. Tires were sold through a chain of 10 shops located throughout eastern Massachusetts, southern New Hampshire and northern Connecticut. These stores kept sufficient inventory on hand to service immediate customer demand, but the bulk of Tire City's inventory was managed at a central warehouse outside Worcester, Massachusetts. Individual stores could be easily serviced by this warehouse, which could usually fill orders from individual stores within 24 hours. For the year ended in December, 1995, TCI had sales of $23,505,000. Net income for that period was $1,190,000. During the previous three years, sales had grown at a compound annual rate in excess of 20%. This record was a reflection of Tire City's reputation for excellent service and competitive pricing, which yielded high levels of customer satisfaction. Past Relationship With MidBank In 1991, TCI had borrowed funds from MidBank to build a warehouse. This loan was being repaid in equal annual installments of $125,000. At the end of 1995, the balance due on the loan was $875,000. Also, in 1991 TCI established a line of credit at MidBank. The company had not yet borrowed any money under this credit arrangement. The Current Financial Need TCI had decided to expand its warehouse facilities to accommodate future growth. Indeed, the current warehouse facilities were practically bulging at the seams. During the next 18 months, TCI planned to invest $2,400,000 on its expansion, $2,000,000 of which would be spent during 1996 (no other capital expenditures were planned for 1996 and 1997). This expansion would fulfill the company's anticipated needs for several years. The warehouse construction project was expected to be completed in early 1997. Therefore, TCI would not be able to deduct any depreciation on the new building in 1996. However, Mr. Martin was told by his accountant that in 1997, Tire City could recognize a depreciation expense of 5% of the warehouse's total cost. The dollar value of TCI's depreciation expense on its other assets in 1996 and 1997 would be the same as it was in 1995. The warehouse expansion project was designed so that disruption of the company's current operations would be minimized. However, management expected that by the end of 1996, TCI would temporarily have to decrease its inventories to a level of $1,625,000, significantly lower than the $2,190,000 shown on the balance sheet at the end of 1995. This cutback in inventories was expected to last only until the warehouse construction project was completed in early 1997. Mr. Martin had estimated that, by the end of 1997, inventory would rise back to the same proportional relationship to sales that it had in 1995. Other than this temporary drop in inventory in 1996, the warehouse expansion was not expected to affect TCI's operations in any other material respects. Operating margins were expected to be consistent with recent past experience (the temporary drop in inventory would not affect cost of goods sold as a percent of sales, for example). Likewise, current accounts other than inventory were expected to maintain steady relationships to sales. Cash balances, for instance, would be maintained at a level of 3% of sales during the next two years. Although the Federal statutory marginal corporate tax rate was 35%, the average tax rate on TCI's pre-tax income had typically been higher than this due to miscellaneous local taxes. This higher overall level of taxation was expected to continue in the future at rates consistent with the most recent past experience. In view of this anticipated stability, Mr. Martin expected TCI's dividend payout policy to remain unchanged in the foreseeable future. TCI had preliminary discussions with MidBank about borrowing money to finance the warehouse expansion and the growth of the business. The proposed terms of the financing called for taking down (i.e., borrowing) the loan in two separate parts on an as-needed basis: one in 1996 and one in 1997. The loan would be repaid in four equal annual installments. The first installment payment would take place one year after the construction of the warehouse was completed (i.e., in 1998). The interest rate was set at 10% per year. Mr. Martin's Task In preparation for his meeting, Mr. Martin intended to develop a set of pro forma financial statements for the company. He and his staff had projected a 20% increase in sales each year in 1996 and in 1997, from $23,505,000 to $28,206,000 and $33,847,000, respectively. Mr. Martin's first priority was to predict what the rest of the income statement and the balance sheet for the firm would look like at the end of 1996 and 1997. For years ending 12/31 1993 1994 1995 INCOME STATEMENT $ 16,230 9,430 6,800 $ 20,355 11,898 8,457 $ 23,505 13,612 9,893 Net sales Cost of sales Gross profit Selling, general, and administrative expenses Depreciation Net interest expense Pre-tax income Income taxes Net income 5,195 160 119 1,326 546 780 6,352 180 106 1,819 822 997 7,471 213 94 2,115 925 $ 1,190 $ $ Dividends $ 155 $ 200 $ 240 BALANCE SHEET $ $ Assets Cash Accounts receivable Inventories Total current assets 508 2,545 1,630 4,683 609 3,095 1,838 5,542 $ 706 3,652 2,190 6,548 Gross plant & equipment Accumulated depreciation Net plant & equipment 3,232 1,335 1,897 3,795 1,515 2,280 4,163 1.728 2,435 Total assets $ 6,580 $ 7,822 $ 8,983 LIABILITIES $ Current maturities of long-term debt Accounts payable Accrued expenses Total current liabilities 125 1,042 1.145 2,312 $ 125 1,325 1,432 2,882 $ 125 1,440 1,653 3,218 Long-term debt 1,000 875 750 Common stock Retained earnings Total shareholders' equity 1,135 2,133 3,268 1,135 2,930 4,065 1,135 3.880 5,015 Total liabilities $ 6,580 $ 7,822 $ 8,983 What is the Net Income in 1997 (Use whole $ amount, do NOT include $ sign - for example, $12,345 would be entered as 12345). What is the Accounts Receivable Balance at the end of 1997 (Use whole $ amount, do NOT include $ sign - for example, $12,345 would be entered as 12345)