Question

Question 1
Jane operates a company called “Fruity Shop”. It sells apple juice, orange juice and melon juice in boxes. Jane rents a retail space to sell these items and the cost of goods sold (COGS), selling price and daily demand for each item is given in the table below:

Description	COGS($)	Unit Selling Price($)	Demand Per Day
Apple Juice	$ 10.00	$ 14.00	400
Orange Juice	$ 12.00	$ 15.00	300
Melon Juice	$ 13.00	$ 16.00	250

The rental cost is $20,000 per month. Jane has hired 5 staff to help her in the shop and the manpower cost is $30,000 per month. She also spends $20,000 monthly on marketing to promote her products and increase her brand awareness. Assuming that there are 22 days in a month.
(a) Construct a spreadsheet model to analyse the company’s monthly finance, the spreadsheet model should include total revenue ($), total expenses ($), profit/loss and profit margin (%).
(b) If the company wants to increase the profit margin to 10% by changing the selling price of Apple Juice, what will be the new selling price?
(c) For every $1 increase in price, the demand will drop by 20 boxes. It applies to all the juices. What is the relationship between the selling price and the demand for all juice? What is the demand for apple, orange and lemon juice if the selling price is $17 per unit?
(d) What will be the new selling price for all juices, if the company wants to earn 20% profit margin, your model should include the price elasticity of demand. Describe how you derive at the solution (write down the steps in detail) and clearly show the new profit margin and selling price.

Work on excel and show the formulas used. Upload the excel file.

Answer 1

	(a)
			Apple Juice	Orange Juice	Melon Juice	Total
	A	Unit Selling Price	$14	$15	$16
	B	Unit Sales per day	400	300	250
	C=A*B	Sales Revenue per day	$5,600	$4,500	$4,000	$14,100
	D=C*22	Sales Revenue per month	$123,200	$99,000	$88,000	$310,200
	E	COGS per unit	$10	$12	$13
	F=B*E*22	COGS per month	$88,000	$79,200	$71,500	$238,700
		Other Expenses:
	G	Rentalexpenses				$20,000
	H	Salaries and Wages				$30,000
	i	Marketing expenses				$20,000
	J=F+G+H+I	Total Cost per month				$308,700
	K=D-J	Net Profit per month				$1,500
	L=K/D	Net Profit Margin				0.004836
		Net Profit Margin percentage				0.48%
		Required Profit margin	10%
		Required Profit margin	0.1
		TotalSales Revenue excluding apple Juice	$187,000	(310200-123200)
		Assuming Unit selling price of apple juice	X
		Unit sales per month of apple juice	8800	(400*22)
		Total Sales Revenue=	187000+8800X
		Total Cost	$308,700
		Net Profit=187000+8800X-308700
		Net Profit=8800X-121700		$121,700
		Net Profit Percentage=(8800X-121700)/(187000+8800X)=0.1
		8800X-121700=18700+880X
		7920X=	140400
		X=140400/7920=	17.7272727
		Required Price of apple Juice	$17.73
			Apple Juice	Orange Juice	Melon Juice	Total
	A	Unit Selling Price	$17.73	$15	$16
	B	Unit Sales per day	400	300	250
	C=A*B	Sales Revenue per day	$7,092	$4,500	$4,000	$15,592
	D=C*22	Sales Revenue per month	$156,024	$99,000	$88,000	$343,024
	E	COGS per unit	$10	$12	$13
	F=B*E*22	COGS per month	$88,000	$79,200	$71,500	$238,700
		Other Expenses:
	G	Rentalexpenses				$20,000
	H	Salaries and Wages				$30,000
	i	Marketing expenses				$20,000
	J=F+G+H+I	Total Cost per month				$308,700
	K=D-J	Net Profit per month				$34,324
	L=K/D	Net Profit Margin				0.100063
		Net Profit Margin percentage				10.01%
	.(c)	Relationship between selling price and demand
		Demand =Q
		Selling Price =p
		dQ/dP=-20
		Demand for apple juice at price=$17	$340	(400-((17-14)*20))
		Demand for orange juice at price=$17	$260	(300-((17-15)*20))
		Demand for lemon juice at price=$17	$230	(250-((17-16)*20))