XYZ company wants to efficiently plan their future purchases, for its top 10 critical items, to be able to minimize their inventory costs and manage cash flow without jeopardizing their operations. Below is a table and a list of assumptions that used to determine/plan Mueller Middle Easts future purchases.

Assumptions:

5000 T of copper coils produced between 01/01/2022 and 31/07/2022

Current inventory levels are enough for production until items will run out of stock at 31/12/2022. Hence, XYZ company solely depends on newly purchased items from 01/01/2023

Assume no maintenance activities will take place in the years 2023 and 2024

Forecasted production for the year 2023 is 12,000T (1000T/month)

Forecasted production for the year 2024 is 24,000T (2000T/month)

XYZ company would want to have enough inventory levels for the full year of 2024 (24,000T of production) on 01/01/2024 i.e. no purchases should be made in the year 2024 and assume inventory levels to reach Zero by 31st December 2024.

Consumption rates of production consumables are directly proportional to production volumes.

Office supplies have no relationship to production volumes and are ordered once a year.

Deliverables:

Using the information provided above calculate in Excel the below (for the 10 items) for every month starting from Aug 2022 Jan 2024.

Quantity to purchase total during the month.

Cost of goods purchased total during the month.

Inventory on-hand At month End

Cost of Inventory on-hand At month End

Please include a paragraph listing

Recommended order frequency

All assumptions and calculations used

Any other recommendations