Using the HW7 Starting File Excel file, answer the following problems (You must use the HW7 Starting File for credit)

Problem 1. (100 pts. Each part is worth 25 pts) Henri of Henris 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 $45,000 per employee. Henris 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 last year and averages $182 income per order.

(a) Construct a base-case spreadsheet model that shows both of these alternatives side-by-side. Keep in mind good spreadsheet modeling techniques. Use the same number of orders (stored in a single cell) to drive the calculations. What is the better option? Input your response in J16

(b) Using last years quantity of purchase orders, which alternative would have been the better? Type your answer in the space provided in Excel (Cell J16)

(c) Use Goal Seek to find the indifference point or crossover 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 difference between the total costs of the two alternatives before running Goal Seek. Make this cell K4). Input the number of orderst o breakeven in K5 and return the orders processed (C4) to its original value.

(d) Henri is considering adding another location. He is considering the following factors: Curb Appeal, Population, Median Income, Local Suppliers and Square footage available. Each factor has been assigned a weight. These three locations have been considered and have been given scores for each factor. The table is below:

Factor	Weight	Atlanta	Birmingham	Chicago
Curb Appeal	0.2	75	60	55
Population	0.15	35	75	75
Median Income	0.30	85	80	60
Local Suppliers	0.10	40	60	90
Square footage	0.25	50	40	55

Use the Function (SUMPRODUCT) to figure the score of each location. Use Conditional Formatting to identify the highest score.

Additional instructions: Your submission must be an EXCEL file; put your solution to each part on a separate tab of the EXCEL workbook.

For part (a), make sure that you include a textbox containing at least a one-sentence response to the question asked. For part (c), provide a screenshot (or Snipping Tool capture) of the Goal Seek Dialog Box. Run Goal Seek before taking the screenshot. How to screenshot on a Mac How to screenshot on a PC Must be submitted as a Microsoft Excel workbook.

.. AutoSaver APO". HW7 Starting File(2) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments v AA ah Wrap Text Insert v Delete w 27.0 IA Pasie Bi Uw v A = = = Merge & Center v $ %. Conditional Format Cell Formatting as Table Styles Sensitivity Sort Filter Format v Find & Select Analyze Data A1 X fx OM 300 HW 7 B B C E F G H T K L M N 0 P Q R OM 300 HW 7 To create and analyze Henri's French Cuisine outsource and location decision 2 3 Author 4 Date Purpose 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 A Documentation Question a,b,c - Question d + Ready + 120% .. AutoSave UPF AOFO ... HW7 Starting File(2) - Saved to my Mac Qe Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments v AA Wrap Text 2 Insert v Dslats 4 w 2Y V Paste B1 w Merge & Center & $% Sensitivity Conditional Format Cell Formatting es Table Styles Format v Sort & Filter Find & Select Analyze Data Y A1 OM 300 HW 7 C D E H I K L M N. N 0 P 2 R OM 300 HW 7 Goal Sesk Sedl How many orders are? Include your Goal Seek screen shot here: Outsource with Value Buy Annual Fixed Cust Unit Variable Cost per order What isthe better option? 3 Inputs 4 Orders prosessed Ist year 5 In house Salary per yes 8 Number of buyers 9 Unit Variable Cost per order 10 11 Average Revente per arder 12 13 Calculations and Outputs 14 Annual Revenue 15 Annual variable costs 16 Annuel fixed Cost 17 Annual Total Com 12 Annual Pronto) 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Annual Revenue Annual Variable Costs Annual Fixed Cust Annual Total Cost Annual Profit (LOSSI Documentation a Question a,b,c Questions + Ready 0 + 100% . AutoSave OFF A@FO... HW7 Starting File(2) - Saved to my Mac Insert Home - Draw Page Layout Formulas Data Review View Tell me A Share Comments Insert v v AA = 2 Wrap Text AYO. Paste 1 Merge & Center % 5 Conditional Format Cell Formatting es Table Styles Dalata L! Format Sort & Filter Find & SAlect Analyze Data Sensitivity B25 SX B B C D E F G . I J L M N O P Q R R 5 U V 2 3 Atlanta 75 Birmingham 60 4 Weight 0.2 0.15 03 35 Factor Curb Appeal Population Median Income Local Suppliers Square footage 75 Chicago 55 75 GD 90 55 90 85 40 50 0.1 0.25 LD 40 Score for each location Use SUMPRODUCT to figure each score Use Cond Bonal Formatting to identity the highest.com 9 10 11 12 13 14 15 16 17 12 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Documentation a Question a,b,c Question d Ready FI + 100% %