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 companys 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.