please help me on all questions. thank you!

**please help me with all parts of this homework. thank you!!!

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.20 75 60 5

OM300: HW7 Due 7/9 at 11:59 pm CST (No Late Work Accepted) Using the "W7 Starting File Excel file, answer the following problems (You must use the "HW7 Starting Fle" for credit) Problem 1. (100 pts. Each part is worth 25 pts) 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 $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 5182 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 year's 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 (whore total costs are equal) between these two alternatives. (Hint Goal Seck 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 Syck. Make this cell K4). Input the number of onderst o breakeven in K5 and return the orders processed (C4) to its originallyalue. (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 Curb Appeal Population Median Income Local Suppliers Square footage Weight 0.20 0.15 0.30 0.10 0.25 Atlanta 75 35 85 40 50 Birmingham 60 75 80 60 40 Chicago SS 75 60 90 55 Use the Punction (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 separato tab of the EXCEL workbook For part(), make sure that you include a textbox containing at least one-sentence response to the question asked For purt (e), provide a screenshot (or Snipping Tool capture) of the Goal Seek Dialog Box Run Goal Seek before taking the screenshot w usta latama Must be submitted as a Microsoft Excel Workbook MacBook Pro Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? B4 x x A B D E G H OM 300 HW 7 1 2 3 Author 4 Date Purpose To create and analyze Henri's French Cuisine outsource and location decision 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 A Documentation A Question ab.c A Question d Ready Xfx OM 300 HW 7 OM 300 HW 7 Inputs Order proced Goal Sel Set Call How many three Include Wur Goran hot hare In house Salary berar Number of L V HNH CHI Der Outsource with Value Buy Annual Com Unit Variable pere 90 11 Merover Calonu 18 15 16 inte Matic AC Annualco AL Menu Marca Ahow och w sin 23 12 1 2 25 Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you lef B25 vfs D E F H 1 2 3 Weight 0.2 Atlanta 75 Birmingham 60 Chicago 55 4 5 0.15 35 75 75 Factor Curb Appeal Population Median Income Local Suppliers Square footage 6 0.3 0.1 85 40 80 60 60 90 7 8 0.25 50 40 55 9 Score for each location 10 11 12 13 14 15 16 Use SUMPRODUCT to figure each score Use Conditional Formatting to identify the highest score 18 20 21 24 25 27 30 15 Dentation Question abus Question OM300: HW7 Due 7/9 at 11:59 pm CST (No Late Work Accepted) Using the "HW7 Starting Fle" Excel file, answer the following problems (You must use the "HW7 Starting Fle" for credit) Problem 1. (100 pts. Each part is worth 25 pts) 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 $45,000 per employee. Henris estimates that the variable cost of each placed purchase order is $15. Value Bay (VB), a group of purchasing specialists, will perform the purchasing function for a fixed annual fee of $100,000 plus ss for each purchase order placed Last year, HPC 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 year's quantity of purchase orders, which alternative would have been the better? Type your answer in the space provided in Excel (Cell J16) () Use Goal Seck 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 Syck. Make this cell K4). Input the number of orderst o breakeven in K5 and return the orders processed (C4) to its originaltvalue. (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: Weight 0.20 Factor Curb Appeal Population Median Income Local Suppliers Square footage 0.15 0.30 0.10 0.25 Atlanta 75 35 85 40 50 Birmingham 60 75 80 60 40 Chicago SS 75 60 90 55 Use the Punction (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(), make sure that you include a textbox containing at least a one-sentence response to the question asked For purt (c), provide a screenshot (or Snipping Tool capture) of the Goal Seek Dialog Box Run Goal Seek before taking the screenshot Iwona ma Must be submitted as a Microsoft Excel Workbook MacBook Pro Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left oft? B4 + x V fx A C D E G H OM 300 HW 7 1 2 3 Author 4 Date Purpose To create and analyze Henri's French Cuisine outsour and location decision 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 A Documentation Question ab.c A Question d Ready xfxOM 300 HW 7 OM 300 HW 7 inputs Order proced Goal Soal Set Call How many Include Wur Goran hot hare In house Salary ber Number of LVC pero Outsource with Value Buy Med Com Unit Variable per Rever 30 11 12 3 Caution wou 16 15 16 Annual Core A Annual PL More Allow 15 12 2 29 10 Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you lef B25 x fx 8 D E F H 1 2 3 Weight 0.2 Atlanta 75 Birmingham 60 Chicago 55 4 5 0.15 35 75 75 Factor Curb Appeal Population Median Income Local Suppliers Square footage 6 0.3 0.1 85 40 80 60 60 90 7 0.25 50 40 55 8 9 Score for each location Use SUMPRODUCT to figure each score Use Conditional Formatting to identify the highest score 10 11 12 13 14 15 16 12 18 19 20 21 22 24 27 20 25 31 15 35 tonentation Question OM300: HW7 Due 7/9 at 11:59 pm CST (No Late Work Accepted) Using the "W7 Starting File Excel file, answer the following problems (You must use the "HW7 Starting Fle" for credit) Problem 1. (100 pts. Each part is worth 25 pts) 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 $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 5182 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 year's 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 (whore total costs are equal) between these two alternatives. (Hint Goal Seck 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 Syck. Make this cell K4). Input the number of onderst o breakeven in K5 and return the orders processed (C4) to its originallyalue. (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 Curb Appeal Population Median Income Local Suppliers Square footage Weight 0.20 0.15 0.30 0.10 0.25 Atlanta 75 35 85 40 50 Birmingham 60 75 80 60 40 Chicago SS 75 60 90 55 Use the Punction (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 separato tab of the EXCEL workbook For part(), make sure that you include a textbox containing at least one-sentence response to the question asked For purt (e), provide a screenshot (or Snipping Tool capture) of the Goal Seek Dialog Box Run Goal Seek before taking the screenshot w usta latama Must be submitted as a Microsoft Excel Workbook MacBook Pro Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? B4 x x A B D E G H OM 300 HW 7 1 2 3 Author 4 Date Purpose To create and analyze Henri's French Cuisine outsource and location decision 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 A Documentation A Question ab.c A Question d Ready Xfx OM 300 HW 7 OM 300 HW 7 Inputs Order proced Goal Sel Set Call How many three Include Wur Goran hot hare In house Salary berar Number of L V HNH CHI Der Outsource with Value Buy Annual Com Unit Variable pere 90 11 Merover Calonu 18 15 16 inte Matic AC Annualco AL Menu Marca Ahow och w sin 23 12 1 2 25 Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you lef B25 vfs D E F H 1 2 3 Weight 0.2 Atlanta 75 Birmingham 60 Chicago 55 4 5 0.15 35 75 75 Factor Curb Appeal Population Median Income Local Suppliers Square footage 6 0.3 0.1 85 40 80 60 60 90 7 8 0.25 50 40 55 9 Score for each location 10 11 12 13 14 15 16 Use SUMPRODUCT to figure each score Use Conditional Formatting to identify the highest score 18 20 21 24 25 27 30 15 Dentation Question abus Question OM300: HW7 Due 7/9 at 11:59 pm CST (No Late Work Accepted) Using the "HW7 Starting Fle" Excel file, answer the following problems (You must use the "HW7 Starting Fle" for credit) Problem 1. (100 pts. Each part is worth 25 pts) 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 $45,000 per employee. Henris estimates that the variable cost of each placed purchase order is $15. Value Bay (VB), a group of purchasing specialists, will perform the purchasing function for a fixed annual fee of $100,000 plus ss for each purchase order placed Last year, HPC 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 year's quantity of purchase orders, which alternative would have been the better? Type your answer in the space provided in Excel (Cell J16) () Use Goal Seck 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 Syck. Make this cell K4). Input the number of orderst o breakeven in K5 and return the orders processed (C4) to its originaltvalue. (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: Weight 0.20 Factor Curb Appeal Population Median Income Local Suppliers Square footage 0.15 0.30 0.10 0.25 Atlanta 75 35 85 40 50 Birmingham 60 75 80 60 40 Chicago SS 75 60 90 55 Use the Punction (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(), make sure that you include a textbox containing at least a one-sentence response to the question asked For purt (c), provide a screenshot (or Snipping Tool capture) of the Goal Seek Dialog Box Run Goal Seek before taking the screenshot Iwona ma Must be submitted as a Microsoft Excel Workbook MacBook Pro Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left oft? B4 + x V fx A C D E G H OM 300 HW 7 1 2 3 Author 4 Date Purpose To create and analyze Henri's French Cuisine outsour and location decision 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 A Documentation Question ab.c A Question d Ready xfxOM 300 HW 7 OM 300 HW 7 inputs Order proced Goal Soal Set Call How many Include Wur Goran hot hare In house Salary ber Number of LVC pero Outsource with Value Buy Med Com Unit Variable per Rever 30 11 12 3 Caution wou 16 15 16 Annual Core A Annual PL More Allow 15 12 2 29 10 Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you lef B25 x fx 8 D E F H 1 2 3 Weight 0.2 Atlanta 75 Birmingham 60 Chicago 55 4 5 0.15 35 75 75 Factor Curb Appeal Population Median Income Local Suppliers Square footage 6 0.3 0.1 85 40 80 60 60 90 7 0.25 50 40 55 8 9 Score for each location Use SUMPRODUCT to figure each score Use Conditional Formatting to identify the highest score 10 11 12 13 14 15 16 12 18 19 20 21 22 24 27 20 25 31 15 35 tonentation