Work on this project and explain step by step:

Commercial Credit Development Program Associates rely heavily on their ability to think critically through complex financial scenarios and clearly communicate their understanding of a customer's business operations and financial performance to inform senior leaders of the Bank. The following Case Study offers the opportunity to display those particular skillsets. Carefully review the enclosed Company Overview, Expansion Strategy, Loan Request, and Financial Statements before answering the following questions.

Company Overview: Steel City Door Company (SCD), located in Pittsburgh, PA, manufactures and sells high-end exterior and garage doors for residential homes and commercial buildings. Approximately 70% of revenue is sourced from residential home builders, including custom home builders and large regional builders. The remaining 30% is direct-to-consumer sales to existing residential homeowners. SCD does very little marketing due to its long-standing relationship with its customers and strong reputation in their existing markets. Until recently, all SCD's customers were located in Pennsylvania. This market has allowed SCD to operate at 85% capacity on average. SCD is subject to seasonal impacts due to their particular industry. SCD acts as its own distributor with five company-owned trucks delivering across its marketing area. Unshipped inventory and raw materials are stored at its warehouse in Pittsburgh, close to its manufacturing plant. SCD believes is has a unique product niche with a concentration on high-end exterior and garage doors. They generally compete with other small to moderately sized door manufacturers, but national retailers do compete in this space with a limited array of high-end options. However, SCD believes national retailers lack the quality that is expected from their customer base. Management of SCD includes President and CEO, Charlie (Chuck) Steel. Mr. Steel founded SCD in 1979 and grew the company to what it is today. At 70 years old, Mr. Steel is planning to transition out of his role over the course of two years. His Daughter, Margaret (Maggie) Steel, and current VP of Sales for SCD, is positioned to take over. Expansion Strategy: Over the last two years, SCD has started prospecting customers in The Carolinas and Virginia to expand their footprint. The Company has attracted several new relationships in these states and has decided to begin a full-scale marketing effort across these new territories.

SCD plans to have these markets up and running by Q1 2024 and contributing an additional $2.5 million in revenue during 2024. However, SCD requires funding to purchase their new facility, equipment and support their operational growth to support the new capacity.

Loan Request: The Management of Steel City Door Company has approached the bank and has asked for the following request: 1. Increase the existing line of credit from $1.2 million to $1.5 million. The interest rate will remain a variable rate set at Libor + 2.50%. 2. Provide a new debt facility to finance the purchase of the new plant and warehouse in their new market. The details of the new loan are listed below: a. Loan amount of $600,000 b. The loan will require monthly payments of loan principal and interest c. The loan will have a fixed interest rate at 4.25% d. Twenty-year amortization period. 3. Provide a new debt facility to finance the purchase of the equipment in their new market. The details of the new loan are listed below: a. Loan amount of $200,000 b. The loan will require monthly payments of loan principal and interest c. The loan will have a fixed interest rate at 4.25% d. Seven-year amortization period.

