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.

Here's how the worksheet starts:

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:

**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.

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

Here's what the variables in the formula are:

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

Here's some output.

Another example:

Upload your workbook. The usual coding standards apply.

Clown clearance

The sleepy town of Fall's End, Montana, has been invaded by evil aliens! They have turned the townsfolk into lava lamps. There are roughly forty aliens, though the exact number is not known.

Poor research led the aliens to disguise themselves as clowns. They thought they would blend in with the locals.

Marcella has discovered that felines are the only creatures unaffected by the aliens' transmogrifiers. Write an Excel worksheet with some VBA to help her figure out how many leopards, lions, and tigers to rent.

Here's what your solution should look like to start with:

Validate the three inputs. They must be numeric, and cannot be less than zero. In addition, leopards cannot be more than six, lions cannot be more than five, and tigers cannot be more than three.

Here are some error messages:

If there are any input errors, show **all** of the error messages at once. Hint: use the flag pattern.

Clear all output and error messages each time the program runs.

Compute and output the cost, and the number of clowns eaten. Each leopard $4,000, and will eat two clowns. Each lion costs $8,500, and will eat four clowns. Each tiger costs $12,800, and will eat seven clowns.

Hint: use singles for all numbers. It's just easier that way.

Here is some output:

Marcella has $90,000 in her wallet. If the total cost is more than $90,000, show a message along with the output, like this:

The usual coding standards apply.

Upload your worksheet.

What's the cricket score?

Cricket is a popular sport in British Commonwealth countries, like Australia, and India. Each match has two sides. There are four components to a side's score:

- Boundaries (sixes): 6 points each
- Boundaries (fours): 4 points each
- Singles: 1 point each
- Sundries: 1 point each

You can read more about cricket scoring, if you want to know more.

Write a VBA program to compute a score given the information above. When the worksheet opens:

The user enters four values, clicks Run, and the program computes the score. For example:

Here's some more sample output.

All input values must be numeric and zero or more. If there are errors, show messages next to each erroneous cell, in red. Show all applicable error messages at once. For example:

Be sure to clear old error messages and output when the program runs.

Do not start from scratch. Download this worksheet, and add the code you need.

The usual coding standards apply.

Upload your solution.

Goat milk

Estimate the amount of goat milk you'll get per day, based on the number of goats of different breeds you have, and whether you love them or not.

The worksheet starts like this:

For each breed, the user enters the number of goats (a number zero or more), and whether they love the goats. They can can enter y, yes, n, or no. Case and extra spaces shouldn't matter.

If they enter invalid data, they see all applicable error messages, like this:

If the data's OK, compute the estimated milk production for that scenario. For example:

Here production data, in gallons per day per goat:

Breed | Loved | Unloved |
---|---|---|

Alpine | 2.2 | 1.7 |

Nubian | 2.8 | 2.1 |

Saanen | 1.9 | 1.5 |

Here's some more output:

Be sure to clear errors and output at the start of each run.

Upload your workbook. The usual standard apply.

Museum ticket sales

Cthulhu's Goat Museum is a must-see if you visit Goatville. Write a program to compute ticket prices for a group of visitors. (Sorry, only goats are allowed. Humans are too crazy.)

It starts like this:

The user types in three things for each visiting group:

- Number of adult goats. Number be numeric, and cannot be negative.
- Number of goatlets (AKA kids). Number be numeric, and cannot be negative.
- Whether the group has a discount coupon. Must be Y or N. Upper- or lowercase is OK. Excess spaces are OK.
- No unaccompanied goatlets, that is, if adults is zero and goatlets is positive, then that's a Bad Thing.

Here's an error display:

If there are errors, the amount due field should be empty.

Here's another error display:

If there are no errors, show the amount due.

- Adults: $10 each
- Goatlets: $5 each
- Coupon discount: 10%

For example:

Another:

Upload your workbook. The usual standards apply.

Goat fuel

Cthulhu sponsors goat races every month. The course varies in length, and whether there are hurdles or not. Before each race, Cthulhu feeds the goats, so they have the energy they'll need. Write a program to work out the number of kilocalories needed for a race.

