Question

Use Excel to fill in the table in the EOQ sheet in 1524Lab5data.xlsx, given the following: annual demand(D)=16000 fixed cost per order(S)=$80 annual holding cost per unit(H)=$6.50 number of units per order(Q)= values from 400 to 1050 in increments of 50 If you begin entering the data with cell A2, which of the following are correct?

Answer 1

To fill in the table in the EOQ sheet in 1524Lab5data.xlsx using Excel, start entering the data with cell A2 and input the values for the number of units per order (Q) from 400 to 1050 in increments of 50. Utilize the EOQ formula in a neighboring column, applying the given values for annual demand (D), fixed cost per order (S), and annual holding cost per unit (H). This will allow Excel to calculate the Economic Order Quantity (EOQ) for each corresponding value of Q.

Step-by-step explanation:

To calculate the Economic Order Quantity (EOQ), the formula
`\(Q^* = \sqrt{(2DS)/(H)}\)` is used, where:

`\(D\)` is the annual demand (16000),

`\(S\)` is the fixed cost per order ($80),

`\(H\)` is the annual holding cost per unit ($6.50).

To apply this formula in Excel, start by entering the values for the number of units per order (Q) in column A, beginning with cell A2 and ranging from 400 to 1050 in increments of 50. In a neighboring column, use the following formula in cell B2 and drag it down for the entire range of Q values:

```excel

=SQRT((2 * D * S) / H)

```

For example, if cell A2 contains 400, the formula in B2 becomes:

```excel

=SQRT((2 * 16000 * 80) / 6.50)

```

By doing this, Excel will automatically calculate the EOQ for each corresponding value of Q. Repeat this process for all values in column A, and the resulting column B will contain the calculated EOQ values.

Starting data entry with cell A2 is crucial for proper organization, as it aligns with Excel's convention of having headers in the first row. This systematic approach allows for an efficient and organized analysis of the EOQ based on the given parameters, facilitating easy interpretation and further decision-making regarding inventory management.