Henri of Henri’s French Cuisine (HFC), a chain of twelve restaurants, is trying to decide if it makes sense to outsource the purchasing function. Currently, Henri employs two buyers at an annual fixed cost of $85,000. Henri’s estimates that the variable cost of each placed purchase order is $15. Value-Buy (VB), a group of purchasing specialists, will perform the purchasing function for a fixed annual fee of $100,000 plus $5 for each purchase order placed. Last year, HFC placed 1450 purchase orders.

(a) Construct a base-case spreadsheet model that shows both of these alternatives side-by-side. Use the same number of orders (stored in a single cell) to drive the calculations.

(b) Using last year’s quantity of purchase orders, which alternative would have been the better?

(c) Use Goal Seek to find the indifference point (where total costs are equal) between these two alternatives.

(d) Construct an XY chart of total cost versus quantity. Graph both alternatives on the same chart. Use a Data Table to generate the values for the chart.