Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please Show Formulas In Excel In The Yellow Boxes Known Information Supplier A 25000 Supplier B 3000 Supplier C 1000 Monthly Demand Annual Demand Holding
Please Show Formulas In Excel In The Yellow Boxes
Known Information Supplier A 25000 Supplier B 3000 Supplier C 1000 Monthly Demand Annual Demand Holding Cost %, h Unit Cost=C$ Fixed Order Cost (Transportation) $ Fixed Order Cost (receiving & Sorting) $ Annual Holding per Unit=h*C 26.00% 6.00 $ 2,300.00 $ 150.00 $ 30.00% 4.00 $ 2,300.00 $ 75.00 $ 20.00% 8.00 2,300.00 120.00 Total Cost Part 1: Independent Order Analysis Supplier A Supplier B Supplier C EOQ Cycle Inventory (Avg. Inv.) Annual Holding Cost Order Frequency Annual Ordering Cost Total Annual Order and Setup Costs Part 2: Joint Ordering Replenishment Analysis Additional Stop Charges (Add into S*) $ 600.00 Aggregated Order Cost (S*) Optimal Number of Aggregated Orders (n*) Rounded Number of Aggregated Orders (n*) Order Quantity by Product (D*) Cycle Inventory (Avg. Inv.) Annual Holding Cost Annual Ordering Cost Total Annual Order and Setup Costs Cost Savings Due to Aggregation (Joint Replenishment) Total Cost) Question 1 What are the Total Annual Order and Setup Costs ($) of the current independent order strategy? Question 2 What are the Total Annual Order and Setup Costs ($) of the proposed aggregated order strategy? Question 3 How many orders per year will Harley place if they adopt the aggregated ordering strategy? (Round your to the nearest whole number of orders.) Question 4 What would the Fixed Order Cost (Transportation) need to change to for the two strategies to have the same Total Annual Order and Setup Costs ($)? (Use Goal Seek to solve this question.) Question 5 Harley would prefer to place 12 orders per year (i.e., once per month.) What would the Fixed Order Cost (Transportation) need to change to so that the Optimal Number of Aggregated Orders (n") is equal to 122 (Use Goal Seek to solve this question.)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started