InstructionsThere are four parts to this problem. Use Excel to perform the following.a. Use the economic order quantity formula (EOQ = SQRT((2SD/H)) to determine the optimal number of units that the company should order based on each assumed level of order based on each assumed level of order quantities provided in the data.b. Complete the table by calculating the number of orders per year, annual order cost, annual holding cost, and annual total cost. Highlight the minimum annual total cost using conditional formatting. Hint: The minimum cost should equal the cost at the EOQ you calculated in part a.c. Create a line chart that graphs annual order cost, annual holding cost, and annual total cost. The x-axis should be the quantity ordered. Include a chart legend, appropriate chart title, axes labels, and properly formatted amounts on the axes. d. Examine the chart and your responses to parts a and b. Indicate any relationships. ## ProblemYour company is concerned that too much working capital is tied up in inventory, but at the same time are concerned stockouts. While many companies reorder inventory items based on the number of units they think they will need, a more cost-effective method to determine the optimal order quantity is accomplished by calculating the economic order quantity (EOQ). The costs to be considered are holding costs consisting of storage facility costs and related labor costs; and order costs which consist of shipping and handling costs. One-half of the inventory is on hand at any point in time, and demand is relative even across time. The company's inventory data and assumed possible order quantities are presented below.### Inventory Cost and Unit Data| | Unit Cost | Units | Rate ||-----------------|------------|-------|------|| Annual demand (D) | | 2,250 | || Order cost per order (S) | $500 | | || Inventory cost per unit | 250 | | || Holding cost per unit (H) | | | 25 || Cost of borrowing rate | | | 10% |### Student Work Area#### a. EOQ CalculationEOQ = 300#### b. Table for Part B(Details of part b table not provided in the image) The image displays a spreadsheet layout related to inventory management, specifically focusing on order quantities and associated costs. It includes a completed section listing assumed quantities ordered per year, and a table labeled "b. Table for part b" which is meant for calculating various costs. Below is a detailed transcription and description of the content and structure:### Text Content- **Cost of borrowing rate:** 10%- **Assumed quantities ordered per year:** - 50 - 100 - 150 - 200 - 250 - 300 - 350 - 400 - 450 - 500 - 550 - 600 - 650 - 700 - 750 - 800 - 850### Table Structure (b. Table for part b)The table is intended to analyze various order quantities and their impact on costs. It includes the following columns:1. **Order Quantity**2. **Orders per Year**3. **Annual Order Cost**4. **Annual Holding Cost**5. **Annual Total Cost**Currently, the table is set up but lacks data entries for the described categories. The areas under "Annual Order Cost," "Annual Holding Cost," and "Annual Total Cost" are intended for calculations, which will depend on the assumed quantities and the cost of borrowing rate specified.### ExplanationThis setup is typically used in economic order quantity (EOQ) calculations to determine the optimum order quantity that minimizes total inventory costs, which are composed of order costs, holding costs, and stock outages. Students or analysts would fill in the table using formulas or data based on inventory management principles, considering factors such as order frequency and carrying costs, to find the most cost-effective ordering strategy.This structured analysis helps businesses or students understand the trade-offs between order size and frequency, ultimately guiding toward efficient inventory management practices.

using the same formula for EOQ as instructed. a.

Answered: Instructions There are four parts to…

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...