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 attaches Excel files showing a risk profile both as simple Excel and crystal ball formatting.