Question

 

1. For a new product, sales volume in the first year is estimated to be 50,000 units and is projected to grow at a rate of 7% per year. The selling price is $100 and will increase by $10 each year. Per-unit variable costs are $22 and annual fixed costs are $1,000,000. Per-unit costs are expected to increase 4% per year. Fixed costs are expected to increase 10% per year. Develop a spreadsheet model to predict the net present value of profit over a three-year period, assuming a 4% discount rate.

Note: Please include Excel worksheet with all the details with your answer.

2. Suppose that a firm can produce a part it uses for $500 per unit, with a fixed cost of $12,000. The company has been offered a contract from a supplier that allows it to purchase the part at a cost of $510 per unit, which includes transportation. The key outputs in the model are the difference in these costs and the decision that results in the lower cost. Assume that the production volume is uncertain. Suppose the manufacturer has enough data and information to estimate that the production volume will be normally distributed with a mean of 1,200 and a standard deviation of 85. Use a 100-trial Monte Carlo simulation to find the average cost difference and percent of trials that result in manufacturing or outsourcing as the best decision. (Your data table should show both the cost difference and decision for each trial.)

Note: Please include Excel worksheet with all the details with your answer.

3. Find the feasible region for the following system of inequalities and show it on a graph.

       x + y > 3

     3x + 2y ≤ 12      

Answer 1

• Net present Value of Profit
  Particulars	Year 1	Year 2	Year 3
  No. of Units	50000	53500	57245
  Selling Price / unit	$100	$110	$120
  Less: Variable Cost / unit	$22	$22.88	$23.80
  Contribution / Unit	$78	$87	$96
  Contribution	$3,900,000	$4,660,920	$5,507,244
  Fixed Cost	$1,000,000	$1,100,000	$1,210,000
  Net Profit	$2,900,000	$3,560,920	$4,297,244
  Present value factor= 1/(1+0.04)^n	0.961538462	0.924556213	0.888996359
  Net present value of Profit	$2,788,461.54	$3,292,270.71	$3,820,234.07
  Total Present Value of Profit	$9,900,966.32

• 

  take x+y=2

  points are

  x	y
  0	2
  2	0

  .

  .

  take 3x+2y=12

  x	y
  4	0
  0	6

  

  .

  .

  combine both graph