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. 5 Problem6 Your company is concerned that too much working capital is tied7 up in inventory, but at the same time are concerned stockouts.8 While many companies reorder inventory items based on the9 number of units they think they will need, a more cost-effective10 method to determine the optimal order quantity is accomplished11 by calculating the economic order quantity (EOQ). The costs to be12considered are holding costs consisting of storage facility costsand related labor costs; and order costs which consist of shipping14 and handling costs. One-half of the inventory is on hand at anypoint in time, and demand is relative even across time. Thecompany's inventory data and assumed possible order quantities131516| L17Inventory cost and unit data181920212223Annual demand (D)Order cost per order (S)Inventory cost per unitHolding cost per unit (H)Cost of borrowing rateUnit Cost Units Rate2,250$5002502510%Student Work Areaa. EOQ calculationEOQb. Table for part b300 2324252627282930313233343536373839404142Cost of borrowing rateAssumed quantities ordered per year5010015020025030035040045050055060065070075080085010%b. Table for part bOrderQuantity50100150200250300350400450500550600650700750800850Orders perYearAnnualAnnualOrder Cost Holding CostAnnual TotalCost

using the same formula for EOQ as instructed. a.

Answered: Instructions There are four parts to…

Practical Management Science

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

See similar textbooks

Related questions

Question

100%

Instructions

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