Course ProjectM4 Working Ahead Cost-Volume-Profit (CVP) Analysis Review the Course Project Guidelines. In the last module, you completed your estimate of cash flows for your project. In this module, you will calculate the break-even point for the project and the expected financial returns. Open the Cost-Volume-Profit spreadsheet that you have been working in and calculate the break-even point.of your proposed project. (Access the CVP template if you have not yet begun this work) The project must use a 6.5% cost of capital and a tax rate of 25%. Complete IRR (Internal Rate of Return) and NPV (Net Present Value) for the project. Make sure you show your Excel formulas or provide calculations so your instructor can review your work. You should have also considered key points of any intangible benefits or costs associated with the project and begun supplementing your pro forma statement with sufficient background information to enable a prospective investor to decide if your company is worth investing in.

Using the PIH Balance Sheet, Cash Flow, and Income Statement to work on the two excel worksheets.

Income Statement Balance Sheet Cash Flow Financial Ratios Annual Income Statement (values in 000's) Period Ending Current Assets Cash and Cash Equivalents Short-Term Investments Get Quarterly Data Trend 12/31/2017 12/31/2016 12/311/2015 12/31/2014 $23,575 $43,045 $47,957 $53,639 $417 $25,473 $13,061 $6,285 $4,010 $196 $1,149 $2,198 Net Receivables Inventory Other Current Assets $0 $0 $0 Total Current Assets $0 $0 Long-Term Assets Long-Term Investments Fixed Assets $54,774 $28,200 $205 10,514 $237 $0 $0 $282 $3,354 $74,234 $20,486 Goodwill $0 $6 $1,430 $4,536 $82,083 Intangible Assets $0 $3,138 S1 $6,855 $4,809 $114,437 90,849 Other Assets Deferred Asset Charges Total Assets Current Llablitles Accounts Payable Short-Term Debt / Current Portion of Long- $9,805 S7 $7,294 5,146 $4,523 $0 $0 Term Debt Other Current Liabilities $0 $0 Total Current Liabilities Long-Term Debt II$8,578 $8,010 $5,876 $2,559 Other Liabilities $54,784 $34,417 $26,838 $19,797 Deferred Liability Charges Misc. Stocks Minority Interest Total Liabilities Stock Holders Equity Common Stocks Capital Surplus Retained Earnings Treasury Stock Other Equity Total Equity Total Liabilities & Equity $0 $0 $0 $0 $0 $24,320 $67,635 $44,492 $34,577 $6 $47,064 $46,809 $48,688 $47,631 $910 $6 $616 $605 $2,278 ($1,009) ($1,009) (1,731) $0 ($169) ($65) (62) ($1) $46,802 $46,357 $47,506 $49,914 $114,437 90,849 $82,083 $74,234 Income Statement Balance Sheet Cash Flow Financial Ratios Annual Income Statement (values in 000's) Period Ending Net Income Get Quarterly Data 12/31/2015 ($1,673) Trend 12/31/2017 12/31/2016 12/31/2014 $294 $11 $3,646 Cash Flows-Operating Activities Depreciation Net Income Adjustments Changes in Operating Activitles Accounts Receivable $445 $335 $422 $26 $435 $125 $3,376 $435 ($14,734) ($4,504) ($66) $1,356 Changes in Inventories Other Operating Activities Liabilities $4,809 $16,278 $7,527 $5,007 $2,311 $3,372 ($712) $4,157 $5,417 $584 6,858 $12,905 Net Cash Flow-Operating Cash Flows-Investing Activitles Capital Expenditures ($28) ($26,953)($6,766) (9,015) ($12,311) (S83) ($48) ($223) Investments Other Investing Activities Net Cash Flows-Investing Cash Flows-Financing Activities Sale and Purchase of Stock Net Borrowings Other Financing Activities Net Cash Flows-Financing Effect of Exchange Rate Net Cash Flow $0 $0 ($305) $0 ($26,981)($6,849) (9,368) (2,534) $224 ($1,195) ($1,731) $38,261 $0 $0 $0 ($1,731) $0 ($5,682) ($16) ($1,435) $0 ($4,912) $38,261 $0 $38,632 $0 ($19,470) Income Statement Balance Sheet Cash Flow Financial Ratios Annual Income Statement (values in 000's) Get Quarterly Data Period Ending Trend 12/31/2017 12/31/2016 12/311/2015 12/31/2014 $38,148 32,256 $27,130 $18,965 $8,141 $1168 7,392 $10,620 $10,824 Total Revenue I$26,980 24,864 Cost of Revenue $16,510 Gross Profit Operating Expenses Research and Development Sales, General and Admin. Non-Recurring Items Other Operating Items Operating Income Add'l income/expense items Earnings Before Interest and Tax Interest Expense Earnings Before Tax Income Tax $0 $5,095 $0 $0 $9,304 S6,918 $7,253 $372 $355 $5,703 $1,492 $119 ($2,336) $5,729 $0 $1,492 $0 $1,492 $119 ($2,336) $5,729 $0 $119 ($2,336) $5,729 ($663) $0 - $1,198 $108 $2,083 $0 $0 Minority Interest Equity Earnings/Loss Unconsolidated $0 Subsidiary Net Income-Cont. Operations Net Income $294 $294 $294 ($1,673) $3,646 ($1,673) $3,646 ($1,673) $3,146 $11 Net Income Applicable to Common Shareholders $11 Breakeven Analysis Property Insurance Holdings, Inc. Note: You may want to print this information to use as reference later To delete these instructions, click the border of this text box and then press the DELETE key Cost Description Variable Costs Cost of Goods Sold Fixed Costs (S) Variable Costs 0.0% 0.0% 0.0% 0.0% Using figures from your Profit and | Loss Projection, enter expected Raw Materials Direct Labor (Includes Payroll Taxes) | annual fixed and variable costs. Fixed costs are those that remain the same regardless of your sales volume. They are expressed in dollars. Rent, insurance and real estate taxes, for example, are usually fixed. Fixed Costs Salaries (includes payroll taxes) Repairs & maintenance Car, delivery and travel Accounting and legal Utilities Insurance Taxes (Real estate, etc.) Interes Depreciation Other (specify) Other (specify) Variable costs are those which change as your volume of business changes. They are expressed as a percent of sales. Inventory, raw materials and direct production labor for example, are usually variable costs. Under the variable expenses column, use whole numbers as a percentage not decimal numbers. For example, use 45%, rather than .45%. Principal portion of debt payment Owners draw For your business, each category of expense may either be fixed or variable, but not both. Total Fixed Costs Total Variable Costs Breakeven Sales level Note: You may want to print this information to use as reference later. To delete these instructions, click the border of this text box and then press the DELETE key The categories of expense shown above are just suggestions. Change the labels to reflect your own accounting systems and type of business. Breakeven is a "big picture" kind of tool; we recommend that you combine expense categories to stay within the 22 lines that this template allows. One of the best uses of breakeven analysis is to play with various scenarios. For instance, if you add another person to the payroll, how many extra sales dollars will be needed to recover the extra salary expense? If you borrow, how much will be needed to cover the increased principal and interest payments? Many owners, especially retailers, like to calculate a daily breakdown. This gives everyone a target to shoot at for the day 20xx 20xx 20xx 20xx 20xx 20xx Investment Outlays at Time Zero: Equipment or other investment (10,000) Operating Cash Flows over the Project's Life: Sales revenue Cost of Sales Gross Profit 2,999 2,999 2,999 Adverstiing Depreciation (equipment) Dues and subscriptions Insurance Payroll Taxes Other Taxes Other expenses Total expenses Oper. income before taxes (EBIT) Taxes on operating income Net Operating Profit After Taxes (NOPAT) Add back depreciation 2,991 2,901 02 2,991 2,901 $2,902 2,991 2,901 $2,902 Note: Change the tax rate in these cells to what is required 2,901 2,901 Operating cash flow Terminal hear Cash Flos: $2,9 02 $2,9 $2,9 02 After-tax salvage value of equipment that you sell Total termination cash flows S1 Net Cash Flow (Time line of cash flows) ($10,000) $2,902 $2,902 $2,902 $2,902 $2,903 Net Present Value IRR Create a formula using the NPV function as specified Create a formula using the IRR function as specified click the help file (? On top right of screen) and search for NPV Click the help file and search for IRR Income Statement Balance Sheet Cash Flow Financial Ratios Annual Income Statement (values in 000's) Period Ending Current Assets Cash and Cash Equivalents Short-Term Investments Get Quarterly Data Trend 12/31/2017 12/31/2016 12/311/2015 12/31/2014 $23,575 $43,045 $47,957 $53,639 $417 $25,473 $13,061 $6,285 $4,010 $196 $1,149 $2,198 Net Receivables Inventory Other Current Assets $0 $0 $0 Total Current Assets $0 $0 Long-Term Assets Long-Term Investments Fixed Assets $54,774 $28,200 $205 10,514 $237 $0 $0 $282 $3,354 $74,234 $20,486 Goodwill $0 $6 $1,430 $4,536 $82,083 Intangible Assets $0 $3,138 S1 $6,855 $4,809 $114,437 90,849 Other Assets Deferred Asset Charges Total Assets Current Llablitles Accounts Payable Short-Term Debt / Current Portion of Long- $9,805 S7 $7,294 5,146 $4,523 $0 $0 Term Debt Other Current Liabilities $0 $0 Total Current Liabilities Long-Term Debt II$8,578 $8,010 $5,876 $2,559 Other Liabilities $54,784 $34,417 $26,838 $19,797 Deferred Liability Charges Misc. Stocks Minority Interest Total Liabilities Stock Holders Equity Common Stocks Capital Surplus Retained Earnings Treasury Stock Other Equity Total Equity Total Liabilities & Equity $0 $0 $0 $0 $0 $24,320 $67,635 $44,492 $34,577 $6 $47,064 $46,809 $48,688 $47,631 $910 $6 $616 $605 $2,278 ($1,009) ($1,009) (1,731) $0 ($169) ($65) (62) ($1) $46,802 $46,357 $47,506 $49,914 $114,437 90,849 $82,083 $74,234 Income Statement Balance Sheet Cash Flow Financial Ratios Annual Income Statement (values in 000's) Period Ending Net Income Get Quarterly Data 12/31/2015 ($1,673) Trend 12/31/2017 12/31/2016 12/31/2014 $294 $11 $3,646 Cash Flows-Operating Activities Depreciation Net Income Adjustments Changes in Operating Activitles Accounts Receivable $445 $335 $422 $26 $435 $125 $3,376 $435 ($14,734) ($4,504) ($66) $1,356 Changes in Inventories Other Operating Activities Liabilities $4,809 $16,278 $7,527 $5,007 $2,311 $3,372 ($712) $4,157 $5,417 $584 6,858 $12,905 Net Cash Flow-Operating Cash Flows-Investing Activitles Capital Expenditures ($28) ($26,953)($6,766) (9,015) ($12,311) (S83) ($48) ($223) Investments Other Investing Activities Net Cash Flows-Investing Cash Flows-Financing Activities Sale and Purchase of Stock Net Borrowings Other Financing Activities Net Cash Flows-Financing Effect of Exchange Rate Net Cash Flow $0 $0 ($305) $0 ($26,981)($6,849) (9,368) (2,534) $224 ($1,195) ($1,731) $38,261 $0 $0 $0 ($1,731) $0 ($5,682) ($16) ($1,435) $0 ($4,912) $38,261 $0 $38,632 $0 ($19,470) Income Statement Balance Sheet Cash Flow Financial Ratios Annual Income Statement (values in 000's) Get Quarterly Data Period Ending Trend 12/31/2017 12/31/2016 12/311/2015 12/31/2014 $38,148 32,256 $27,130 $18,965 $8,141 $1168 7,392 $10,620 $10,824 Total Revenue I$26,980 24,864 Cost of Revenue $16,510 Gross Profit Operating Expenses Research and Development Sales, General and Admin. Non-Recurring Items Other Operating Items Operating Income Add'l income/expense items Earnings Before Interest and Tax Interest Expense Earnings Before Tax Income Tax $0 $5,095 $0 $0 $9,304 S6,918 $7,253 $372 $355 $5,703 $1,492 $119 ($2,336) $5,729 $0 $1,492 $0 $1,492 $119 ($2,336) $5,729 $0 $119 ($2,336) $5,729 ($663) $0 - $1,198 $108 $2,083 $0 $0 Minority Interest Equity Earnings/Loss Unconsolidated $0 Subsidiary Net Income-Cont. Operations Net Income $294 $294 $294 ($1,673) $3,646 ($1,673) $3,646 ($1,673) $3,146 $11 Net Income Applicable to Common Shareholders $11 Breakeven Analysis Property Insurance Holdings, Inc. Note: You may want to print this information to use as reference later To delete these instructions, click the border of this text box and then press the DELETE key Cost Description Variable Costs Cost of Goods Sold Fixed Costs (S) Variable Costs 0.0% 0.0% 0.0% 0.0% Using figures from your Profit and | Loss Projection, enter expected Raw Materials Direct Labor (Includes Payroll Taxes) | annual fixed and variable costs. Fixed costs are those that remain the same regardless of your sales volume. They are expressed in dollars. Rent, insurance and real estate taxes, for example, are usually fixed. Fixed Costs Salaries (includes payroll taxes) Repairs & maintenance Car, delivery and travel Accounting and legal Utilities Insurance Taxes (Real estate, etc.) Interes Depreciation Other (specify) Other (specify) Variable costs are those which change as your volume of business changes. They are expressed as a percent of sales. Inventory, raw materials and direct production labor for example, are usually variable costs. Under the variable expenses column, use whole numbers as a percentage not decimal numbers. For example, use 45%, rather than .45%. Principal portion of debt payment Owners draw For your business, each category of expense may either be fixed or variable, but not both. Total Fixed Costs Total Variable Costs Breakeven Sales level Note: You may want to print this information to use as reference later. To delete these instructions, click the border of this text box and then press the DELETE key The categories of expense shown above are just suggestions. Change the labels to reflect your own accounting systems and type of business. Breakeven is a "big picture" kind of tool; we recommend that you combine expense categories to stay within the 22 lines that this template allows. One of the best uses of breakeven analysis is to play with various scenarios. For instance, if you add another person to the payroll, how many extra sales dollars will be needed to recover the extra salary expense? If you borrow, how much will be needed to cover the increased principal and interest payments? Many owners, especially retailers, like to calculate a daily breakdown. This gives everyone a target to shoot at for the day 20xx 20xx 20xx 20xx 20xx 20xx Investment Outlays at Time Zero: Equipment or other investment (10,000) Operating Cash Flows over the Project's Life: Sales revenue Cost of Sales Gross Profit 2,999 2,999 2,999 Adverstiing Depreciation (equipment) Dues and subscriptions Insurance Payroll Taxes Other Taxes Other expenses Total expenses Oper. income before taxes (EBIT) Taxes on operating income Net Operating Profit After Taxes (NOPAT) Add back depreciation 2,991 2,901 02 2,991 2,901 $2,902 2,991 2,901 $2,902 Note: Change the tax rate in these cells to what is required 2,901 2,901 Operating cash flow Terminal hear Cash Flos: $2,9 02 $2,9 $2,9 02 After-tax salvage value of equipment that you sell Total termination cash flows S1 Net Cash Flow (Time line of cash flows) ($10,000) $2,902 $2,902 $2,902 $2,902 $2,903 Net Present Value IRR Create a formula using the NPV function as specified Create a formula using the IRR function as specified click the help file (? On top right of screen) and search for NPV Click the help file and search for IRR