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                                                     ...
Using Excel Linear Solver Please answer the following, include all of the constraints. You are a...
Using Excel Linear Solver Please answer the following, include all of the constraints. You are a project manager for a consulting firm. You are going to hire four new employees. The candidates that you are choosing from are named Amy, Bob, Charlie, Debbie, and Elizabeth. You are managing two projects that you will use your new employees to complete. Project 1 will require at least 700 labor hours and Project 2 will require at least 870 labor hours. Elizabeth is...
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...
Can anyone please explain step by step how to solve this by excel solver cause the...
Can anyone please explain step by step how to solve this by excel solver cause the solver won't accept the binary word A group of college students is planning a camping trip during the upcoming break. The group must hike several miles through the woods to get to the campsite, and anything that is needed on this trip must be packed in a knapsack and carried to the campsite. On particular student, Tina Shawl, has identified eight items that she...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT