The Peppy Paladin Caf is an experiential learning lab at a state university in the Midwest. After

The Peppy Paladin Café is an experiential learning lab at a state university in the Midwest. After the food service operator closed a dining location in the business building, students developed the concept to provide limited food and beverage options for students, faculty, and staff. Having just completed its first semester of operations, the café is operated by student volunteers who receive internship credit for running the café.

The café staff collected sales statistics from the most recent semester of operations

(January-April), and it is now time to prepare the sales budget for next semester.

Required

a. Use the AVERAGE formula in Excel to determine the average number of sodas and the average number of cups of coffee sold in a day.

b. Use the STDEV.P formula in Excel to determine the variation in daily sales of sodas and cups of coffee. Which product has the highest standard deviation? What does that imply?

c. Plot the daily sales of each beverage. What trends do you identify? How does this information help you estimate daily sales better than average sales for the entire period?

d. Calculate the average number of sodas and cups of coffee sold each weekday. Does there appear to be a pattern of sales depending on the day of the week? Knowing what you know about class schedules, how might you explain your findings? (Hint: Use the AVERAGEIF or the COUNTIF and AVERAGE formulas to determine the total sales by day of the week.)

e. Develop a sales estimation function for sodas and coffee, based on the day of the week, using the SLOPE and INTERCEPT formulas. Use the RSQ formula to determine how much of the sales variation is explained by the day of the week.

f. Develop a sales estimation function for sodas and coffee, based on the daily high temperature, using the SLOPE and INTERCEPT formulas. Use the RSQ formula to determine how much of the sales variation is explained by the maximum daily temperature.

g. Which of the two sales estimation functions do you think would be the better one to use in preparing the sales budget? Why?

h. Sodas and coffee aren't as perishable as are certain food items like sandwiches. Discuss how incorrect estimates of sales of perishable items can affect a business.

The Excel data files for answering this problem can be found in WileyPLUS.