FNSACC511 Provide Financial and Business Performance - Product Assessment Task

Product Assessment Task

Part B: Short Case Studies

Question 1
Dareduna Limited Case Study
Your client Dareduna Limited has had inventory management and ordering issues for an item that they stock- bond007. They have asked you to assist them in understanding the relevant costs associated with the item and establish an Economic order quantity for the item. You need to review existing arrangement and suggest desired re-order point before the next financial year.

Dareduna Limited has provided you with the following data for item bond007:
Inventory usage rate is 100 per week
Safety stock level is: 80 units
Annual demand is 5000 units in total (assume 52 weeks)
Lead time is 3 weeks
Unit price $2
Order cost $ 6.5 per order
Carrying costs $0.35 per unit per year

You are required to complete the following:

1. Calculate EOQ
2. Calculate the desired re-order point
   You are required to insert the above data into a spreadsheet clearly labelling all cell references as well as worksheets. Please use formulas/ functions to perform all mathematical calculations in the worksheet clearly showing all workings. Please note your work will be cross-checked against the input you have put in the cells.
3. Create an EOQ chart out of this data using a macro (You will be required to assume Order quantities in multiples of 100’s). Select this table and chart and print only to *.pdf format and save. Submit along with other files on CANVAS by the due date. Explain how this macro feature would assist the client in the future?
4. From the data table, find out how much the Total relevant cost is at an order quantity of 630 unit using the HLOOKUP function?
5. Suggest ways in which your client can implement the EOQ and relay this information in the form of a short report to Dareduna Limited. (200 words)