Question

Alice works at the local restaurant as a food server. She typically earns 18% tips on her tables served during her shift. From her tip earnings she is required to pay 5% of liquor sales served at her tables to the bartender. Additionally, she is required to pay 2% of her total sales to the hostess. Alice averages sales of 80% food and 20% liquor. This is the last night Alice works before rent is due and she is $92 short from her full amount. How much in sales from her tables tonight does she need to have to pay her full rent for the month? Use Goal Seek to find this amount. Set up your Excel with range names, ability to change parameters of % food, % liquor, earned tip rate %, liquor % payout, hostess % payout. Format all dollars in currency format.

Answer 1

Following is the answer. The amounts are calculated in Excel, hence view the formuals used for obtaining the below values. I am only a moderate user of Excel and not an advanced user, hence was not able to perform the Goal Seek portion.

Alice earning Calculation - Manual
Description	Legends	Percentage	Amount - Calculated Manually
Sales Earnings	A	80%	491
Liquor Earnings	B	20%	123
Total Earnings	C = A+B	100%	613
Tips Earned - Income - Sales	D = A * 18%	14.40%	88
Tips Earned - Income - Liquor	E = B * 18%	3.60%	22
Total Income for Alice	F = D + E	18.00%	110
Payment to Bartender - 5% on Liquor Sales	G = 5% * B	-1.00%	-6
Payment to Hostess - 2% on Total Sales	H = 2% * C	-2.00%	-12
Total Expense for Alice	I = G + H	-3.00%	-18
Net Income for Alice	J = F - I	15.00%	92