Assignment
Mindy operates a company called "Fruity Juice" via e-commerce website. It sells apple juice, orange juice and guava juice in boxes. She rents a warehouse to store these items and engages a third-party logistics vendors to ship these items to the customer. 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
|
$12.00
|
450
|
|
Orange Juice
|
$11.50
|
$13.00
|
250
|
|
Guava Juice
|
$12.50
|
$15.00
|
300
|
The warehouse rental is $8000 per month. Mindy has hired 3 staff to help her with the operation and each staff cost $3000 per month and she gets a salary of $5000. To promote the product online, Mindy also spends $2000 monthly on digital marketing to increase her brand awareness. She also needs to pay third-party logistics vendors $0.50 per 10 boxes to ship the items to the customers. Assuming that there are 30 days in a month.
Task
1. Construct a spreadsheet model to analyze the company's monthly finance, the spreadsheet model should include total revenue ($), total expenses ($), profit/loss ($) and profit margin (%). Sketch the influence diagram of your model.
2. If the company wants to increase the profit margin to 10% by changing the selling price of Apple Juice, discuss what will be the new selling price of apple juice, explain how you derive the answer? Show your answer in nearest 10 cents.
3. 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? Calculate the demand for apple, orange and guava juice if the selling price is $16 per unit?
4. 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. Total demand for each month has to be at least 15,000. Describe how you derive at the solution (write down the steps in detail) and clearly show the new profit margin and selling price.