Spreadsheet Question Worksheet

Spreadsheet: Popcorn

Warning: Remember to restore the original data in every cell changed in one problem before going to the next question!

Set A - Scenario

Your math class is in charge of the popcorn station at all ball games this year.  Last year's class developed a spreadsheet called POPCORN to use to calculate the best profit.  You can enter the amount of unpopped corn, edit the number of cups of popped popcorn per different size bags, and edit the costs for materials as well as the sales price.  Note that the spreadsheet is designed to calculate simultaneously costs and profits for small bags and for large bags or fill large bags at one time but not both.  Use the spreadsheet POPCORN to answer the following questions:

Part 1

1.   If you have 40 cups of unpopped corn and small bags hold 5 cups of popped corn, how many small bags of popcorn can you produce?

2.   How many large bags holding 11 cups of popped popcorn can you fill with 20 cups of unpopped corn?

3.   If you pop 55 cups of unpopped corn to put in 9-cup large bags, what will your profit be if you sell each bag for $1.30?  Remember you must account for the cost of the corn ($.33/cup) and the bag ($ .05 each)

4.   The popcorn makers at the Tuesday night basketball game made a profit of $129.17 selling small bags of popcorn for $1.00. Which of the following would have resulted in this profit?

a.   Small bags hold 5 cups, empty bag cost 8 cents, unpopped corn cost 54 cents per cup, you popped 22.5 cups of unpopped corn.

b.   Small bags hold 7 cups, empty bag cost 6 cents, unpopped corn cost 54 cents a cup, you popped 33.5 cups of unpopped corn.

5.   The end of the ball season is near and you need to make as close to $301 as possible from the last 50 cups of unpopped corn.  If you only have 9-cup large bags left, what new sales price for the large bags of popped popcorn will make this profit? (Note: The large bags each cost 10 cents and the unpopped com cost 54 cents a cup.)

Part 2

1.   If you have a container of 25 cups of unpopped corn, how many small bags of popcorn can you produce if each small bag holds 4 cups of popped popcorn?

2.   How many large bags (each holding 11 cups of popped. popcorn) can you fill with 10 cups unpopped popcorn?

3.   If unpopped corn costs $.22 per cup, you have 7-cup large bags that cost you $.05 each, and your sales price is $1.50, how many cups of corn did you pop to make a profit of $88.38?

4.   The popcorn makers at the Friday night football game made a profit of $243.13 selling small bags of popcorn for $1.00. Which of the following would have resulted in this profit?

a.   Small bags hold 4 cups, empty small bags cost $.02 each, unpopped corn costs $.41 a cup, you popped 30.5 cups of corn

b.   Small bags hold 6 cups, empty small bags cost $.04 each, unpopped corn costs $.28 a cup, you popped 25 cups of corn.

5.   You have run out of small 7-cup bags and need to reorder.  The supply company has just increased the price of the 7-cup bags by a penny, to $.07 each.  What profit will you make when you pop 40 cups of unpopped corn (that cost you $.34/cup) to sell at $1.10 per bag using the $.07 bags?  If you can make a deal with the supply company to keep-the cost per bag at $.06, does your profit increase or decrease?  What would your profit be if you used the $.06 bags?

Set B - Scenario 

A local civic club has asked your school to operate the popcorn stand at the fall fair.  To make the, most profit, your class must decide the size of bags (by the number of cups of popped popcorn) and the sales price for each size of filled bag.  Use the spreadsheet called POPCORN to determine the profit or amount in each situation below:

l. Unpopped corn this week is $.34 per cup, the large bags are selling for $1.75 and the small bags for $1.15.

a.   What would be the profit from the 6-cup large bags (which cost you $.05 each) if you pop 27 cups of unpopped corn?

b.   If you use the 3-cup small bags that cost $.03 each and pop all 27 cups of unpopped corn, will you make a higher profit than with the large bags?  What will be the profit?

2.   The grocery store has a sale on bags of unpopped corn and large 5-cup paper bags.  The corn is $.23 per cup and the bags are $.04 each.  If you buy 46 cups of unpopped corn and sell the large bags of popped popcorn for $1.35, what profit will you make?  How many bags will you have sold?

3.   The cotton candy stand is next to the popcorn stand.  They are doing a great business by keeping all prices to $1.00. If you sell your 3-cup small bags (each bag costs $.02) of popcorn for $1.00, how many cups of unpopped corn (at a cost of $.13/cup) will you need to make a profit of $391.70?

4.   The cotton candy stand is still doing a booming business.  They have dropped their price to $.75. If you sell your 3-cup small bags (each bag costs $.02) of popcorn for $.75, what will your profit be if you pop 45 cups of unpopped corn that cost you $.15/cup?

5.   You only have 10-cup large bags left to use.  They cost you $.05 each.  You only have 23 cups of unpopped corn left.  The cost of the corn was $.16/cup. If you need to make at least $150 in profit, what should your sales price be if you want to keep it as low as possible?

Set C - Scenario

The local parks and recreation committee for your town has asked your school to operate the popcorn stand at the summer softball games. To make the most profit, your class must decide the size of bags (by the number of cups of popped popcorn) and the sales price for each size of filled bag.  Use the spreadsheet called POPCORN to determine the profit or amount in each situation below:

l.    Unpopped corn for this week is $.40 per cup and the small bags you have are 5-cup bags which cost $.02 each.  What will be the profit for the 5-cup bags if you pop 36 cups of unpopped corn and set the sales price at $1.00?

2.   If you send someone to the store to exchange the 5-cup bags in #1 above for 6-cup bags that cost $.03 each, will you increase your profit?  What will the profit be if that is the only change you make?

3.   The grounds keeper just discovered a squirrel in your popcorn supplies.  You had a full 160 cup container of unpopped corn.  The squirrel has totally ruined the first 50 cups, down to the first divider.  If you pop the remainder to put in 12-cup large bags to sell for $1.75 each, what will be your profit? (The unpopped corn cost $.45/cup and each bag cost $.04) How much profit would you have made if the squirrel had not discovered your supplies?

4.   For the last 2 weeks the ballgames have been rained out.  Your 94 cups of unpopped corn will be stale if you do not sell it all this week.  If the corn cost $.30/cup, the large bags cost $.02 each, and each bag holds 9 cups of popped popcorn, what should be your sales price to make a profit of $606.60?

5.   (Continuation of #4) Oops!  You just counted the bags.  You only have 160 of the large 9-cup bags.:

a.   What is the maximum number of cups of unpopped corn that you should pop to fill all of the bags you have while a teammate goes to buy more bags?  What profit will you make?

b.     The store was out of 9-cup bags so your teammate bought 8-cup bags for $.01 each.  What profit do you make using the 8-cup bags with the remainder of the unpopped corn?  Did you reach your goal of $606.60 in total profit?

POPCORN (spreadsheet) Answer Key

 Set A:

1.  288

2.  65

3.  $208.97

4.  b

5.  $1.85

Set B:

1.  225

2.  33

3.  13

4.  a

5.  $180.74. increase. $182.80

Set C:

1.  a. $254.38 b. yes. $341.86

2. $403.12. 331

3.  35

4.  $367.71

5.  $2.03

Set D:

1. 223.83

2.  no. $179.33

3.  $466.55. $67S..62

4.  $2.01

5.     a. 40. $288.86

     b. $363.50. yes