Henri of Henri’s French Cuisine (HFC), a chain of twelverestaurants, is trying to decide if it makes sense to outsource thepurchasing function. Currently, Henri employs two buyers at anannual fi xed cost of $85,000. Henri’s estimates that the variablecost of each placed purchase order is $15. Value-Buy (VB), a groupof purchasing specialists, will perform the purchasing functionfor a fi xed annual fee of $100,000 plus $5 for each purchase orderplaced. Last year, HFC placed 1450 purchase orders.(a) Construct a base-case spreadsheet model that shows bothof these alternatives side-by-side. Use the same number oforders (stored in a single cell) to drive the calculations.(b) Using last year’s quantity of purchase orders, whichalternative would have been the better?(c) Use Goal Seek to fi nd the indiff erence point (where totalcosts are equal) between these two alternatives. (Hint: GoalSeek can work only with a single “Set cell.” Create a cell inyour model that computes the diff erence between the totalcosts of the two alternatives before running Goal Seek.)(d) Construct an XY chart of total cost versus quantity. Graphboth alternatives on the same chart. Use a Data Table togenerate the values for the chart. (Hint: Th e Data Tablewill need to track two output values: the total costs ofthe two alternatives. When setting up the Data Table, itwill simply have an additional column, compared to theexample presented in the text.)
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 fi xed 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 fi xed 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 fi nd the indiff erence point (where total
costs are equal) between these two alternatives. (Hint: Goal
Seek can work only with a single “Set cell.” Create a cell in
your model that computes the diff erence between the total
costs of the two alternatives before running Goal Seek.)
(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. (Hint: Th e Data Table
will need to track two output values: the total costs of
the two alternatives. When setting up the Data Table, it
will simply have an additional column, compared to the
example presented in the text.)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
