Question

so I started working on this and I just don’t feel like I’m doing it correctly. I have emailed my professor for help but they are out of town and still haven’t responded. Any help is greatly appreciated!

Excel

A friend who has not majored in STEM or Business has struggled to find their footing in life. You are excited and proud to hear that they will be opening a Cupcake shop in a newly revitalized part of Huntsville called “Cupcakes ‘N’ Coffee”. They have come to you to try and figure out if their new venture will be successful based on the assumptions that they have. Your friend tells you that they will be selling cupcakes, coffee, and wine, and food entrees. They tell you that they have spent many hours studying other similar businesses in the area. They realized there are times when there are high demand expectations and low demand expectations. From what they have seen high demand times are Friday, Saturday, and Monday’s and they believe the expected distribution of sales will be 50% cupcakes, 25% coffee, 10% wine, and 15% food. On low demand days they think the expected distribution will be 40% cupcakes, 30% coffee, 10% wine, and 20% food. Cupcakes sell for $5 each, coffee is $2 a cup, a glass of wine is $7 , and an entree of food will be $10. The unit costs to make each cupcake is $1, a cup of coffee is $0.25 , a glass of wine is $2, and food entrees have a unit cost of $5 each. From what they have seen on high demand days they believe they will sell 150 total units of items and on low demand days 50 total units of items, respectively. The monthly rent is $1000, utilities vary but are expected to average $10 a day, the internet will be $50 a month, and employee labor is expected to average $100 a day. Lastly, ( but not least!) your time is money and besides you have faith that your friend’s business will be a success, in order to help your friend with these calculations you charge them $250 a week in store credit for the life of the business which is cash convertible at any time. Additional assumptions include that the business is open all 7 days and that one month can be considered 4 weeks or 28 days. Your friend asks you to setup the basic revenue model for them and let them know the following:

1. Weekly Expected Revenue
2. Weekly Expected Costs
3. Weekly ExpectedProfits

Now that you have setup the basic revenue model for your friends shop “ “Cupcakes ‘N’ Coffee” based on their assumptions they would like help making some additional decisions. Here are some questions they have for you:

1. Your friend would like you to run a scenario in which high demand days are only Friday and Saturday. What is the expected revenue, costs, and profits?
2. Your friend has recently taken a trip to Brooklyn, NY; couch surfing for a few weeks and working sporadically at cupcake shops. They noticed that the demand distribution for similar businesses more closely related to theirs may actually be 25% for both high and low demand, across all four items that they intend to sell at the shop. All other assumptions held constant, what is the expected weekly revenue, costs, and profit? (5 points) Using this same assumption your friend would like to know the expected revenue, costs, and profit if the price of coffee increases to $4 a cup, which seems to be more in line with industry standards?
3. Your friend believes that two workers are sufficient( them and one other person) but just in case service times end up being unexpectedly high they would like to know how the weekly expected revenue, costs, and profits will change if two additional part time workers are hired at $50 a day, each? (7.5 points)  What if the city of Huntsville provides a hiring incentive that totals $2,000 a month for the two part-time workers, what is the expected weekly revenue, costs, and profit?
4. Your friend is not sure, but believes they may be able to get a contract for a food wholesaler better than going to Costco or Sam's Club (general public wholesalers with an annual subscription fee). With this new contract cupcakes will now cost $0.50 each, coffee will still be $0.25, wine will decrease to $1 a glass and the unit cost for food entrees will decrease to $3. Holding the original assumptions constant, what is the new expected weekly revenue, costs, and profit for this scenario?
5. The employee labor will be a mutual friend of you two. Your friend would like to know if it is cheaper to pay the mutual friend a percentage of profit right now or keep the current wage of $100 a day. Your friend would like to offer 3% of the profits in lieu of the current wage. Is this cheaper for him to do based on the current assumptions or is it cheaper to pay $100 a day? Show the changes to the weekly expected revenue, costs, and profits
   
   Suggested tables to have include 1) total unit sales by demand 2) unit costs 3) unit sales price 4) demand distribution percentages 5) Fixed Costs 6) conversion factor tables with factor for days in a month, days in a week, and weeks in a month, 7) a calculated table showing expected unit sales by days of the week and other tables as you see fit.

Answer 1

Demand and Variable Cost Table

	Demand Distribution	Cup Cakes	Coffee	Wine	Food	Total
Selling Price Per unit		5	2	7	10
Cost Per Unit		1	0.25	2	5
Monday	150	75	37.5	15	22.5
Tuesday	50	20	15	5	10
Wednesday	50	20	15	5	10
Thursday	50	20	15	5	10
Friday	150	75	37.5	15	22.5
Saturday	150	75	37.5	15	22.5
Sunday	50	20	15	5	10
Total	650	305	172.5	65	107.5
Total Revenue Week		1525	345	455	1075	3400
For 4 Weeks	2600	1220	690	260	430
Total Weekly Cost		305	43.125	130	537.5	1015.625

Weekly Profit Statement:

Total Demand	650
Total Revenue	3400
Cost of goods sold	1015.625
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	700
Accountant Charges	250
Total Cost	2298.125
Profit	1101.875

Demand Days are only Fridays and Saturdays:

	Demand Distribution	Cup Cakes	Coffee	Wine	Food	Total
