Please show formulas in excel, I want to learn the material as much as possible!
Economic order quantity: This is what happens at your firm that operates 7days/wk, 52 wks/yr.
Your firm uses an average of 2,200 doohickeys every week.
The doohickey vendor has signed a contract for the year, doohickey prices are fixed at $16.00.
The finance manager has sent out a memo stating that recent market conditions have made the firm’s cost of capital 30% per year.
The accounting department has sent out a memo stating that warehouse operations cost $15.00/sqft per year. Five doohickeys take up 10 sq ft of warehouse space.
The risk manager has sent out a memo stating that the firm loses 15% per year on inventory due to shrinkage and obsolescence.
The Quality Department has sent out a memo stating that each order placed requires an inspector to review the documents and incoming items that takes an average of one hour; the average cost to the firm of the QA inspector is $180/hr.
The Purchasing Department has revised the average manhours required to process an order. It now takes a buyer 20 min to process an order and the buyer costs the firm $90/hr.
A) What is the holding cost for one doohickey for one year?
B) What is the cost to place one order based on the items in
C) Make an Exceltm spreadsheet table that has a column showing the annual cost for each of the items in 1)a based on ordering quantities of 1,000, 1,200, 1,400, 1,600, 1,800, 2,000, 2,200, 2,400 at a time. The different order quantities should be in separate rows. Make sure there is a column showing total annual holding costs and another column showing total annual ordering costs for each order quantity.
D) Indicate the Economic Order Quantity by highlighting the row with the quantity closest to the EOQ, based on the sum of the quantity annual holding and ordering cost.
E) Calculate the Economic Order Quantity directly using the appropriate equation in the lecture.
Periodic review inventory system, re-order point question. (NEW TAB IN EXCEL)
This is what happens at your firm, that operates 7 days/week, 52 weeks/year.
You check widget inventory every Friday.
You order more widgets right after inventory-they always arrive on the following Wednesday.
You noticed that every order if added to the Friday on-hand inventory totals 3,418 widgets.
Your firm policy permits accepting more than a 1/100 chance of stocking out of widgets.
In the last 26 weeks, records show that 32,760 widgets have been used.
A) What is the average widget demand over the last year, in widgets per week?
B) How long is the re-order period?
C) How long is the lead time?
D) What is the standard deviation in demand over the Re-order and lead time period?
E) The firm decides that too much is being spent on inventory, so they have eased the 1/100 chance limit to 1/20. What’s the revised re-stocking level going to be now? (text page 293)
Single period inventory system (NEW TAB IN EXCEL)
A) Select a product that fits the description of a single period inventory model product. Name the product .
B) Estimate the unit Sale price and unit Cost of the product. Estimate a Disposal cost and a Salvage cost for each unsold product per unit; added together these two items must be >$0.
C) Makeup a Demand estimate for at least twenty sales periods, i.e. demand for the product for twenty separate periods.
D) Using Excel functions, find the mean and standard deviation of the Demand estimates in 3)
E) Calculate the optimal single period order quantity based on items 3)b through 3)