This assignment is based on an exercise done in a workshop I attended in 1994. I no longer remember who gave the workshop or who created the original material for the workshop, but I found the exercise to be a powerful demonstration of the power of spreadsheets.

This assignment is a practical assignment to show the basics of spreadsheets and to show that spreadsheets are good for asking "What If" questions. We can change a number and the spreadsheet will automatically recalculate everything that is based on that number. For this assignment, imagine that you are in charge of feeding fish in an aquarium. You have a daily food budget of $425. Based on the cost of feeding each fish and the number of fish you have, you need to make sure you are within your food budget. At the end, you can ask "what if" questions to see if you can afford changes in prices and changes in the numbers of fish.

The first handout is the basic unfilled spreadsheet. This contains the names of the fish, the numbers of the fish, and the numbers associated with feeding each fish. You should make the headings stand out by making them bold, make the cells the right size for the data, make all the money figures into currency format (so the dollar signs are automatically added), and input the appropriate calculations for each of the calculated cells. You might need help with the mathematical formulas, but try to figure out how to enter the calculations yourself.

Look at the filled spreadsheet to see how it should look when you are done. Be careful not to merely enter the numbers. Any number that does not appear in the unfilled spreadsheet should be calculated. Check the hints for ideas on how to create the formulas.

When you are finished, answer the following "what if" questions. Write down your answers (yes or no) and what you did to get the answers.

1. The cost of food for the electric eels has risen to $.25 per lb. Will you be able to feed the 5 electric eels currently housed without going over budget.

2. Your crew has recently captured 2 dolphins in need of medical care. You have sufficient funds to cover their medical expenses, but you're concerned about the food budget. Can you keep both of these dolphins or should you send one or both to another aquarium?

3. The San Francisco Aquarium wishes to sell some of its numerous
Lion Fish. How many can you afford to acquire?

- The total cost per day to feed the fish (column E) is the number of fish (column B) times the amount of food eaten per fish per day (column C) times the cost of food per pound (column D). That is, column E is column B times column C times column D. Use the asterisk (*) for times. Set up the formula for the Dolphins and use fill down.
- The total cost per day for all the fish (cell B13) is the sum of the costs for each individual fish (column E). Use the SUM function to add up column E.
- The amount Under/Over Budget is the food budget (B14) minus the total cost (B13). If that number is positive, you are within budget. If that number is negative, you have exceeded your budget.
- The per week costs (C13-C15) are the costs per day (B13-B15) times 7. Use the asterisk (*) for multiplication.
- The per month costs (D13-D15) are the costs per week times 4.5.
- The per year costs (E13-E15) are the costs per month times 12.

This page was prepared by Dr. David M. Marcovitz.

Last Updated: April 10, 2002