Explore BrainMass
Share

# Excel Financial Model / Mean Values

Assume:
Gross profit = net sales - cost of sales
Net operating profit = gross profit - administrative expenses
Net income before taxes = net operating profit - interest expense
Net income = net income before taxes - taxes

Net sales in the first year (in hundreds of thousands of dollars) is uniformly distributed between 6 and 12. The net sales are expected to increase each year by 5% plus or minus 1%. Cost of sales (in hundreds of thousands of dollars) is normally distributed, with a mean of 5.6 and a standard deviation of 0.2, and is expected to increase an average of 5% per year. Selling expenses has a fixed component that is uniform between \$75,000 and \$110,000. The variable component is estimated to be 7% of net sales. Administrative expenses are normal, with a mean of \$50,000 and a standard deviation of \$4,000, and will increase 3% per year on average. Interest expenses are \$10,000 in year 1 and will decline by \$1000 each year. The company is taxed at a 50% rate. Develop a risk profile of net income using Excel. (Model check: net income should be \$110,000 for year 1 and \$144,360.62 for year 5)

#### Solution Summary

Solution attaches Excel files showing a risk profile both as simple Excel and crystal ball formatting.

\$2.19