Need to apply an inventory management model .Given a record of demand as well as the current inventory reorder strategy. This is used to make use of the Cycle Service Level and Safety Inventory concept to see if you can improve the inventory position.

I need a Excel solution for following case-study(Supply Chain Problem):

MEET VISA, GIOVANNI :

Giovanni Visa is a well seasoned supply chain analyst . After completing his last project where he helped Victoria Sagio optimise the truck fleet composition at V360 , he felt like he needed a change . While browsing job offers on Seek and LinkedIn, he came across an interesting role . V - ertilisers , a major fertiliser wholesaler, was offering a six month contract to examine the companys inventory management practices with the intention of replacing the current simple rules of thumb with a more rational and complex planning model.

After completing the interview process, Giovanni hit the ground running and set off to understand the business setting of V -ertilisers . The available data suggested that the company held an average ending inventory of $ 5 . 1 million worth of all raw materials, fertilisers and related by -products, to maintain average monthly sales of $300 ,000 . The challenge was to reduce the inventory levels, while maintaining a high customer service level in sales

V -ertilisers started off as a small local business ran in the Wheatbelt in Western Australia . It was founded by two friends : Sunita and Reuben, who signed a founding agreement in March 1952 . They started the business by investing the entire life savings of their families . On his first day at work, Giovanni was approached by Sunita : We started off by buying premises for a small depot holding only 1000kg of ammonia -based fertiliser . She was very proud of their legacy and success . For 35 years, our firm has been performing well, Sunita stated and it has been recognized by our customers multiple times, by winning the Perth Royal Show for our excellence in performance .

The Australian fertilisers industry was witnessing rising consumer demand due to migration -led increase in population in the country and geopolitical tensions in Eastern Europe, where significant amounts of crops and fertilisers are produced . The agriculture industry in Western Australia is heavily dependent on the weather and the timing of fertilising the soil - even one week of delay can result in multi -million revenue losses due to the sheer size of the farming stations in the Wheatbelt . Therefore, to maintain a high customer service level, V -ertilisers kept high inventory levels .

V -ertilisers did not use any formal analytical techniques to govern ordering and inventory management policies, as confirmed from detailed discussions with Reuben, his employees, and his suppliers agents . Most of the operating decisions of the business were based on simple operating principles combined with managerial intuition . Therefore, the time was right for an analysis and development of advanced planning models .

The company sold two main types of fertilisers: cropping and pasture. Cropping fertilisers offer excellent storage and handling characteristics and ensure that crops get all the essential macro and micro-nutrients they need. Pasture fertilisers contain the ideal balance of phosphorus, potassium and sulfate sulfur to increase farmers pasture production and livestock profitability. In March 2015, to keep pace with technological developments, the main storage area of V-ertilisers was renovated and transformed into a next generation yard with a specialised blender. This enabled a new product branch to grow - specialised horticulture fertilisers created by making custom blends to meet specific soil requirements in different parts of the state, or even within a station. The renovations also increased the inventory capacity and physical space of the business, which increased the number of customers that could be served at one time, as well as the size of business operations in general. Updated machines were installed to serve more customers more efficiently, thus boosting sales.

However, despite all these achievements, V-ertilisers were feeling pressure from growing competition and rising customer expectations, as Sunita explained to Giovanni:

I am sceptical about the efficacy of the operations strategy that I have been using for the past year. I wonder whether there could be a better way to address the demand in order to serve the customers effectively and to maximise my profits at the same time. My main concerns are about the inventory levels of raw materials like ammonia, that should be kept in order to fulfil the demand. What, when and how much to order are the main issues when it comes to determining the purchase policy of the firm.

WORLD -CLASS PRODUCER:

Western Australias (WA) agriculture and food sector is a world -class producer of high -quality, safe agriculture, food and fibre products, vital to our states economy and to feeding the Western Australian community and people in our target export markets . Due to the close proximity to the Outback and the harsh summer climate, fertilising is key to maintaining the current production, or even increasing it .

Our states agrifood sector is well placed to offer premium products that are sustainably and ethically produced, of known provenance and exceptional quality . As well as being the nations largest grain - producing region, Western Australia is a significant producer of Australias meat and livestock, dairy, wool, horticulture and honey products . WA is one of the most pest -free and disease -free agricultural production areas in the world . Strict quarantine requirements ensure this status is maintained for the benefit of local producers and consumers around the globe . This, combined with a focus on innovation, research and development, allows the sector to meet the specific requirements of domestic and overseas customers .

