Are my formulas correct and did I get the correct answer?Question: Sunblessed Juice Company sells bags of oranges and cartons of orange juice. Sunblessed grades oranges on a scale of 1 (poor) to 10 (excellent). At present, Sunblessed has 220,000 pounds of grade 6 oranges and 150,000 pounds of grade 9 oranges on hand. The aver- age quality of oranges sold in bags must be at least 7, and the average quality of the oranges used to produce orange juice must be at least 8. Each pound of oranges that is used for juice yields a revenue of $2.25 and in- curs a variable cost (consisting of labor costs, variable overhead costs, inventory costs, and so on) of $1.35. Each pound of oranges sold in bags yields a revenue of $2.00 and incurs a variable cost of $1.20.a. Determine how Sunblessed can maximize its profit. ### Selling Oranges and Orange Juice Analysis**Revenues and Variable Costs**- **Juice** - **Revenue per unit**: $2.25 - **Variable cost per unit**: $1.35- **Bags** - **Revenue per unit**: $2.00 - **Variable cost per unit**: $1.20**Grades Used to Make Juice and Bags**| Grade | Juice | Bags | Used | Available ||--------|------------|--------------|----------|-----------|| 6 | 26666.666 | 193333.328 | 220000 | <= 220000 || 9 | 53333.332 | 96666.664 | 150000 | <= 150000 || **Sold** | **80000** | **289999.992** | | |**Required Average Quality**| | Juice | Bags ||----------|-------|------|| **Average** | 8 | 7 |**Quality Constraints**| | Juice | Bags ||----------|---------|----------------|| **Actual** | 640000 | 2029999.944 || **Required** | 640000 | 2029999.944 |**Cost and Revenue Summary**- **Total Variable Cost**: $456,000- **Total Revenue**: $760,000- **Profit**: $304,000**Range Names and Formula References**- **Available**: =Model!$F$10:$F$11- **Mix**: =Model!$B$10:$C$11- **Profit**: =Model!$B$27- **Quality**: =Model!$B$20:$C$20- **Required**: =Model!$B$22:$C$22- **Used**: =Model!$D$10:$D$11Understanding the distribution and cost analysis for producing juice and juice bags enables informed decision-making to maximize profit while meeting quality standards. The analysis covers revenue, costs, quality constraints, and the overall profit margin. ### Selling Oranges and Orange Juice - Excel Model Breakdown#### OverviewThis exhibit is a screenshot of an Excel spreadsheet model for analyzing the sales of oranges and orange juice. The model includes revenues, variable costs, grades of oranges used, quality constraints, and profit calculations. Below we describe each section of the spreadsheet.#### Revenues and Variable Costs- **Columns C to E**: Different product types, labeled as "Juice" and "Bags".- **Rows 2 - 4**: - Revenue and Variable Cost per unit. - Juice revenue is $2.25 per unit. - Juice variable cost is $1.35 per unit. - Bags revenue is $2 per unit. - Bags variable cost is $1.20 per unit.#### Grade and Amount Sold- **Rows 6 - 13**: Evaluation of orange sales by grade. - **Grade** specifies the quality or category of oranges. - **Sold** indicates the number of units sold for "Juice" and "Bags" products. - For instance, Grade 1 oranges sold for Juice total 26,666.666 units, and for Bags, 193,333.328 units. - Summation Functions: - `SUM(B10:B11)` & `SUM(C10:C11)` compute total units sold.#### Quality Constraints- **Rows 15 - 21**: Assess average required quality. - **Required Average Quality**: - 8 for Juice. - 7 for Bags. - **Actual**: - Calculated by summing up products of sold units and grades. - `SUMPRODUCT` functions used to compute actual quality: - For example: `=SUMPRODUCT($A$10:$A$11,B10:B11)`.#### Total Costs, Revenues, and Profit- **Row 23**: Total Variable Cost computation. - For Juice: `=SUMPRODUCT(B6:C6,B12:C12)` - For Bags: `=C12*C16`- **Row 24**: Total Revenue computation. - For Juice & Bags: `=SUMPRODUCT(B5:C5,B12:C12)`- **Row 25**: Profit computation: - `=B24-B23`.### Additional Components- **Range Names (F to G)** -

Upon analyzing the question requirements, a spreadsheet was formulated by observing the template but…

Answered: Are my formulas correct and did I get…

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...