You can download a start worksheet. It looks like this:

The user types:

- The number of goats (must be a number more than zero)
- The course length (must be a number at least 10)
- Whether there are hurdles (must be y or n, allow upper- or lowercase, and extra spaces)

Validate the data, showing **all** appropriate error messages. For example:

Make sure error messages and output are cleared each time the program runs.

Some sample output:

Kilocalories is goats times length times 100 divided by 1,000. If there are hurdles, multiply kilocalories times two.

If more than 100 kilocalories are needed, Cthulhu wants to bring in Euphonites, his goat nutritionist. Show a message to that effect:

Upload your workbook. The usual coding standards apply.

Dance party pricing

Goats loooove to dance! Write a program to price a dance party for goats. You can download a starting workbook. It looks like this:

Users enter:

- Number of young goats (number, 0 or more)
- Number of old goats (number, 0 or more)
- Music style (D, R, or C, for dub step, rock, or country)

Your program should validate the fields. Let the user type upper- or lowercase for music style, with extra spaces. If there are error messages, show all that apply, like this:

Make sure that old error messages and output is erased. So if there are errors, cell B9 should be blank.

The total price depends on the price per goat, and a setup fee. Each young goat costs $30. Each old goat costs $50. The setup fee depends on the type of music. $400 for dub step, $200 for rock, and $550 for country.

Upload your solution. The usual coding standards apply.

Goat shirts

Paula Poundstone is selling shirts for goats on her website. The shirts are made of a remarkably soft tri-poly blend that goats adore. Write a VBA program to compute the cost of an order. The worksheet starts like this (you can download) it):

Customers enter the number of small, medium, and large goat shirts they want. The values must be numeric, and greater than zero. If they aren't, show error messages, like this:

Of course, once an error is fixed, its message should go away. Only show the output if all input is valid.

Shipping can be done by Bonnie, or Toni. Bonnie is cheaper, but her attention wanders. Toni is more likely to get the job done quickly.

The user should enter B or T in the shipping field. Case and extra spaces don't matter. If the user enters something else, show an error message, as shown.

If all the data is OK, compute the total cost. Here are some examples.

Large shirts sell for $28 each, medium for $25, and small for $22. Shipping is $2 per shirt for Bonnie, and $3 per shirt for Toni.

Upload your solution. The usual coding standards apply.

Grocery orders

Pat has started a grocery delivery service for goats. You can download the starting worksheet.

Your worksheet should look like this at first:

The user fills in the orange cells, and hits Run. Your program should show error messages, like this:

Here's what it looks like after valid input:

You don't have to round the outputs.

Here's some more output:

Product prices are as given. Delivery costs $12 for the cart, $35 for the drone, and $288 for teleportation. If teleportation is selected, show an extra message in magenta, as shown above.

Input conditions:

- Name is required.
- Values for spinach, cauliflower, and lettuce are required. They must be numeric. They must be zero or more.
- Delivery mode is required. It must be C, D, or T. Lowercase is OK. Extra spaces before or after the letter are OK.

*All* applicable errors must be shown after the Run button is pressed.

When the program runs, clear existing errors and output. For example, if, after seeing the previous example, Larry changed the lettuce amount to something invalid, all of the output would be cleared:

Upload your solution. The usual standards apply.

Medal scores

Winners in Goatz Sportz earn medals: gold, silver, and bronze. Write a program to find the average medal scores in a data set.

Here's the worksheet to start with. You can download it.

Each medal count should be numeric, and zero or more. Your validation code should use case-wise deletion, that is, reject records where one or more values are invalid. Check the lesson Filtering: invalid data for more.

After running the program, the output should be something like:

A medal score is computed for each goat. The goat gets three points for a gold, two for a silver, and one for a bronze. The total score for valid records is output in G11. The average is in G12.

Use a `Range`

object, and write the code so that it will work with no changes when new goat data is added. The usual coding standards apply.

Upload your work.