Use the following information about XYZ Plumbing (FY=12/31), a public company with the most recent closing stock price of $4.50, to answer the following three problems:

(Answers must be filled out in excel in yellow area)

INFORMATION SHEET

(Open first image in new tab to see clearly)

PROBLEMS

Common Size Common Size Common Size XYZ Plumbing - Income Statement Assumptions 2019 - 2020 Revenue increases by 25% ($ basis) 2020E $ $ Revenue CGS Gross Profit 2018 10,000,000 4,500,000 5,500,000 100.0% 45.0% 55.0% 2019 13,000,000 5,785,000 7,215,000 2019 2018 2019 $ $ Gross MARGIN increases by 1.0% XYZ Plumbing - Balance Sheet 2018 ASSETS Cash 1,500,000 AR 1,000,000 Inventory 2,000,000 Pre-paid expenses 500,000 Total Current Assets 5,000,000 SG&A R&D Deprec & Amort Total Op. Expense 2,000,000 1,000,000 500,000 3,500,000 20.0% 10.0% 5.0% 35.0% 2,470,000 1,000,000 500,000 3,970,000 SG&A improves by 1.0% (% basis) R&D increases by 10% ($ basis) No change in Deprec & Amort cost 5,039,700 1,500,000 2,300,000 600,000 9,439,700 LIABILITIES Accounts Payable Notes Payable Current portion of LTD Accrued Liabilities Total Current Liabilities 1,800,000 1,500,000 1,000,000 700,000 5,000,000 2,000,000 1,500,000 1,000,000 1,520,000 6,020,000 Operating Profit $ 2,000,000 20.0% $ 3,245,000 Long-Term Debt 320******** 9,000,000 Building and Improvement 15,000,000 Equipment 5,000,000 20,000,000 10,000,000 15,000,000 5,000,000 20,000,000 Interest cost Interest income 805,000 45,000 8.1% 0.5% 875,000 60,000 Interest costs are given Interest income is given 875,000 130,000 Depreciation Net PP&E 500,000 19,500,000 1,000,000 19,000,000 Stockholder's Equity Common Stock at Par Paid in Capital Retained Earnings Total Stockholder's Equity 100,000 9,900,000 500,000 10,500,000 100,000 9,900,000 2,419,700 12,419,700 $ $ Pre-Tax Tax (35% in 2017. 21% rate in 2018 and 2019) Net Income 1,240,000 434,000 806,000 12.4% 35.0% 8.1% 2,430,000 510,300 1,919,700 See D22 $ $ Total Assets 24,500,000 28,439,700 Total Liabilities + Equity 24,500,000 28,439,700 $ 0.08 $ 0.19 EPS - Basic EPS - Diluted Average Shares Outstanding 10,000,000 10,000,000 10,000,000 Diluted Shares Diluted Shares Diluted Shares Information Officers and Directors Options Exercise Price 3,000,000 $ 1.00 Publice Warrants Exercise Price 2,500,000 $ 4.00 Stock Price used for calculating Diluted Shares $ 4.50 10,000,000 Diluted Shares Calculation Table Basic Shares O&D warrants Public Total 10,000,000 Proceeds Officers and Directors Public Shareholders Total Proceeds Buyback Price Total Shares repurchased $ 4.50 Diluted Shares Do not type in the Blue or Brown boxes PROBLEM SOLVING Problem 1 Statement of Cash Flows 2019 Admin Key Pts Net Income 1 1 Adjustments Depreciation and amortization Cash Provided by Operating Assets and Liabilities Accounts Receivable Inventories Pre-paid expenses Accounts payable Accrued liabilities Net Cash provided (used) by operating activities TUTTI NINN Cash Provided from Investing Activities Additions to property, plant and equipment Net Cash provided (used) by investing activities 2 2 2 Cash Provided from Financing Activities Additions to long term borrowing Addition to short term borrowing Net Cash provided (used) by financing activities 2 2 Net Cash Flow 2 Do not type in the Blue or Brown boxes below Problem 2 Analysis Admin Key 2019 Pts 2 Current Ratio Quick Ratio Gross Margin Operating Margin Accounts Receivable turnover Inventory turnover Total asset turnover Debt to Equity Return on Equity 2 2 2 2 2 2 2 Problem 3 Projections Admin 2020E Key Pts Revenue (Increase of 25% from 2019) CGS Gross Profit (Gross Margin increases by 1.0% from 2019) 1 1 SG&A (Improves by 1.0% from 2019) R&D (Increases by 10% in total dollars from 2019) Deprec & Amort (no change from 2019) Total Op. Expense 1 Operating Profit 2 Interest cost (given) Interest income (given) $ $ 875,000 130,000 875,000 130,000 1 Pre-Tax Tax (21% rate) Net Income 2 1 EPS - Basic EPS - Diluted (See Information sheet for Equity Structure) 8 Common Size Common Size Common Size XYZ Plumbing - Income Statement Assumptions 2019 - 2020 Revenue increases by 25% ($ basis) 2020E $ $ Revenue CGS Gross Profit 2018 10,000,000 4,500,000 5,500,000 100.0% 45.0% 55.0% 2019 13,000,000 5,785,000 7,215,000 2019 2018 2019 $ $ Gross MARGIN increases by 1.0% XYZ Plumbing - Balance Sheet 2018 ASSETS Cash 1,500,000 AR 1,000,000 Inventory 2,000,000 Pre-paid expenses 500,000 Total Current Assets 5,000,000 SG&A R&D Deprec & Amort Total Op. Expense 2,000,000 1,000,000 500,000 3,500,000 20.0% 10.0% 5.0% 35.0% 2,470,000 1,000,000 500,000 3,970,000 SG&A improves by 1.0% (% basis) R&D increases by 10% ($ basis) No change in Deprec & Amort cost 5,039,700 1,500,000 2,300,000 600,000 9,439,700 LIABILITIES Accounts Payable Notes Payable Current portion of LTD Accrued Liabilities Total Current Liabilities 1,800,000 1,500,000 1,000,000 700,000 5,000,000 2,000,000 1,500,000 1,000,000 1,520,000 6,020,000 Operating Profit $ 2,000,000 20.0% $ 3,245,000 Long-Term Debt 320******** 9,000,000 Building and Improvement 15,000,000 Equipment 5,000,000 20,000,000 10,000,000 15,000,000 5,000,000 20,000,000 Interest cost Interest income 805,000 45,000 8.1% 0.5% 875,000 60,000 Interest costs are given Interest income is given 875,000 130,000 Depreciation Net PP&E 500,000 19,500,000 1,000,000 19,000,000 Stockholder's Equity Common Stock at Par Paid in Capital Retained Earnings Total Stockholder's Equity 100,000 9,900,000 500,000 10,500,000 100,000 9,900,000 2,419,700 12,419,700 $ $ Pre-Tax Tax (35% in 2017. 21% rate in 2018 and 2019) Net Income 1,240,000 434,000 806,000 12.4% 35.0% 8.1% 2,430,000 510,300 1,919,700 See D22 $ $ Total Assets 24,500,000 28,439,700 Total Liabilities + Equity 24,500,000 28,439,700 $ 0.08 $ 0.19 EPS - Basic EPS - Diluted Average Shares Outstanding 10,000,000 10,000,000 10,000,000 Diluted Shares Diluted Shares Diluted Shares Information Officers and Directors Options Exercise Price 3,000,000 $ 1.00 Publice Warrants Exercise Price 2,500,000 $ 4.00 Stock Price used for calculating Diluted Shares $ 4.50 10,000,000 Diluted Shares Calculation Table Basic Shares O&D warrants Public Total 10,000,000 Proceeds Officers and Directors Public Shareholders Total Proceeds Buyback Price Total Shares repurchased $ 4.50 Diluted Shares Do not type in the Blue or Brown boxes PROBLEM SOLVING Problem 1 Statement of Cash Flows 2019 Admin Key Pts Net Income 1 1 Adjustments Depreciation and amortization Cash Provided by Operating Assets and Liabilities Accounts Receivable Inventories Pre-paid expenses Accounts payable Accrued liabilities Net Cash provided (used) by operating activities TUTTI NINN Cash Provided from Investing Activities Additions to property, plant and equipment Net Cash provided (used) by investing activities 2 2 2 Cash Provided from Financing Activities Additions to long term borrowing Addition to short term borrowing Net Cash provided (used) by financing activities 2 2 Net Cash Flow 2 Do not type in the Blue or Brown boxes below Problem 2 Analysis Admin Key 2019 Pts 2 Current Ratio Quick Ratio Gross Margin Operating Margin Accounts Receivable turnover Inventory turnover Total asset turnover Debt to Equity Return on Equity 2 2 2 2 2 2 2 Problem 3 Projections Admin 2020E Key Pts Revenue (Increase of 25% from 2019) CGS Gross Profit (Gross Margin increases by 1.0% from 2019) 1 1 SG&A (Improves by 1.0% from 2019) R&D (Increases by 10% in total dollars from 2019) Deprec & Amort (no change from 2019) Total Op. Expense 1 Operating Profit 2 Interest cost (given) Interest income (given) $ $ 875,000 130,000 875,000 130,000 1 Pre-Tax Tax (21% rate) Net Income 2 1 EPS - Basic EPS - Diluted (See Information sheet for Equity Structure) 8