Create a Microsoft Excel spreadsheet that calculates pro forma free cash flows and the net present value. The spreadsheet should include all of the inputs necessary to calculate free cash flows and NPV based on the information provided.

Further, the spreadsheet should be formula-based and appropriately use cell referencing. The spreadsheet should also be dynamic in the sense that, if any of the inputs are changed, all of the cash flow and NPV calculations will automatically update.

Situation:

Preliminary discussions between executives at Forage and Scoot-Ease suggested that the assets of Scoot-Ease could be purchased for $1,350,000. Amiya was tasked with determining whether an acquisition at this price would create value for Forage. Fortunately for Amiya, these types of decisions were supported by many functions at Forage. Amiya received the following information:

In its most recent full fiscal year, Scoot-Ease generated $550,000 in revenue. Forages marketing department expected this revenue to grow at 20%, year-over-year, for the foreseeable future.

The cost accounting group provided Amiya with the following cost estimates: Fixed operating expenses of $95,000 in the first year that will grow at 10% in future years. Variable operating expenses for cost of goods sold, SG&A, and other of 12%, 25%, and 2 7% of sales, respectively. Last, they e-mailed Amiya the depreciation schedule that should be used for the assets (see Table 1).

The economics department provided an estimate that the assets purchased would be productive for Forage for about five years. At that time, all of the assets would be sold at an estimated sales price of $500,000.

The tax group provided Amiya with a projected marginal tax rate of 19% to use for profits and any capital gain or loss on the sale of assets. They also reminded Amiya that individual projects could have negative taxes if the taxable income were to be negative because these tax losses would offset other taxable income from other divisions in Forage.

Amiya also had information from the finance group. A colleague in the treasury department informed Amiya that it was company practice to hold net working capital (NWC) levels equal to 15% of expected sales. Forage was a fast-growing information technology company, with many successful investments but also many investments that failed. Because of this, Forage typically used a discount rate of 17% when considering new investments. Fortunately for Amiya, a colleague sent her a template from a similar previous analysis, which would provide a starting point for Amiya (See Table 2).

Depreciation table given:

Table Template that needs to be filled:

Table 1. Depreciation Schedule to Use for Purchased Assets Table 2. Template for Net Present Value (NPV) Analysis Table 1. Depreciation Schedule to Use for Purchased Assets Table 2. Template for Net Present Value (NPV) Analysis