Question
Hi! Please help solve these questions in one microsoft excel file . (I have problems starting from part (d) onwards) Please show step by step
Hi! Please help solve these questions in one microsoft excel file. (I have problems starting from part (d) onwards)
Please show step by step with formula/function used visible on formula bar
Part 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 (%). (Using a table format with no hardcoding. E.g. If A2=total revenue, B2=total expenses, profit/loss =A2-B2)
- (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? (Using goal seek function in excel)
-
(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? (Make a table)
- What is the demand for apple, orange and lemon juice if the selling price is $17 per unit? (Using VLOOKUP with reference from the table)
- (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. (Using solver in excel)
A continuation of this question link here: https://www.chegg.com/homework-help/questions-and-answers/hi-please-solve-question-single-excel-file-please-screenshot-showing-step-step-solution-fo-q35402032
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started