The sector places a high value on overseas markets, with agriculture being WAs second major export industry . Our state exports around 80 per cent of its agricultural production . In 2016 /17 , WA exported an estimated $ 8 . 5 billion in agriculture and food products . The top three products exported were wheat, canola and barley . For the past decade, about 70 per cent of the states agrifood exports has been destined for Asia, with China, Indonesia and Vietnam some of our largest markets . With growing demand for premium agrifood products, especially in Asia, WA is in a good position to build on its reputation as a reliable supplier of clean, safe and high -quality food to overseas markets .

The WA agriculture and food sector continues to be the lifeblood of rural and regional communities . It is a significant source of employment, with the broader sector providing jobs for 188 ,400 people in 2016 /17 .

THE SUPPLY CHAIN:

Raw materials for V-ertilisers products are delivered from mine sites directly to the processing plant located at the company premises. As its a vertically integrated business, this is also the place where distribution to the end customers occurs. Customers place their orders directly with the business through an automatic order-retrieval system, by either sending a text message through a cell phone or accessing the suppliers web-based portal. This order is automatically converted to a sales order in the CRM software. The business operates completely on a cash-and carry basis, which means that no credit is provided by Vertilisers to its customers. The business mandates receipt of payment through an online banking payment system before making a delivery.

Given the business is located in Western Australia, there is plenty of space to store the goods. After talking to the yard manager, Giovanni learnt that the maximum yard capacity is 15,000 tonnes of raw material and finished products. Inventory ordering had to follow a procedure known as a pre-planned delivery system. Essentially, the procedure required that a raw-material order is placed before 10:00 am Monday, allowing for 6 full days before delivery to the main yard. Within the following 24 hours, the raw material is ready to be transformed into finished products. These are then dispatched to the end clients, based on approved and paid orders. Current pricing for each fertiliser type is fixed by long term, half-yearly contracts that have been negotiated 1 month before Giovanni joined the business

To oversee business operations, V-ertilisers employed a full-time manager, who was responsible for inventory management. Both inventory management and ordering policies were based on a set of basic and simple assumptions. The sales forecast for a coming week was assumed to be an 8 weeks moving average of the historical weekly demand. Based on the macroeconomic trends and the aggressive targets set by the board, sales were expected to rise somewhat each month, which served for planning purposes.

V-ertilisers planned its raw material orders according to a simple operating policy. Each Mondays opening inventory (i.e. the previous weeks closing stock plus the order received on Sunday) was expected to meet, or exceed the forecasted demand for the following 4 weeks. If that condition was not met, a new purchase order was placed before 10am, with a fixed amount of 1,300 tonnes at $740 per tonne. The order amount was optimised by Sunita and Reuben and constituted the most cost efficient scenario

The companys goal was to minimise the chance of running out of raw material stock. Every tonne of unmet demand was estimated by the finance team to be associated with a cost equal to 70% of the material price. Inventory storage costs were not directly accounted for in the above process, although the company confirmed that the cost of capital used to procure stock could be estimated at 10% per year, or 0.19% per week.

Alright Giovanni, now is your time to shine, said Sunita on Giovannis second day at work. Lets make it easier for you and start small. Please focus your work only on ammonia at the main yard. We can worry about the rest of the business in a fortnight.

Giovanni wondered how to include some measures and criteria in the business that might guide V-ertilisers to make better purchase decisions. He also needed to determine what critical points needed to be evaluated before coming up with any kind of inventory management policy, and what planning horizon was most suited for this type of problem.

On May 1st, 2023 Giovanni was provided access to the weekly demand data for the previous 39 weeks. The opening stock of ammonia inventory on Monday 06.09.2021 was 2,858.877 tonnes. With all this information, his task was to develop an inventory rationalisation model that the wholesaler could adopt to manage its inventory planning for the future. His gut feeling was that other forecasting models should perform better than the current moving average estimate. Giovanni also planned to change how the restocking point is calculated. By varying the Service Level Agreement (95%, 99%, 99.9%) he would perform a sensitivity analysis of total inventory costs and stockout costs compared to the current operating policy.

Following is the Data File: