You will need to separately forecast NOWC and use Days Outstanding and enter the required total values for NOWC for each year into the spread sheet in #12 & #13.

The Comstock Corporation is operating at full capacity and cannot meet the demand for its floor mats. They are considering investing in a new floor mat manufacturing machine that has an estimated life of five years. The machine will increase their capacity and should enable them to increase sales by 5% over the next five years. The cost of the machine is $50,000 installed, andthe machine will be depreciated on a straight line basis over its five-year life to a value of $0. At the end of five years they will need to replace this machine if they want to continue meeting the higher demand.

The floor mat manufacturing machine will result in new sales of 2,000 floor mats in year 1. Sales are estimated to grow by 5% per year each year through year three and remain constant for the final two years. (You will need to adjust these Sales projections in the spread sheet.) Comstock will charge customers $18 per floor mat for all three years. The floor mats have a cost per unit to manufacture of $9 each excluding the effect of depreciation. Incremental Selling, General & Administrative expenses are estimated to be $2,000 annually. Comstock spent $3,000 over the last year on a market study to estimate the incremental sales from expanding capacity.

The resulting increase in manufacturing capacity will require an increase in various net working capital accounts. It is estimated that the Comstock Corporation will collect their receivables in 30 days on average (Accounts Rec./Ave. Daily Sales). They expect to need to hold 45 days of finished product inventory (Inventory/Ave. Daily COGS). They will continue to pay their bills in 30 days on averages (Accounts Payable/Ave. Daily COGS).

The firm is in the 20% tax bracket, and has a cost of capital of 10%.

11. Calculate the required investment in each element of Net Operating Working Capital (Accounts Receivable, Inventory & Accounts Payable) for the five years of the project using the Days Sales or Days COGS measure given above. Be sure to use next years Sales and COGS for your projection of required NOWC as you will need to make the investment in NOWC at the start of the period. The first adjustment to cash flow for NOWC will be in Year 0 at the start of the project. Use these estimates of NOWC Investments to calculate the Cash Flow from NOWC which is part of Free Cash Flow.

12. Use the 5 year financial projection template on Canvas to calculate the NOPAT, Free Cash Flow, NPV and IRR of the 5 year project using the NOWC estimates from above.

13. Using a new spreadsheet model, assume instead that Comstock can depreciate all of the $50,000 cost of the machine in the first year. Calculate the new NOPAT, Free Cash Flow for all five years and the project NPV and IRR.

(Use the below excel format please)

C3 : X fx 0 . 1 Net Operating Working Capital Projection Assumptions Days Receivable to Sales Days Inventory to COGS Days Payables to COGS Unit Sales = Price per Unit= $ Cost of Sales per unit = COGS as % of Sales = 0% 3 4 Year Sales Cost of Sales - - S $ 13 - $ - $ 14 15 - $ Average Daily Sales = Average Daily COGS = - - $ 18 - $ - $ - - $ $ 19 20 - - $ $ - $ - $ Projected Accounts Receivable $ Projected Inventory $ Projected Accounts Payable $ Required NOW $ Change in NOWC $ Cash Flow Impact of NOWC $ 22 $ - $ $ 23 $ - $ $ $ 24 25 26 #11 NOWC Projection #12 #13 M21 c o E F G H I J K L M N P Q R s. A 1 Simple Financial Projection Template Assumptions OCC = Tax Rate = Unit Sales = Price per Unit Cost of Sales per unit = COSG as % of Sales = Annual SG&A= Incremental R&D = $ Enter Expenses as Positive Numbers 1 2 Year Sales Cost of Sales Gross Profit Selling, General & Admin. Research & Development $ Depreciation EBIT $ Income Tax $ NOPAT (Unlevered Net Income) $ Add Back Depreciation Subtract Capital Exp./Dispositions $ Subtract Increase in NOW $ Free Cash Flow $ Discount Factor FCF Present Value $ NPV $ IRR 1.00000 1.00000 1.00000 1.00000 1.00000 #NUM! $ - $ - $ $ - $ - Enter Required NOWC Here Enter the values you calculated in #11 Required NOW Change in NOW Cash Flow Impact of NOW $ $ $ Enter Required Capital Expenditure Here 0.2 0.2 0.2 Straight LineDepreciation Projection Capital Expenditure $ Annual Depr. Factors Depreciation Expense EOY Net Book Value $ #11 NOWC Projection $ - $ - $ - $ - $ - #12 #13 + C3 X fx 0% - K L M N O P Q R 09 A 1 Simple Financial Projection Template Assumptions OCC = Tax Rate = Unit Sales = Price per Unit= $ Cost of Sales per unit = $ COSG as % of Sales Annual SG&A= Incremental R&D Enter Expenses as Positive Numbers - $ Year Sales Cost of Sales Gross Profit Selling, General & Admin. Research & Development $ Depreciation EBIT $ Income Tax $ NOPAT (Unlevered Net Income) $ Add Back Depreciation Subtract Capital Exp./Dispositions $ Subtract Increase in NOW $ Free Cash Flow $ Discount Factor FCF Present Value $ NPV $ IRR 1 1.00000 1.00000 - 1.00000 - .00000 - $ 1.00000 - $ $ $ $ #NUM! - $ - $ - $ - $ - $ . Enter Required NOWC Here Enter the values you calculated in #11 Required NOW $ Change in NOWC $ Cash Flow Impact of NOW $ - Enter Required Capital Expenditure Here Straight LineDepreciation Projection Capital Expenditure $ Annual Depr. Factors Depreciation Expense EOY Net Book Value $ - $ - - $ $ - $ . #11 NOWC Projection #12 #13 + C3 : X fx 0 . 1 Net Operating Working Capital Projection Assumptions Days Receivable to Sales Days Inventory to COGS Days Payables to COGS Unit Sales = Price per Unit= $ Cost of Sales per unit = COGS as % of Sales = 0% 3 4 Year Sales Cost of Sales - - S $ 13 - $ - $ 14 15 - $ Average Daily Sales = Average Daily COGS = - - $ 18 - $ - $ - - $ $ 19 20 - - $ $ - $ - $ Projected Accounts Receivable $ Projected Inventory $ Projected Accounts Payable $ Required NOW $ Change in NOWC $ Cash Flow Impact of NOWC $ 22 $ - $ $ 23 $ - $ $ $ 24 25 26 #11 NOWC Projection #12 #13 M21 c o E F G H I J K L M N P Q R s. A 1 Simple Financial Projection Template Assumptions OCC = Tax Rate = Unit Sales = Price per Unit Cost of Sales per unit = COSG as % of Sales = Annual SG&A= Incremental R&D = $ Enter Expenses as Positive Numbers 1 2 Year Sales Cost of Sales Gross Profit Selling, General & Admin. Research & Development $ Depreciation EBIT $ Income Tax $ NOPAT (Unlevered Net Income) $ Add Back Depreciation Subtract Capital Exp./Dispositions $ Subtract Increase in NOW $ Free Cash Flow $ Discount Factor FCF Present Value $ NPV $ IRR 1.00000 1.00000 1.00000 1.00000 1.00000 #NUM! $ - $ - $ $ - $ - Enter Required NOWC Here Enter the values you calculated in #11 Required NOW Change in NOW Cash Flow Impact of NOW $ $ $ Enter Required Capital Expenditure Here 0.2 0.2 0.2 Straight LineDepreciation Projection Capital Expenditure $ Annual Depr. Factors Depreciation Expense EOY Net Book Value $ #11 NOWC Projection $ - $ - $ - $ - $ - #12 #13 + C3 X fx 0% - K L M N O P Q R 09 A 1 Simple Financial Projection Template Assumptions OCC = Tax Rate = Unit Sales = Price per Unit= $ Cost of Sales per unit = $ COSG as % of Sales Annual SG&A= Incremental R&D Enter Expenses as Positive Numbers - $ Year Sales Cost of Sales Gross Profit Selling, General & Admin. Research & Development $ Depreciation EBIT $ Income Tax $ NOPAT (Unlevered Net Income) $ Add Back Depreciation Subtract Capital Exp./Dispositions $ Subtract Increase in NOW $ Free Cash Flow $ Discount Factor FCF Present Value $ NPV $ IRR 1 1.00000 1.00000 - 1.00000 - .00000 - $ 1.00000 - $ $ $ $ #NUM! - $ - $ - $ - $ - $ . Enter Required NOWC Here Enter the values you calculated in #11 Required NOW $ Change in NOWC $ Cash Flow Impact of NOW $ - Enter Required Capital Expenditure Here Straight LineDepreciation Projection Capital Expenditure $ Annual Depr. Factors Depreciation Expense EOY Net Book Value $ - $ - - $ $ - $ . #11 NOWC Projection #12 #13 +