-1

I am trying to figure out what the optimal number of products I should make per day are, displaying the values in a chart and then using the chart to find the optimal number of products to make per day.

  • Cost of production: $4
  • Sold for: $12
  • Leftovers sold for $1

So the ideal profit for a product is $8, but it could be -$3 if it's left over at the end of the day.

The daily demand of sales has a mean of 150 and a standard deviation of 30.

I have been able to generate a list of random numbers using to generate a list of how many products: NORMINV(RAND(),mean,std_dev) but I don't know where to go from here to figure out the amount sold from the amount of products made that day.

Neeku
  • 3,646
  • 8
  • 33
  • 43
user3502333
  • 1
  • 4

1 Answers1

0

The number sold on a given day is min(# produced, daily demand).

ADDENDUM

The decision variable is a choice you make: "I will produce 150 each day", or "I will produce 145 each day". You told us in the problem statement that daily demand is a random outcome with a mean of 150 and a SD of 30. Let's say you go with producing 150, the mean of demand. Since it's the mean of a symmetric distribution, half the time you will sell everything you made and have no losses, but in most of those cases you actually could have sold more and made more money. You can't sell products you didn't make, so your profit is capped at selling 150 on those days. The other half of the time, you won't sell all 150 and will take a loss on the unsold items, reducing your profit a bit. The actual profit on any given day is a random variable, because it is determined by random demand.

Since profit is random, you can calculate your average earnings across many days based on the assumption that you produce 150. You can also average earnings based on the assumption that you produce 140 per day, or 160 per day, or any other number. It sounds like you've been asked to plot those average earnings versus how many you decided to produce, and choose a production level that results in the highest long-term average earnings.

pjs
  • 18,696
  • 4
  • 27
  • 56
  • Thank you for your help. I'm assuming that the number produced would be the random generated number, while the daily demand is the mean? – user3502333 Apr 06 '14 at 04:15
  • No, the number produced would be your decision variable and daily demand is a random number. – pjs Apr 06 '14 at 04:52
  • So the decision variable would be the number generated using `NORMINV(RAND(),mean,std_dev)` and for the random number I would just use the `RAND()` function and selection one from between 150-30 and 150+30? – user3502333 Apr 06 '14 at 07:25
  • No. See addendum above. This sounds like homework, so I'm trying to avoid spelling out absolutely everything, which is why I initially answered only the specific question posed in your original post. – pjs Apr 06 '14 at 16:32