Proforma Income Statement:

PLEASE ONLY ANSWER THIS QUESTION WITH EXCEL CHART WITH FORMULAS, DO NOT ANSWER WITHOUT PROVIDING EXCEL CHART FORMAT WITH FORMULAS

The purpose of this assignment is to determine the desirability of a project Johnson and Johnson is considering. You must do this project in Excel and submit spreadsheet. You must use formulas in Excel to construct the proforma income statement so you can change some of the variables you used to see how robust the investment is.

Suppose Johnson & Johnson (ticker symbol - jnj) has decided to introduce a new heart stent, the Heart Flow. Before they launch the Heart Flow, they conducted an analysis to see if the Heart Flow would be a desirable investment. The company estimated that it would sell 1,000,000 Heart Flows per year at a price of $75,000 for the next six years. After the first year of sales, the quantity sold will increase by 2% per year for the remaining life of the project. The initial capital outlay is determined to be $12 billion and a $1.0 billion outlay in net working capital (NWC) would also be required. Assume that there is a one-time investment in NWC and that this will be recovered at the end of the project. Assume that the equipment used will be depreciated using the MACRS 7-year schedule and that the equipment has a salvage value of zero. At the end of year 6, the equipment will be sold for 120% of its book value. Also, assume that that the tax rate is 25%.

Using information from Johnson & Johnsons financial statements (you may want to use Morningstar.com or some other online site) estimate the operating cash flows from the project. Make any simplifying assumptions that are necessary to produce the estimate. Morningstar has changed their website. They used to report COGS and SGA percentages, they dont seem to now. If you find them, let me know. Use a fixed 6% as the SGA percentage. For COGS%, click the tab for Operating Performance, and compute COGS% = 1 Gross Margin %.

Part 1 - Here, we simply want to work through a proforma income statement to determine the cash flows from the project. Other assumptions necessary for completing the proforma income statement can be found by looking at some of the historical averages of values in Johnson & Johnsons financial statements.