Question

In: Accounting

Please answer ONLY subpart (D) and using solver to solve the question Jane operates a company...

Please answer ONLY subpart (D) and using solver to solve the question


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.

Solutions

Expert Solution

ANSWER:-

If you have any queries please ask me in the comment i am here for help you. Please do not direct thumbs down just ask if you have any query. And if you like my work then please appreciates with up vote.

                                                     ******THANK YOU*************

  


Related Solutions

Please solve using EXCEL SOLVER and show steps 1 – A company requires during the next...
Please solve using EXCEL SOLVER and show steps 1 – A company requires during the next four months, respectively, 50, 65, 100, and 70 units of a commodity (no backlogging is allowed). Production costs are $5, $8, $4, and $7 per unit during these months. The storage cost from one month to the next is $2 per unit (assessed on ending inventory). It is estimated that each unit on hand at the end of month 4 could be sold for...
SOLVE THE FOLLOWING 2 LINEAR PROGRAMMING PROBLEMS USING EXCEL AND THE SOLVER ADD-IN. PLEASE SHOW ME...
SOLVE THE FOLLOWING 2 LINEAR PROGRAMMING PROBLEMS USING EXCEL AND THE SOLVER ADD-IN. PLEASE SHOW ME ALL THE EXCEL STEPS. PROBLEM #1:   Maximize Z = $60X + $90Y                             Subject to:   60X + 30Y >= 1,500                                                     100X + 100Y <= 6,000                                                                              Y >= 30                                                                           X, Y >= 0 PROBLEM #2: Minimize Z = $3,000X + $1,000Y                              Subject to:   60X + 20Y >= 1,200                                                         10X + 10Y >= 400                                                     ...
PLEASE SOLVE THIS QUESTION WITHOUT CHANGING THE SOURCE CODE POSTED BELOW. (ONLY ADDING) #include <iostream> using...
PLEASE SOLVE THIS QUESTION WITHOUT CHANGING THE SOURCE CODE POSTED BELOW. (ONLY ADDING) #include <iostream> using namespace std; const int arrSize = 3; // QUESTION ii. COMPLETE A displayMatrix(...) FUNCTION HERE // QUESTION iii. COMPLETE A transposedMatrix(...) FUNCTION HERE // QUESTION iv. COMPLETE A calculateTotal(...) FUNCTION HERE int main(){ // variable declaration int mat[arrSize][arrSize], transMat[arrSize][arrSize] = {0}, total = 0; // QUESTION i. Prompt user to enter 9 integer numbers and fill in into the matrix cout << "\nThe original...
How to solve using Excel Solver: This is an assignment problem with 10 applicants and 5...
How to solve using Excel Solver: This is an assignment problem with 10 applicants and 5 positions. Determine the optimal assignments that Brenda Last should make. Use the data to determine the optimal assignments, but do not complete questions in the last two paragraphs of the text Formulate a linear programming model for this problem. List the objective function (minimize or maximize) and all model constraints. Solve problem using MS Excel’s Solver Hint: Use the “integer” constraint Highlight the "winning"...
Using the partial ANOVA Table found in question d-2, please answer the following questions. Use the...
Using the partial ANOVA Table found in question d-2, please answer the following questions. Use the 0.05 significance level. How many treatments are there? What is the total sample size? What is the critical value of F? (Round your answer to 2 decimal places.) d-1. Write out the null and alternate hypothesis. d-2. From the information given above, fill in the blanks in the ANOVA table below. Calculate the value of F also. (Round the values to the nearest whole...
Question 1 Jane operates a company called “Fruity Shop”. It sells apple juice, orange juice and...
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...
3) Perfectly competitive markets PLEASE DO NOT ANSWER A-D PLEASE ONLY ANSWER E,F,G # of Contraptions...
3) Perfectly competitive markets PLEASE DO NOT ANSWER A-D PLEASE ONLY ANSWER E,F,G # of Contraptions Total Cost 0 500 1 580 2 640 3 690 4 730 5 760 6 800 7 850 8 950 9 1200 10 2000 c) If market price equals $100, how many units should be produced? What is revenue? What is profit? Add these columns to your Table too. d) What is the fixed cost? Would the number of units produced change if the...
please answer D and E completely Lee Company, which has only one product, has provided the...
please answer D and E completely Lee Company, which has only one product, has provided the following data concerning its most recent month of operations: Selling price $95 Units in beginning inventory 100 Units produced 6,200 Units sold 5,900 Units in ending inventory 400 Variable costs per unit: Direct materials $42 Direct labour $28 Variable manufacturing overhead $1 Variable selling and administrative $5 Fixed costs: Fixed manufacturing overhead $62,000 Fixed selling and administrative $35,400 The company produces the same number...
Solve the following LP problem using the Solver in MS Excel. A farmer wants to customize...
Solve the following LP problem using the Solver in MS Excel. A farmer wants to customize his fertilizer for his current crop. He can buy plant food mix A and plant food mix B. Each cubic yard of food A contains 20 pounds of phosphoric acid, 30 pounds of nitrogen and 5 pounds of potash. Each cubic yard of food B contains 10 pounds of phosphoric acid, 30 pounds of nitrogen and 10 pounds of potash. He requires a minimum...
Problem:  Using Solver, solve the linear program to find the optimal number of batches to make of...
Problem:  Using Solver, solve the linear program to find the optimal number of batches to make of each of the three cookies. Price per chocolate chip cookie $                      1.50 Price per sugar cookie $                      1.00 Price per snickerdoodle cookie $                      1.00 Recipes for one batch Number of cookies/batch 20 20 30 Ingredient Chocolate chip cookie recipe Sugar cookie recipe Snickdoodle recipe Butter (sticks) 2 2 2 Sugar (cups) 1 2 1 Eggs 2 3 1 Chocolate chips (cups) 1 0 0...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT