Cost and Pricing: The Case of SweetTooth Candies Business/Analytical Learning Objectives:

Determine variable(s) of interest, identify and organize relevant information for a problem with multiple components

Recognize and calculate fixed costs and variable costs

Use Excel to perform calculations

Compute costs per unit and weight per unit from bulk costs and bulk weights

Suggest a price/lb factoring in costs, market structure, and competitors pricing

Introduction

Rashed just bought a self-serve candy business. This shop, SweetTooth Candies, serves a large variety of candies, including chocolates, hard candies, jelly beans and gummies. The customer fills an empty bag with their choice of candies and then it is weighed at the checkout. All candies are sold for the same priceper pound to customers.

Rashed needs to decide how much to charge for each pound of product. However, he has discovered that determining this answer is not so easy because many of the candies have different costs. Based on his initial orders and invoices, Rashed has gathered data on the costs and weights of the products he stocks. Several of the candies have the same costs, so he has grouped them together into types. Refer to the candycost worksheet of the data file for this information.

Next, Rashed knows that he cannot just take an average price of the candies. Instead, he invites 25 family and friends to the shop just prior to its opening and tells them to get themselves whatever they would like. Rashed weighs each ingredient they put into their bag, so he knows exactly how much of each type of candy each person chooses. The details of these measurements are shown in the candydata worksheet of the data file provided.

Deliverables:

Perform the necessary analysis and research to answer the following set of questions. The output must be a professional looking report and an Excel spreadsheet. Remember to format all values to the appropriate number of decimal places. Add column labels to organize and present you calculations. Use formulas to do all your calculations, and do not change them into values (I should be able to check your formulas and calculations).

Adapted from DePaolo et al (2018), Weight-based cost estimation using Excel: candy, yogurt, and salad applications. Journal of Business Cases and Applications.

Initial Calculations

3) Refer to the candycost worksheet that shows all of the cost and weight data. Calculate how much each of the items costs per pound. Present results in new column labeled Cost Per Pound.

Table 1: "candycost" dataset
Component	Bulk Cost	Cost per Ibs	Cost per Unit
Cholocate covered nuts	$12.96 / 2 U.S. lbs	$6.48	n/a
Gourmet jelly beans	$24.50 / 5 U.S. pounds	$4.90	n/a
Gummy candies	$13.50 / 5 U.S. pounds	$2.70	n/a
Sour hard candies	$16.50 / 5 U.S. pounds	$3.30	n/a
Individually wrapped candies	$14.99 / 5 U.S. pounds	$3.00	n/a
M&Ms, Skittles, Reeses pieces, etc.	$156.50 / 25 U.S. pounds	$6.26	n/a
Plastic bags to hold candy	$35 / 1000 bags	n/a	$0.04
Twist ties to secure bags closed	$ 10 / 2000	n/a	$0.01
Average Cost per unit lbs		$4.44

4) Determine how much each plastic bag and twist tie cost Rashed. While he will assume the weight of the bag and tie are negligible, Rashed wants their purchase cost factored into the cost of each order. Do these calculations in Excel, labeled as Cost Per Unit.

5) The sample data for the 25 observations are shown in the worksheet candydata. For each of these observations, determine the total weight of the observation. Do these calculations in Excel in a column labeled Total Weight.

6) Also in Excel, calculate the total cost of each of the 25 sample orders. Remember to add in the cost of the plastic bag and twist tie in addition to the cost of the candies. Do these calculations in Excel in a column labeled Total Cost. You may add other columns as required; please provide appropriate column labels.