Use the goal seek function in Excel to determine how many customer meals ( in
Fantastic news! We've Found the answer you've been seeking!
Question:
Use the goal seek function in Excel to determine how many customer meals in total and by product line must be sold to earn $ in net operating income. Hint: Use the goal seek function to change net operating income to $ by changing only the cell that contains the total number of customer meals sold.
a How many total meals must be sold to earn $ in net operating income?
b How much total sales is required to earn $ in net operating income?
Transcribed Image Text:
Total Sales Revenue ($5 per serving) Less: Variable costs: Customer Meals: Direct Materials ($1 per customer serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead (60% of Direct labor) Variable Manufacturing Cost ($1.25 per donated meal) Customer Delivery Expenses ($2 per customer order) Donation Delivery Expense ($125 per delivery) Total Variable Costs Total Contribution Margin Less: Fixed Costs Allocated Based on Sales Revenue Fixed Manufacturing Expenses Fixed Selling Expenses Fixed Administrative Expenses Total Fixed Expenses Net Operating Profit Average Contribution Margin Per Meal Sold Average Contribution Margin Ratio (% of Revenue) Break-even point (customer meals sold) Break-even point (total sales revenue) Margin of Safety (Customer Meals) Margin of Safety (Sales $) Degree of Operating Leverage Single Serving Dual Serving Family Size 15,000 $ 50,000 $ 240,000 $ $ $ $ $ $ 3,000 $ 2,250 1,350 3,750 1,500 750 12,600 $ $ 2,400 $ 10,000 $ 3,750 2,250 6,250 2,500 1,250 26,000 $ 3,689 $ 1,426 1,967 7,082 (4,682) $ Single Serving Dual Serving 0.80 $ $ 12,295 $ 4,754 6,557 23,607 48,000 $ 9,000 5,400 15,000 6,000 3,000 86,400 $ 59,016 $ 22,820 31,475 113,311 393 $ 40,289 $ 24,000 $ 153,600 $ 180,000 Family Size 4.80 $ 12.80 Single Serving Dual Serving Family Size Total 305,000 $ 61,000 15,000 9,000 25,000 10,000 5,000 125,000 75,000 29,000 40,000 144,000 36,000 Overall 9.00 59.02% Total Input Values: Number of Customer Meals Sales mix (% of customer meals) Number of Servings per Meal Number of Servings Number of Customer Orders Number of Donated Meals Number of Donated Deliveries Price Per Serving Variable Costs: Direct Materials ($1 per serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead 60% of DL Variable cost of donated meals Variable delivery expenses (customer meals) Variable delivery expenses (donated meals) Fixed costs: Fixed manufacturing costs Fixed selling expenses Fixed administrative expenses Total Fixed costs 60% Dual Single Serving Serving 3,000 5,000 15% 25% 1 2 4 3,000 10,000 48,000 61,000 1,250 3,000 5,000 5,000 12,000 20,000 10 24 40 750 3,000 6 $ $ $ $ $ $ $ $ $ $ Family Size 12,000 5.00 per customer serving 1.00 per customer serving 0.75 per customer meal 60% of direct labor cost 1.25 per donated meal 2.00 per customer order 125.00 per delivery 75,000 29,000 40,000 144,000 Total 20,000 100% Total Sales Revenue ($5 per serving) Less: Variable costs: Customer Meals: Direct Materials ($1 per customer serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead (60% of Direct labor) Variable Manufacturing Cost ($1.25 per donated meal) Customer Delivery Expenses ($2 per customer order) Donation Delivery Expense ($125 per delivery) Total Variable Costs Total Contribution Margin Less: Fixed Costs Allocated Based on Sales Revenue Fixed Manufacturing Expenses Fixed Selling Expenses Fixed Administrative Expenses Total Fixed Expenses Net Operating Profit Average Contribution Margin Per Meal Sold Average Contribution Margin Ratio (% of Revenue) Break-even point (customer meals sold) Break-even point (total sales revenue) Margin of Safety (Customer Meals) Margin of Safety (Sales $) Degree of Operating Leverage Single Serving Dual Serving Family Size 15,000 $ 50,000 $ 240,000 $ $ $ $ $ $ 3,000 $ 2,250 1,350 3,750 1,500 750 12,600 $ $ 2,400 $ 10,000 $ 3,750 2,250 6,250 2,500 1,250 26,000 $ 3,689 $ 1,426 1,967 7,082 (4,682) $ Single Serving Dual Serving 0.80 $ $ 12,295 $ 4,754 6,557 23,607 48,000 $ 9,000 5,400 15,000 6,000 3,000 86,400 $ 59,016 $ 22,820 31,475 113,311 393 $ 40,289 $ 24,000 $ 153,600 $ 180,000 Family Size 4.80 $ 12.80 Single Serving Dual Serving Family Size Total 305,000 $ 61,000 15,000 9,000 25,000 10,000 5,000 125,000 75,000 29,000 40,000 144,000 36,000 Overall 9.00 59.02% Total Input Values: Number of Customer Meals Sales mix (% of customer meals) Number of Servings per Meal Number of Servings Number of Customer Orders Number of Donated Meals Number of Donated Deliveries Price Per Serving Variable Costs: Direct Materials ($1 per serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead 60% of DL Variable cost of donated meals Variable delivery expenses (customer meals) Variable delivery expenses (donated meals) Fixed costs: Fixed manufacturing costs Fixed selling expenses Fixed administrative expenses Total Fixed costs 60% Dual Single Serving Serving 3,000 5,000 15% 25% 1 2 4 3,000 10,000 48,000 61,000 1,250 3,000 5,000 5,000 12,000 20,000 10 24 40 750 3,000 6 $ $ $ $ $ $ $ $ $ $ Family Size 12,000 5.00 per customer serving 1.00 per customer serving 0.75 per customer meal 60% of direct labor cost 1.25 per donated meal 2.00 per customer order 125.00 per delivery 75,000 29,000 40,000 144,000 Total 20,000 100%
Expert Answer:
Answer rating: 100% (QA)
To use the Goal Seek function in Excel we need to set up a model that calculates the Net Operating Profit based on various inputs including the number ... View the full answer
Related Book For
Cost Management A Strategic Emphasis
ISBN: 9781259917028
8th Edition
Authors: Edward Blocher, David F. Stout, Paul Juras, Steven Smith
Posted Date:
Students also viewed these accounting questions
-
The following table summarizes the operating results for Bene Petits first year of operations: Bene Petit First year operating data: Single (1 serving) Dual (2 servings) Family (4 servings) Total...
-
Most businesses sell several products at varying prices. The products often have different unit variable costs. Thus, the total profit and the breakeven point depend on the proportions in which the...
-
Use the information in problem 12-53 to answer the following questions: Required 1. What is the maximum machine operating cost of the overhauled AccuDril for the replacement decision to be an...
-
A pistoncylinder device contains superheated steam. During an actual adiabatic process, the entropy of the steam will (never, sometimes, always) increase.
-
Maslovskaya, CPA, has been engaged to examine the financial statements of Broadwall Corporation for the year ended December 31, 2015. During the year, Broadwall obtained a long-term loan from a local...
-
Flash calculations are simpler for binary systems than for the general multicomponent case because the equilibrium compositions for a binary are independent of the overall composition. Show that, for...
-
Which interest rate on a bond determines the amount of the semiannual interest payment? a. Market rate b. Effective rate c. Stated rate d. Semiannual rate
-
List the six building blocks that make up digital computers, and describe the flows of data that occur among these blocks.
-
Create a scenario within a healthcare organization that would require an improvement team be established to help address the issue. Who do you think should be included as project participants? Why?...
-
Observe the following market data: Silver forward contracts (settled in cash) that mature in 4 months have a forward price of $512 per ounce. Silver forward contracts (settled in cash) that mature...
-
Find the minimum spanning tree
-
A 41 b AB ta B mm D P BC Bar ABC has a rectangular cross-section of a by b, where a = 270 mm and b = 89 mm. Rod BD has a diameter of 17 mm. Both members are made of a material with Young's Modulus of...
-
: What are the key considerations when integrating organizational structures during a merger or acquisition, and how can companies manage cultural differences to ensure a successful transition ?
-
Elle wanted to order candy online. Company A is offering ( 21)/(2) pounds of chocolate for $32.50, while Company B is offering ( 23)/(4) pounds of the same chocolate for $35.00. Which company is...
-
Barnum Stores has six locations. The firm wishes to expand by two more stores and needs a bank loan to do this. Mr Barnum, the banker, will finance construction if the firm can present an acceptable...
-
Determine the required thickness t of the hollow rectangular member for the segment BC of the shaft shown in fig. 5 if the permissible shear stress is Tall = 100 MPa and the shaft should not twist...
-
How would you prepare 50mL of 0.01N solution from question 15 above?
-
A fast-food restaurant averages 150 customers per hour. The average processing time per customer is 90 seconds. a. Determine how many cash registers the restaurant should have if it wishes to...
-
Lawn Master Company, a manufacturer of riding lawn mowers, has a projected income for the coming year as follows: Required 1. Determine the breakeven point in sales dollars. 2. Determine the required...
-
Which of the following costs is not properly characterized as a prevention cost under a COQ reporting system? a. Quality training costs b. Supplier assurance costs c. Testing and inspection costs d....
-
Develop a SWOT analysis for Fowlers Farm based on Problem 2-47. The analysis should include two to three items in each category: strengths, weaknesses, opportunities, and threats. In Problem Joel...
-
Using data from a random sample of elementary schools, a researcher regresses average test scores on the fraction of students who qualify for reduced-price meals. The regression indicates a negative...
-
In the demand curve model of Equation (12.3), is \(\ln \left(P_{i}^{\text {butter }} ight)\) positively or negatively correlated with the error, \(u_{i}\) ? If \(\beta_{1}\) is estimated by OLS,...
-
In the study of cigarette demand in this chapter, suppose we used as an instrument the number of trees per capita in the state. Is this instrument relevant? Is it exogenous? Is it a valid instrument?
Study smarter with the SolutionInn App