Optimal order quantity

Tags
Challenge
No

Your company buys Stuff from wholesalers, and sells it retail. Work out the optimal order quantity. That is, how many Stuffs you should order each time to minimize cost.

Here's how the worksheet starts:

Start

Users type data into four cells. Validate them.

  • Sales (units per year). Must be a number that is one or greater.
  • Ordering cost ($). The cost of placing one order. Must be a number that is not negative.
  • Carrying cost ($/unit/year). Cost of carrying a unit per year. Storage, insurance, like that. Must be a number that is greater than zero.
  • Whether Smurfs are present. After trimming spaces, should be Y or N, though case doesn't matter.

Show appropriate error messages. Here's a sampling:

Errors

All applicable error messages should show, in red, next to the cells they apply to. Use the flag pattern.

Don't show output if there are any errors. As in the screenshot above.

Here are some more errors.

Errors

If all of the data is OK, compute the optimal order quantity, and show it. The formula from Wikipedia is:

Formula

Here's what the variables in the formula are:

Variables in formula

If there are Smurfs, add one to the order quantity. They always steal just one.

Here's some output.

Output

Another example:

More output

Upload your workbook. The usual coding standards apply.

Where referenced