For this project, let's say that your class wants to raise money to buy a new aquarium for the classroom. The class has decided to have a candy sale.
The cost of the aquarium and all the parts (pump, filter, gravel, etc.) will be about $150. This includes the price of the tropical fish that will live in the aquarium.
The class wants the price of the candy to be no more than $1. You don't feel like anything higher priced will sell.
You found you can buy a box of 30 assorted chocolate bars for $24.
Now you have three questions:
- If a box of 30 costs $24, how much is the cost per bar?
- How much profit will the class make off each bar?
- How many candy bars must be sold to buy the aquarium?
If you were figuring out the first question on paper, it might look something like this:
 |
Now we are going to use Numbers to solve this same this same problem. Follow the steps and make this same spreadsheet yourself:
STEP ONE:
Put a title in the upper left corner (cell A1), then enter labels for
- Price per box
- Number of bars
- Price per bar
Then, enter numbers in Column B for Price per box (24) and Number of bars (30).
Your spreadsheet should look something like this:
I have made labels in Column A, and I will put my numbers in Column B.
STEP TWO:
Enter 24 for the Price per Box, and 30 for the Number of Bars.
Next, enter a formula to calculate the Price per Bar. Simply divide the Price per Box (B3) by the Number of Bars (B4).
The result of the formula in B5 should be .8 -- or 80 cents.
Next, format cells B3 and B5 to currency format. Then, align your labels to the right.
STEP THREE:
The class has decided to sell each candy bar for $1. In the spreadsheet, put in some labels for Sales Price per Bar, Cost per Bar, and Profit per Bar.
- The Sales Price is how much we sell it for.
- The Cost is how much it costs us.
- The Profit is the what we make after we subtract our cost from the sales price.
We calculated the Cost per Bar earlier, in cell B5. (We called it Price per Bar then. Now that we are re-selling it, it becomes a cost to us.) Since we already calculated the cost, just transfer the result by entering =B5 in cell B8. That will transfer the value of B5 to B8 without having to re-enter any formula.
 THINK ABOUT THISWhy is it better to transfer the value from B5 to B8, instead of just typing in .80? |
Subtract the Cost per Bar from the Sales Price to calculate the Profit per Bar.
Finally, change the cells to currency format. Make column A wider, if necessary, and right-align the labels.
STEP FOUR:
On your own, you are going to determine how many bars your class needs to sell to raise $150. To do this on paper, you would write:
125 ÷ .20 = ____
Can you make your spreadsheet figure this out for you?
