YumYum EXPRESS CASE SCENARIO

YumYum Express is a food delivery platform currently struggling to achieve profitability in its operations. As the manager of YumYum

Express, you are tasked with analyzing a dataset of $1,000$ food orders placed in January and the first week of February $2024.$ Your goal

is to understand the cost structure and profitability dynamics to identify strategic opportunities for improvement.

The dataset includes detailed information on each food order, such as Order ID$,$ Customer ID$,$ Restaurant ID$,$ Order and Delivery Date

and Time, Order Value, Delivery Fee, Payment Method, Discounts and Offers, Commission Fee, Payment Processing Fee, and

Refunds$/$Chargebacks$.$ This information will serve as a basis for analyzing the cost structure and profitability of the food delivery

service. However, due to poor record$-$keeping by the data managers at YumYum Express, the dataset needs to be cleaned before

analysis can begin.

Ensure you understand the meanings of the columns in the dataset before starting your assignment. If any data field remains unclear

after reviewing the data dictionary, contact the professor or TA for clarification.

Open the Excel file YumYum$\_$Express.xlsx$.$

Data Management $(15$ points$)$

$1.$ In column Order Value, format dollar values in Accounting format, US Dollars, no decimals.

$2.$ The Payment Method field is improperly formatted. Re$-$format it so that it says, $$Credit Card$,$Digital Wallet$,$ and $$Cash on

Delivery$$ instead of $$CreditCard$,$DigitalWallet$,$ and $$CashonDelivery$.$

$3.$ Autofit column width of fields $$Order Date and Time$,$Delivery Date and Time$,$Payment Method$,$Discounts and Offers$.$

BMGT$301-$WB$11$

Summer $2024$

Excel Assignment $1$ Instructions

$2$

$4.$ The Order Date and Time field reflects both the date and time when an order was placed. Split the data into two new columns: Order

Date, and Order Time. Do not delete the Order Date and Time field.

$5.$ Generate a new filed named $$order month$,$ which is about the month in which the order was placed.

Statistical Analysis $(28$ points$)$

YumYum Express$$s profits are derived from two primary sources: the Delivery Fee and the Commission Fee. The allocation of these

fees between YumYum Express and the delivery drivers is as follows:

$$ YumYum Express retains $60\%$ of the Delivery Fee for each order, with the remaining $40\%$ going to the delivery driver.

$$ YumYum Express retains $75\%$ of the Commission Fee for each order, with the remaining $25\%$ going to the delivery driver.

In the event of an approved refund request, YumYum Express covers $60\%$ of the refund amount, while the delivery driver is

responsible for the remaining $40\%.$

$1.$ Using formulas, compute the following for Order Value: average, maximum, minimum, sum, variance. Store each result in a

new column separately. HINT: Make sure to use the variance formula.

$2.$ Find the fifth largest Order Value value using a formula. Store it in a column named order$\_$value$\_5$th$.$

$3.$ Create a new field net profits and calculate the net profits YumYum Express earns from each order. Format net profits as

Currency, US Dollars, with two decimals, and negative values shown in red and surrounded by parentheses.

$4.$ Calculate the total profits YumYum Express received from all orders placed in January, $2024.$ Store the result in a new column

named total profits Jan.

$5.$ Create a new field $$wait$\_$time$$ measuring the difference between $$Order Date and Time$$ and $$Delivery Date and Time$.$ Count

the number of orders that consumers waited for more than $1$ hour. Store the result in a new column named

long$\_$wait$\_$orders.

$6.$ Utilize formulas to calculate the frequency of each value in the Discounts and Offers column. Identify which discount and

offer has been used most frequently to place orders. Based on the Discounts and Offers data, determine how many new

users have joined YumYum Express. Calculate the user growth rate and store the results in new columns named

popular$\_$discounts$\_$offers, new$\_$user$\_$amount, and user$\_$growth$\_$rate.

$7.$ Due to a new policy by the government, YumYum Express$$ analysts expect that there will be a $4\mathrm{.}2\%$ increase in Commission

Fee in March, $2024.$

BMGT$301-$WB$11$

Summer $2024$

Excel Assignment $1$ Instructions

$3$

Using the total profits Jan you have calculated, calculated the projected March $2024$ profits for YumYum Express. Store this in

a field named projected$\_$march$\_$profits. Do this using absolute references.

Reflection $(7$ points$)$

Based on the user growth rate you calculated, would you classify YumYum Express as a rapidly growing business? Given the current

bank savings interest rate of $5\%,$ do you think expanding YumYum Express is a prudent decision? Write down your answers to these

questions along with the reasons for your conclusions.

Please format the cell nicely so the response is easy to read.

Submission

Submit your Microsoft Excel workbook file $(.$xlsx$)$ to ELMS via file upload.

Make sure to double check that the file you submitted is a$)$ in the correct format