Biff Titan, a student at UWO, plans to open a hot dog stand inside UWOs football stadium during home games. There are eight (8) home games scheduled for the upcoming season. He must pay the UWO athletic department a vendors fee of $1,600 for the season. The stand and other equipment will cost him $2,850 for the season. Each hot dog will cost him $0.61. He has talked to friends at other universities who sell hot dogs at games. Based on their information and the athletic departments forecast that each game will sell out, he anticipates that he will sell approximately 1,075 hot dogs during each game.

1. What will he need to charge per hot dog to break even for the season? Answer to 2 decimal places. Include the excel table you used to answer this (copy and paste it into the word document) and note any formulas you created. (5 pts)

UWO has made the playoffs and will be adding one more game. Biff is going to sell premium hot dogs (instead of regular) that cost $0.96 each and sell them for $3.25 each. The vendor fee for that game only will be $450, and he will need to buy $225 in supplies for that game only.

2. How many hot dogs will he need to sell at that game to break even for that game? Round to 0 decimal places. Include the excel table you used to answer this (copy and paste it into the word document) and note any formulas you created. (5 pts)

Next season, hot dog costs are going up by 21% over last season, so Biff is going to charge $1.75 per regular hot dog. He will need to spend $1,950 for equipment and stand updates next season, not $2,850. In addition, he is busy, so he is hiring his friend, Dexter St. Jock, to help him at $58/game. Assume the number of games goes up by 1 from last season, hot dog sales increase 8% over the last season, and dont worry about playoffs

3. What vendor fee should Biff negotiate to make a profit of $6,250 next season? Answer to 0 decimal places. Include the excel table you used to answer this (copy and paste it into the word document) and note any formulas you created. (5 pts)

(over)

Questions 4-10

Busters is a grocery store chain in Hortonville. There are four locations in the city, denoted by their location. Lori, the owner of Busters, has compiled some data about sales of certain products and given you the data to perform some analysis on it. The file contains the following variables for each product:

• The year and month for the sales for the product
• The store the sales are for (denoted by their location in Hortonville)
• The group the product is in
• The product
• How many units were sold
• The sales revenue (Total, not per unit)

4. What is the total sales revenue of Cheerios for all locations together? Answer to 0 decimal places. Include the Pivot Table you used to find this answer. (5 pts)

5. What was the total sales (units) of the ice cream group in the east location? Answer to 0 decimal places. Include the Pivot Table you used to find this answer. (5 pts)

6. What was the sales revenue per unit of chocolate in the south location in 2011? Answer to 2 decimal places. Include the Pivot Table you used to find this answer. (5 pts)

7. In 2013, what percent of fruit group units were sold at each of the locations? Answer to 2 decimal places. Include the Pivot Table you used to find this answer. (5 pts)

8. Create a 2-D column pivot chart to show total sales revenue by month for each location. Month should be on the horizontal axis, and sales revenue on the vertical axis. Format the chart to include (5 pts):

• A chart title, centered above the chart
• A horizontal and vertical axis title
• A legend and data table on the bottom (below the chart).
• Only January, March, May, July, September and November (with each month being shown only once on the horizontal)

9. If you look at trends for the product groups (fruit cereal, etc) for all locations combined by year, which group(s) are showing increasing yearly sales revenue and which group(s) are showing decreasing yearly sales revenue. Be sure to include data tables, pivot charts, pivot tables or other evidence to support your answer.(5 points)

10. Which one of the locations do you think is doing best for 2013, and why do you think so? Be sure to include data tables, pivot charts, pivot tables or other evidence to support your answer. (5 points)