UCI Investment Excel Spreadsheet

A hospital corporation is planning to expand the surgical unit in one of their facilities. The expansion will take an initial investment of $12K. The hospital board wishes to know something about the value of the project, and risk associated with the project. One important criterion has to do with the NPV of the project; if there is less than a 10% risk of having a negative net present value for the investment, they will consider the expansion. The expansion will, of course, have revenues and expenses associated with it.

The following are the assumptions about income and expenses:

(a)Quarter 1 Sales:A normally distributed variable with a mean of $175,000

And a standard deviation of $5,000.

(b)Cost of Goods Sold Ratio:Rate:Triangular distribution with MIN=.6, MAX=.9,

and Most Likely value = .75

(c)General and Administrative Expenses:

Salaries: $14,000 for Quarters 1 and 2

$15,500 for Quarters 3 and 4

The following are %’s of Gross Profit:

Commissions:10%

Utilities: 5%

Telephone:A variable with a skewed left distribution with a minimum value of 3%, a most likely value of 6%, and a maximum value of 8%

Travel:A variable with a skewed right distribution with a minimum value of 2%, a most likely value of 3%, and a maximum value of 7%

Entertainment/Promotion:A uniformly distributed variable between 5% and10%

The following is a % of Salaries:

Payroll Taxes: 7%

(d) Quarterly Sales Growth Factor: 10% per quarter

(e)Taxes:36% tax rate on net profit; losses should be carried forward to the next quarter

(f)Initial investment:$12,000

(g)Discount rate (cost of capital):8%

Please review some of the distribution options that we have not yet discussed and see if any fit the assumptions above, and then develop a simulation that you believe will enable the board to make a sound decision.