please show calculations

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 2023 , Great Lakes 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 2022 . This cutback in inventories was expected to last only until the warehouse construction project was completed in early 2024. Mr. Kolias had estimated that, by the end of 2024 , inventory would rise back to the proportional relationship to sales that it had in 2022. Other than this temporary drop in inventory in 2023 , the warehouse expansion was not expected to affect Great Lakes' 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 percentage 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 durng the next two years. Although the Federal statutory marginal corporate tax rate was 35%, the average tax rate on Great Lakes' pretax income had typically been higher than this due to miscellaneous local taxes. The 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. Kolias expected Great Lakes' dividend payout policy to remain unchanged in the foreseeable future. Great Lakes had preliminary discussions with SkyBank 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 2023 and one in 2024 . The loan would be repaid in four equal annual installments. The first 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 Great Lakes' pretax income had typically been higher than this due to miscellaneous local taxes. The 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. Kolias expected Great Lakes' dividend payout policy to remain unchanged in the foreseeable future. Great Lakes had preliminary discussions with SkyBank 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 2023 and one in 2024 . 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 2025). The interest rate was set at 10% per year. Mr. Kolias' Task In preparation for his meeting, Mr. Kolias 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 2023 and 2024 from $23,505,000 to $28,206,000 and $33,847,000, respectively. Mr. Kolias' 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 2023 and 2024. 21 BALANCE SHEET 22 23 Assets 24 Cash balances 25 Accounts receivable 26 Inventories: 27 Total current assets 28 29 Gross plant & equipment 30 Accumulated depreciation 31 Net plant \& equipment Total assets 32 33 \begin{tabular}{|l|l|l|} \hline 3,232 & 3,795 & 4,163 \\ \hline 1,335 & 1,515 & 1,728 \\ \hline 1,897 & 2,280 & 2,435 \\ \hline \end{tabular} 56,580$7,822$8,983 35 Uabilities 36 Current maturities of long-term debt 37 Accounts payable 38 Accrued expenses 39 Total current liabilities 40 Long-term debt. 42 Common stock 43 Retained eamings 44 Total shareholders' equity \begin{tabular}{rrrrr} 125 & $ & 125 & $ & 125 \\ 1,042 & 1,325 & 1,440 \\ 1,145 & 1,432 & 1,653 \\ \hline 2,312 & 2,882 & 3,218 \end{tabular} 45 46 Total liablities and Equity \begin{tabular}{r|r|r} \hline 1,000 & 875 & 750 \\ \hline 1,135 & 1,135 & 1,135 \\ \hline 2,133 & 2,930 & 3,880 \\ \hline 3,268 & 4,065 & 5,015 \end{tabular} 47 S 6,580 \& 7,822$8,983 Great Lakes Industrial Supply, Inc. Chuck Kolias, Chief Financial Officer of Great Lakes Industrial Supply, Inc. was preparing for a meeting with his company's bank later in the week. At that meeting, Mr. Kolias intended to present a request that the bank grant Great Lakes Industrial Supply 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, Mr. Kolias had gathered some recent financial statements for Great Lakes. (see Excel template and Exhibit 1). Company Background Great Lakes Industrial Supply, Inc (Great Lakes) was a rapidly growing distributor of industrial hoses, fittings and related supplies in the upper mid-west United States. Supplies were sold to industrial clients through a chain of 12 outlets located throughout Ohio, Pennsylvania Michigan, and Illinois. These outlets kept sufficient inventory on hand to service immediate customer demand, but the bulk of Great Lakes' inventory was managed at a central warehouse outside Cleveland, Ohio. Individual stores could be easily serviced by this warehouse, which could usually fill orders from individual outlets within 24 hours. For the year ended December, 2022, Great Lakes 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 Great Lakes' reputation for excellent service and competitive pricing, which yielded high levels of customer satisfaction. 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 Great Lakes' reputation for excellent service and competitive pricing, which yielded high levels of customer satisfaction. Past Relationship with SkyBank In 2019, Great Lakes had borrowed funds from SkyBank to build a warehouse. This loan was being repaid in equal annual installments of $125,000. At the end of 2022 , the balance due on the loan was $875,000. Also, in 2018 , Great Lakes established a line of credit at SkyBank. The company had not yet borrowed any money under this credit arrangement. The Current Financial Need Great Lakes had decided to expand its warehouse facilities to accommodate future growth. Indeed, the current warehouse facilities were practically bukging at the seams. During the next 18 months, Great Lakes planned to invest $2,400,000 on its expansion, $2,000,000 of which would be spent during 2023 (no other capital expenditures were planned for 2023 and 2024). This expansion would fulfill the company s n icipated n e s o everal years. The warehouse construction project was expected to be completed in early 2024. Therefore, Great Lakes would not be able to deduct any depreciation on the new building in 2023. However, Mr. Kolias was told by his accountant that in 2024, Great Lakes could recognize a depreciation expense of 5% of the warehouse's total cost. The dollar value of Great Lakes' depreciation expense on its other assets in 2023 and 2024 would be the same as it was in 2022 . Annual purchases can be estimated as (purchases = ending inventory - beginning inventory + cost of goods sold). A B Assignment: 1 Based on Mr. Kollas' prediction of 20% sales growth in 2023 and 2024 and relying on other assumptions provided in the Great Lakes case, prepare complete pro forma forecast of Great Lakes' 2023 and 2024 income statements and year-end balance sheets. Assume that any new financing, if needed, will be in the form of bank debt. Assume that all debt (any exisitng or new debt) carries an interest rate of 10%. 2 How much does Great Lakes have to borrow in 2023? How much in 2024? 3 Assess the financial health of Great Lakes Indusrial Supply, Inc, in past and forecasted periods. Specificaly, calculate the financial ratios defined in the worksheet financial ratios and interpret the results. Will Great Lakes Industrial Supply be in a stronger or weaker financial condition two years from now? 4 Suppose that the proposed terms of the bank credit included a covenant (a contractual obligation that binds the borrower to specific actions or outcomes as a condition for extending a loan) that read as follows: "The company must maintain net working capital (defined for purposes of this loan as accounts receivable plus inventories minus accounts payable) of at least $4 million. For purposes of this covenant, net working capital will be measured at the end of each fiscal year," is Great Lakes likely to be able to satisfy this covenant in both 2023 and 2024 ? 5 What would be the impact on Great Lakes' external funding needs and interest coverage (calculated as EBT / Interest expense) as of the end of 2023 and 2024 if: a. Inventory were not reduced by the end of 2023 ? b. Great Lakes experienced higher growth in its revenues than was originally anticipated in 2023 and 2 f 4 ? c. Days receivables were reduced to 45 days? d. Accrued Expenses were to grow less than expected in 2023 and 2024 ? 6 As a lender, would you be willing to loan Great Lakes Indusrial Supply, Inc. the funds needed to expand it warehouse facilities and finance its growth? Why or why not? 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 2023 , Great Lakes 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 2022 . This cutback in inventories was expected to last only until the warehouse construction project was completed in early 2024. Mr. Kolias had estimated that, by the end of 2024 , inventory would rise back to the proportional relationship to sales that it had in 2022. Other than this temporary drop in inventory in 2023 , the warehouse expansion was not expected to affect Great Lakes' 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 percentage 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 durng the next two years. Although the Federal statutory marginal corporate tax rate was 35%, the average tax rate on Great Lakes' pretax income had typically been higher than this due to miscellaneous local taxes. The 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. Kolias expected Great Lakes' dividend payout policy to remain unchanged in the foreseeable future. Great Lakes had preliminary discussions with SkyBank 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 2023 and one in 2024 . The loan would be repaid in four equal annual installments. The first 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 Great Lakes' pretax income had typically been higher than this due to miscellaneous local taxes. The 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. Kolias expected Great Lakes' dividend payout policy to remain unchanged in the foreseeable future. Great Lakes had preliminary discussions with SkyBank 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 2023 and one in 2024 . 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 2025). The interest rate was set at 10% per year. Mr. Kolias' Task In preparation for his meeting, Mr. Kolias 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 2023 and 2024 from $23,505,000 to $28,206,000 and $33,847,000, respectively. Mr. Kolias' 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 2023 and 2024. 21 BALANCE SHEET 22 23 Assets 24 Cash balances 25 Accounts receivable 26 Inventories: 27 Total current assets 28 29 Gross plant & equipment 30 Accumulated depreciation 31 Net plant \& equipment Total assets 32 33 \begin{tabular}{|l|l|l|} \hline 3,232 & 3,795 & 4,163 \\ \hline 1,335 & 1,515 & 1,728 \\ \hline 1,897 & 2,280 & 2,435 \\ \hline \end{tabular} 56,580$7,822$8,983 35 Uabilities 36 Current maturities of long-term debt 37 Accounts payable 38 Accrued expenses 39 Total current liabilities 40 Long-term debt. 42 Common stock 43 Retained eamings 44 Total shareholders' equity \begin{tabular}{rrrrr} 125 & $ & 125 & $ & 125 \\ 1,042 & 1,325 & 1,440 \\ 1,145 & 1,432 & 1,653 \\ \hline 2,312 & 2,882 & 3,218 \end{tabular} 45 46 Total liablities and Equity \begin{tabular}{r|r|r} \hline 1,000 & 875 & 750 \\ \hline 1,135 & 1,135 & 1,135 \\ \hline 2,133 & 2,930 & 3,880 \\ \hline 3,268 & 4,065 & 5,015 \end{tabular} 47 S 6,580 \& 7,822$8,983 Great Lakes Industrial Supply, Inc. Chuck Kolias, Chief Financial Officer of Great Lakes Industrial Supply, Inc. was preparing for a meeting with his company's bank later in the week. At that meeting, Mr. Kolias intended to present a request that the bank grant Great Lakes Industrial Supply 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, Mr. Kolias had gathered some recent financial statements for Great Lakes. (see Excel template and Exhibit 1). Company Background Great Lakes Industrial Supply, Inc (Great Lakes) was a rapidly growing distributor of industrial hoses, fittings and related supplies in the upper mid-west United States. Supplies were sold to industrial clients through a chain of 12 outlets located throughout Ohio, Pennsylvania Michigan, and Illinois. These outlets kept sufficient inventory on hand to service immediate customer demand, but the bulk of Great Lakes' inventory was managed at a central warehouse outside Cleveland, Ohio. Individual stores could be easily serviced by this warehouse, which could usually fill orders from individual outlets within 24 hours. For the year ended December, 2022, Great Lakes 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 Great Lakes' reputation for excellent service and competitive pricing, which yielded high levels of customer satisfaction. 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 Great Lakes' reputation for excellent service and competitive pricing, which yielded high levels of customer satisfaction. Past Relationship with SkyBank In 2019, Great Lakes had borrowed funds from SkyBank to build a warehouse. This loan was being repaid in equal annual installments of $125,000. At the end of 2022 , the balance due on the loan was $875,000. Also, in 2018 , Great Lakes established a line of credit at SkyBank. The company had not yet borrowed any money under this credit arrangement. The Current Financial Need Great Lakes had decided to expand its warehouse facilities to accommodate future growth. Indeed, the current warehouse facilities were practically bukging at the seams. During the next 18 months, Great Lakes planned to invest $2,400,000 on its expansion, $2,000,000 of which would be spent during 2023 (no other capital expenditures were planned for 2023 and 2024). This expansion would fulfill the company s n icipated n e s o everal years. The warehouse construction project was expected to be completed in early 2024. Therefore, Great Lakes would not be able to deduct any depreciation on the new building in 2023. However, Mr. Kolias was told by his accountant that in 2024, Great Lakes could recognize a depreciation expense of 5% of the warehouse's total cost. The dollar value of Great Lakes' depreciation expense on its other assets in 2023 and 2024 would be the same as it was in 2022 . Annual purchases can be estimated as (purchases = ending inventory - beginning inventory + cost of goods sold). A B Assignment: 1 Based on Mr. Kollas' prediction of 20% sales growth in 2023 and 2024 and relying on other assumptions provided in the Great Lakes case, prepare complete pro forma forecast of Great Lakes' 2023 and 2024 income statements and year-end balance sheets. Assume that any new financing, if needed, will be in the form of bank debt. Assume that all debt (any exisitng or new debt) carries an interest rate of 10%. 2 How much does Great Lakes have to borrow in 2023? How much in 2024? 3 Assess the financial health of Great Lakes Indusrial Supply, Inc, in past and forecasted periods. Specificaly, calculate the financial ratios defined in the worksheet financial ratios and interpret the results. Will Great Lakes Industrial Supply be in a stronger or weaker financial condition two years from now? 4 Suppose that the proposed terms of the bank credit included a covenant (a contractual obligation that binds the borrower to specific actions or outcomes as a condition for extending a loan) that read as follows: "The company must maintain net working capital (defined for purposes of this loan as accounts receivable plus inventories minus accounts payable) of at least $4 million. For purposes of this covenant, net working capital will be measured at the end of each fiscal year," is Great Lakes likely to be able to satisfy this covenant in both 2023 and 2024 ? 5 What would be the impact on Great Lakes' external funding needs and interest coverage (calculated as EBT / Interest expense) as of the end of 2023 and 2024 if: a. Inventory were not reduced by the end of 2023 ? b. Great Lakes experienced higher growth in its revenues than was originally anticipated in 2023 and 2 f 4 ? c. Days receivables were reduced to 45 days? d. Accrued Expenses were to grow less than expected in 2023 and 2024 ? 6 As a lender, would you be willing to loan Great Lakes Indusrial Supply, Inc. the funds needed to expand it warehouse facilities and finance its growth? Why or why not