Case Study - Steel City Door Company Unqualified Unqualified Unqualified August 30, 2023 Dec. 31 Dec. 31 Dec. 31 Statement in Thousands 2020 2021 2022 Highlights. USD USD USD INCOME STATEMENT: Sales 6,740 7,399 8,212 Gross Profit 4,553 5,006 5,481 Operating Expenses 4,405 4,789 5,254 NPBT 78 142 183 NPAT 50 90 131 EBITDA 210 303 376 Cash Dividends 25 40 250 BALANCE SHEET: Total Current Assets 436 1,581 1,626 Net Fixed Assets 998 1,072 970 Total Assets 2,434 2,653 2,596 Short Term Obligations 736 916 1,08 Total Current Liabilities 904 1, 109 1,295 Long Term Deb 345 327 195 Total Liabilities 1,414 1,583 ,645 Net Worth ,020 1,070 951 RATIOS: Sales Growth 9.78% 10.99% Gross Margin 67.55% 67.66% 66.74% Profit Margin 0.74% 1.22% 1.60% Current Ratio 1.59 1.43 1.26 Quick Ratio 0.56 0.50 0.43 Working Capital 532 472 331 Age of Receivables 25 25 23 Days Supply in Inventory 146 148 135 Age of Payables 26 27 26 Debt/Tangible Net Worth 1.39 1.48 1.73 Sr Funded Debt/EBITDA 5.15 4.10 3.41 Ttl Funded Debt/EBITDA 5.81 4.50 3.73 Breakeven Sales - Cash Basis 7,503 8, 112 Actual Sales/Breakeven Sales CASH FLOW: Incr (Decr) in Cash Cash From Sales 7,354 ,200 Cash From Trading 4,889 5,443 Net Cash After Operations 135 296 Cash After Financing Costs 20 (33) Cash After Debt Amortization 109) (184) Capital Expenditures (160) (12) Financing Surplus (Requirement) (269) (196) Page 5 / 12 - +Case Study - Steel City Door Company Unqualified Unqualified Unqualified August 30, 2023 Dec. 31 Dec. 31 Dec. 31 Statement in Thousands 2020 2021 2022 ASSETS Common Size usb % usD % usb % Cash 40 16 42 16 35 13 Trade Accounts Receivable 475 195 520 19.6 533 205 Reserve for Bad Debts (-) (11) (0.5) (11) (0.4) (12) (0.5) Net Accounts Receivable 464 191 509 19.2 521 201 Raw Materials 450 185 500 18.8 575 221 Work in Progress 200 8.2 175 6.6 215 83 Finished Goods 225 92 295 1141 218 84 Total Inventory 875 35.9 970 36.6 1,008 38.8 Other Current Assets 57 23 60 23 62 24 Total Current Assets 1,436 59.0 1,581 59.6 1,626 62.6 Property, Plant, & Equipment 1,126 46.3 1,207 455 1,120 431 Accumulated Depreciation (-) (128) (5.3) (135) (5.1) (150) (5.8) Net Fixed Assets 998 41.0 1,072 404 970 37.4 TOTAL ASSETS 2,434 100.0 2,653 100.0 2,596 100.0 LIABILITIES Common Size usD % usD % usbD % Line of Credit 607 249 765 288 935 36.0 Current Portion Long Term Debt 80 33 80 3.0 80 3.1 Current Portion Capital Leases 49 20 71 2:7 71 27 Trade Accounts Payable 156 64 179 6.7 191 7.4 Accrued Expenses 10 04 12 05 15 06 Other Current Liabilities 2 01 2 01 3 01 Total Current Liabilities 904 3741 1,109 41.8 1,295 499 Long Term Debt 240 99 160 6.0 80 3.1 Capital Lease Obligations 105 43 167 6.3 115 44 Deferred Income Taxes 25 1.0 27 1.0 35 1.3 Subordinated Debt 140 58 120 45 120 46 Total Liabilities 1,414 58.1 1,683 59.7 1,645 63.4 NET WORTH Common Size usbD % usbD % usb % Common Stock 335 1338 335 126 335 129 Retained Earnings 685 28.1 735 277 616 23.7 Total Net Worth TOTAL LIABILITIES & NET WORTH Case Study - Steel City Door Company Unqualified Unqualified Unqualified August 30, 2023 Dec. 31 Dec. 31 Dec. 31 Statement in Thousands 2020 2021 2022 REVENUE Common Size USD % USD % USD Sales 6,740 100.0 7,399 100.0 8,212 100.0 Cost of Goods Sold 2,187 32.4 2,393 32.3 2,731 33.3 Gross Profit 4,553 67.6 5,006 67.7 5,481 6 OPERATION EXPENSES Common Size USD % USD % USD % Selling, General & Admin. Exp. 2,979 44.2 3,229 43.6 3,510 42.7 271 4.0 298 40 328 4.0 Officers' Salaries Lease/Rent Expense 120 1.8 131 18 138 1.7 Salaries and Wages 660 9.8 680 9.2 755 9.2 63 0.9 85 1.1 105 Insurance and Utilities 1.3 Other Operating Expenses 250 3.7 280 3.8 304 3.7 Depreciation 62 09 86 12 114 14 Operating Expenses 4,405 65.4 4,789 64.7 5,254 64.0 Operating Profit 148 2.2 217 2.9 227 2.8 OTHER R. & E. (NET INCOME) Common USD USD USD Size Gain on Sale of Assets 0 0.0 0.0 35 0.4 Interest Expense 70 1.0 75 1.0 79 1.0 Profit Before Tax 78 12 142 19 183 2.2 Current Taxes (-) (0.4) (52) (0.7) (52) (0.6) Profit Before Extraord Items 50 0.7 90 1.2 131 1.6 NET INCOME 50 0.7 90 1.2 131 1.6 Page 7 / 12 +Case Study - Steel City Door Company Unqualified Unqualified Unqualified August 30, 2023 Dec. 31 Dec. 31 Dec. 31 Statement in Thousands 2020 2021 2022 CHANGES IN RETAINED EARNINGS USD % USD USD Common Size Beginning Net Worth 995 97.5 1,020 95.3 1,070 112.5 Changes in Retained Earnings: Net Income (Loss) 50 4.9 90 8.4 131 Distributions 25 25 10 37 250 26.3 Total Change in RE 25 2.5 50 4.7 (119) (12.5) CHANGES IN NET WORTH Common Size USD % USD % USD % Change in Net Worth 25 2.5 50 4.7 (119) (12.5) Ending Total Net Worth 1,020 100.0 1,070 100.0 951 100.0Case Study - Steel City Door Company Unqualified Unqualified August 30, 2023 Dec. 31 Dec. 31 Statement in Thousands 2021 2022 UCA Cash Flow usbD usD Net Sales 7,399 8,212 Change in Current Receivables (45) (12) Cash from Sales 7,354 8,200 Cost of Goods Sold (Less Depr.) (2,393) (2,731) Change in Inventories (95) (38) Change in Accounts Payable 23 12 Cash Production Costs (2,465) (2,757) CASH FROM TRADING 4,889 5,443 Selling, General & Admin. Exp. (3,229) (3,510) Other Operating Expenses (1,474) (1,630) Change in Prepaids 0 0 Change in Accrued Expenses 2 3 Chg in Oth. Curr. Assets/Liab. (3) (1) Cash Operating Costs (4,704) (5,138) CASH AFTER OPERATIONS 185 305 Other Income (Expense) 0 35 Change in Other Liabilities 0 0 Income Tax Expense (52) (52) Change in Deferred Income Taxes 2 8 Change in Income Taxes Payable 0 0 Taxes Paid & Other Inc (Exp) (50) (9) NET CASH AFTER OPERATIONS 135 296 Dividends or Owners Withdrawals (40) (250) Change in Dividends Payable 0 0 Interest Expense (75) (79) Change in Interest Payable 0 0 Cash Financing Costs (115) (329) CASH AFTER FINANCING COSTS 20 (33) Current Portion Long Term Debt (129) (151) CASH AFTER DEBT AMORTIZATION (109) (184) Capital Expenditures (160) (12) Change in Long Term Investments 0 0 Change in Intang/Other Assets 0 0 Cash Used for Plant/Invest (160) (12) FINANCING SURPLUS/REQUIREMENT (269) (196) Change in Short Term Debt 158 170 Change in Long Term Debt 113 19 Change in Contributed Capital 0 0 Oth. Chgs in Retained Earnings 0 0 Total External Financing 271 189 FINANCING SURPLUS (REQUIREMENT) + TOTAL EXTERNAL FINANCING 2 (7) Change in Cash & Equivalents 2 (7) Case Study - Steel City Door Company Unqualified Unqualified Unqualified August 30, 2023 Dec. 31 Dec. 31 Dec. 31 Statement in Thousands 2020 2021 2022 Ratios. USD USD USD Operating Ratios: Sales Growth 9.78% 10.99% Pre-Tax Profit Margin 1.16% 1.92% 2.23% Profit Margin 0.74% 1.22% 1.60% Return on Assets (ROA) 2.05% 3.39% 5.05% Return on Equity (ROE) 4 90% 8.41% 13.77% Asset Turnover 2.77 2.79 3.16 Current Position: Current Ratio 1.59 1.43 1.26 Quick Ratio 0.56 0.50 0.43 Working Capital 532 472 331 Working Capital/Assets 21.86% 17.79% 12.75% Working Capital Turnover 12.67 15.68 24.81 Receivable Turnover 14.53 14.54 15.76 Age of Receivables 25 25 23 Inventory Turnover 2.50 2.47 2.71 Days Supply in Inventory 146 148 135 Payable Turnover 14.02 13.37 14.30 Age of Payables 26 27 26 Equity Position: Owner Equity/Assets 41.91% 40.33% 36.63% Creditor Equity/Assets 58.09% 59.67% 63.37% Debt/Tangible Net Worth 1.39 1.48 1.73 Sr Funded Debt/EBITDA 5.15 4.10 3.41 Ttl Funded Debt/EBITDA 5 81 4.50 3.73 Fixed Assets/Long Term Debt 195.69% 226.16% 277.14% Fixed Assets/Tangible Net Worth 97.84% 100. 19% 102.00% Plant Turnover 6.75 6.90 8.47 Other: Interest Coverage (NPBT) 2.11 2.89 3.32 Prin & Interest Coverage (NPBT) 1.06 1.14 Interest Coverage (Operating Cash) 1 80 3.75 Prin & Interest Coverage (OC) 0.66 1.29 Sustainable Growth Rate 2.51% 4.90% (11.12)% Breakeven Sales - Cash Basis 7,503 8,112 Actual Sales/Breakeven Sales 11, 180.71 6,380.95 Basic Defense Interval (19) (25) (31) Bankruptcy Ratio: Z value 3.65 3.68 4.01 Z 2.90 Strong Page 10 / 12 - +Case Study - Steel City Door Company Unqualified Unqualified Unqualified 2022 August 30, 2023 Dec. 31 Dec. 31 Dec. 31 RMA(16) Statement in Tho 2020 2021 2022 10MM-25MM RMA Comparison by Sales - NAICS Code USD USD USD 321911 Assets: Cash & Equivalents 1.6 1.6 1.3 6.3 Trade Receivables (net) 19.1 19.2 20.1 27.6 Inventory 35.9 36.6 38.8 28.5 All Other Current 2.3 2.3 2.4 23 Total Current 59.0 59.6 62.6 64.7 Fixed Assets (net) 41.0 40.4 37.4 27.7 Intangibles (net) 0.0 0.0 0.0 4.2 All Other Non-Current .0 00 0.0 3.4 Total Assets 100.0 100.0 100.0 100.0 Liabilities Notes Payable - Short Term 24.9 28.8 6.0 8.2 Cur. Mat. - LTD 5.3 5.7 5.8 4.2 Trade Payables 6.4 6 .7 7.4 9.8 Income Taxes Payable 0.0 0.0 0.0 0.1 All Other Current .5 0.5 0.7 9.2 Total Current 37.1 41.8 49.9 31.5 Long Term Debt 14.2 12.3 7.5 16.6 Deferred Taxes 1.0 1.0 13 0.2 All Other Non-Current 5.8 4.5 4.6 4.9 Net Worth 41.9 40.3 36.6 46.8 Total Liabilities & Net Worth 100.0 100.0 100.0 100.0 Income Data Net Sales 100.0 100.0 100.0 100.0 Gross Profit 67.6 67.7 66.7 29.3 Operating Expenses 65.4 64 7 64 0 23.7 Operating Profit 2.2 2.9 2.8 5.7 All Other Expenses (net) 10 10 0.5 0.9) Profit Before Taxes 1.2 1.9 2.2 6.5 RATIOS: Current 1.59 1.43 1.26 1.9 0.56 0.50 0.43 1.1 Quick Sales/Receivables 14.53 14.54 15.76 11.2 Cost of Sales/Inventory 2.50 2.47 2.71 6.7 Cost of Sales/Payables 14.02 13.37 14.30 23.3 Days Receivable 25.13 25.11 23.16 33.0 Days Inventory 146.03 147.95 134.72 54.0 Days Payables 26.04 27.30 25.53 16.0 Sales/Working Capital 12.67 15.68 24.81 7.1 EBIT/Interest 2.11 2.89 3.32 9.0 Cash Flow/Cur. Mat. LTD 0.87 1.17 1.62 NA Fixed/Worth 0.98 1.00 1.02 0.6 Debt/TNW 1.39 1.48 1.73 1.2 % Profit Before Tax/TNW 7:65 11 13.27 2 19.24 @ 37.3 % Profit Before Tax/Total Assets 3.20 5.35 7.05 16.4Case Study - Steel City Door Company Unqualified Unqualified Unqualified 2022 August 30, 2023 Dec. 31 Dec. 31 Dec. 31 RMA(16 Statement in Thousands 2020 2021 2022 10MM-25MM % Officers Comp/Sales 4.02 4.03 3.99 NA Page 12 / 12 - +