Selling Price Per unit		5	2	7	10
Cost Per Unit		1	0.25	2	5
Monday	50	25	12.5	5	7.5
Tuesday	50	20	15	5	10
Wednesday	50	20	15	5	10
Thursday	50	20	15	5	10
Friday	150	75	37.5	15	22.5
Saturday	150	75	37.5	15	22.5
Sunday	50	20	15	5	10
Total	550	255	147.5	55	92.5
Total Revenue Week		1275	295	385	925	2880
For 4 Weeks	2200	1020	590	220	370
Total Weekly Cost		255	36.875	110	462.5	864.375

Total Demand	550
Total Revenue	2880
Cost of goods sold	864.375
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	700
Accountant Charges	250
Total Cost	2146.875
Profit	733.125

Increase in coffee price to $4

	Demand Distribution	Cup Cakes	Coffee	Wine	Food	Total
Selling Price Per unit		5	4	7	10
Cost Per Unit		1	0.25	2	5
Monday	150	75	37.5	15	22.5
Tuesday	50	20	15	5	10
Wednesday	50	20	15	5	10
Thursday	50	20	15	5	10
Friday	150	75	37.5	15	22.5
Saturday	150	75	37.5	15	22.5
Sunday	50	20	15	5	10
Total	650	305	172.5	65	107.5
Total Revenue Week		1525	690	455	1075	3745
For 4 Weeks	2600	1220	690	260	430
Total Weekly Cost		305	43.125	130	537.5	1015.625

Total Demand	650
Total Revenue	3745
Cost of goods sold	1015.625
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	700
Accountant Charges	250
Total Cost	2298.125
Profit	1446.875

Increase in Labour cost by 50*2 = 100 a day. Total increase = 100 (previous cost) + 100 (new) = 200

Total cost in a week = 200*7 = 1400

Total Demand	650
Total Revenue	3745
Cost of goods sold	1015.625
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	1400
Accountant Charges	250
Total Cost	2998.125
Profit	746.875

* the price of coffee is $4

City Provides hiring incentives of $2000 a month. As there are 4 weeks in a month,hence weekly incentives will be

2000/4 = $500 per week

Subtracting the same from the labour cost = 1400-500 = 900

Total Demand	650
Total Revenue	3745
Cost of goods sold	1015.625
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	900
Accountant Charges	250
Total Cost	2498.125
Profit	1246.875

New Cost from Costo or Sam's club:

	Demand Distribution	Cup Cakes	Coffee	Wine	Food	Total
Selling Price Per unit		5	2	7	10
Cost Per Unit		0.5	0.25	1	3
Monday	150	75	37.5	15	22.5
Tuesday	50	20	15	5	10
Wednesday	50	20	15	5	10
Thursday	50	20	15	5	10
Friday	150	75	37.5	15	22.5
Saturday	150	75	37.5	15	22.5
Sunday	50	20	15	5	10
Total	650	305	172.5	65	107.5
Total Revenue Week		1525	345	455	1075	3400
For 4 Weeks	2600	1220	690	260	430
Total Weekly Cost		152.5	43.125	65	322.5	583.125

Total Demand	650
Total Revenue	3400
Cost of goods sold	583.125
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	700
Accountant Charges	250
Total Cost	1865.625
Profit	1534.375

3% of Profit

if we pay 3% of the profit then we have to deduct the labor charges from the statement

Total Demand	650
Total Revenue	3745
Cost of goods sold	1015.625
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	0
Accountant Charges	250
Total Cost	1598.125
Profit	2146.875

3% of profit will be = 2146.875*0.03 = 64.04

Labour cost before was $700 for a week and hence offering 3% is cheaper.

Monthly Statement

	Monthly
Total Demand	2600
Total Revenue	14980
Cost of goods sold	4062.5
Monthly Rent	1000
Utilities	280
Internet	50
Labour	2800
Accountant Charges	1000
Total Cost	9192.5
Profit	5787.5

Your friend has recently taken a trip to Brooklyn, NY; couch surfing for a few weeks and working sporadically at cupcake shops. They noticed that the demand distribution for similar businesses more closely related to theirs may actually be 25% for both high and low demand, across all four items that they intend to sell at the shop. All other assumptions held constant, what is the expected weekly revenue, costs, and profit?

The above question is not clear. Does that mean an increase in demand by 25% or 25% as demand only.

Answering with the assumption that it is the demand increase of 25%

	Demand Distribution	Cup Cakes	Coffee	Wine	Food	Total
Selling Price Per unit		5	4	7	10
Cost Per Unit		1	0.25	2	5
Monday	187.5	93.75	46.875	18.75	28.125
Tuesday	62.5	25	18.75	6.25	12.5
Wednesday	62.5	25	18.75	6.25	12.5
Thursday	62.5	25	18.75	6.25	12.5
Friday	187.5	93.75	46.875	18.75	28.125
Saturday	187.5	93.75	46.875	18.75	28.125
Sunday	50	20	15	5	10
Total	800	376.25	211.875	80	131.875
Total Revenue Week		1881.25	847.5	560	1318.75	4607.5
For 4 Weeks	3200	1505	847.5	320	527.5
Total Weekly Cost		376.25	52.96875	160	659.375	1248.594

Total Demand	800
Total Revenue	4607.5
Cost of goods sold	1248.594
Monthly Rent	250
Utilities	70
Internet	12.5
Labour	700
Accountant Charges	250
Total Cost	2531.094
Profit	2076.406