Optimal order quantity

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.

Download the start worksheet, and add your code.

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 one unit per year. Storage, insurance, like that. Must be a number that is not negative.
  • Whether Smurfs are present. After trimming spaces, the first character should be Y or N, though case doesn't matter. So "Y", " n", " No, dude", "Yeah", and " Negatory, Nellie" are all OK. "Don't see none, Wilbur", " Seen them", "Banjo Bill wiped 'em out" are invalid.

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

You don't have to use Subs for this. Though you can, if you want.

Upload your workbook.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Referenced in: