Anonymous
timer Asked: Apr 26th, 2020

Question Description

This is due on Wednesday. It's an excel based assignment. The questions are explained in the word file. And for some questions, the data is given in the excel file.

Unformatted Attachment Preview

20200426171107icarbajo Exam #2 BUS2300 Spring 2020 Excel formulations should be in the same Excel file, with a different sheet for each problem. The essay should be a separate Word or PDF document. All files should be turned in via Canvas. The work on this exam must be done individually; any evidence of collaboration will result in a 0 on the exam. Late work will not be accepted. Exam is due before 11:59 pm on Thursday, April 30th. 100 points This exam is setup to test your ability to apply the concepts we have learned in chapters 2, 3, 6 and 9. All seven questions relate to a real business that has recently hired you because of your extensive Management Science background. If you have any questions concerning the exam they can be addressed to Mr. Woods. 20200426171107icarbajo Scenario: You have just been hired by the Marinette Marine in Marinette, Wisconsin. Marinette Marine is a subsidiary of the Fincantieri Group, one of the world’s largest designers and constructors of merchant and naval vessels. They have asked to solve the following business problems because of your extensive decision science education. Question 1: 15 points Marinette Marine is part of the Fincantieri Marine Group which also includes Bay Shipbuilding and Ace Marine. Fincantieri Marine Group is looking at creating a new parts manufacturing and distribution facility in Asia to shorten their supply chain for Asia. They currently have a presence in Vietnam, so this is not a potential candidate. They are interested in countries that have a good physical infrastructure (especially ports), trainable workforce, little corruption, and minimal trade barriers. Management has provided you with a subset of applicable data for the last 5 years from the World Economic Forum Global Competitiveness database and it is found in the Question1 tab of your Excel file. Management would like to select a country that is seeing a positive trend in the data you are using in the analysis. Use Pivot Tables to analyze the data and select 3 to 5 country finalists and a single country recommendation for the new facility. Question 2: 15 points The finance team is looking at some various CD and savings options to create a sinking fund to pay for a planned facility expansion. They are going to need $175,000 at the end of month 3, $160,000 at the end of month 4 and $120,000 at the end of month 6. Marinette has been working with 2 local banks; the following table outlines their current return on various investments. (Blanks note that that option is not available at that bank.) Return Term (months) 1 2 3 4 5 6 Bank 1 0.87% 1.78% 2.55% 3.78% 6.50% Bank 2 0.85% 1.90% 3.92% 5.40% 7.08% The 1-month investments noted in the table above each have a setup fee, Bank 1 has a $400 fee per investment, Bank 2 charges $600. This fee is charged at the beginning of each investment. Create an Excel model to determine the minimum amount put into this sinking fund to meet the required payments. 20200426171107icarbajo Question 3: 15 points The Fincantieri management team is very interested in the tools and techniques you covered in your Quantitative Problem Solving class. Your manager has asked to you write a 1 to 2-page double spaced paper outlining some of the tools and techniques they could use to answer their business problems in the other product groups. Research Fincantieri’s other product groups (http://www.fincantieri.it/) and provide 2 specific examples of how concepts covered in this class (but not covered in this exam) that would apply to another product group. Make sure to note how these examples would impact their financial performance (costs, profits, etc.) The focus should be on why Fincantieri should model business decisions, not a step-by-step set of instructions on how to model. Attach a separate document in Canvas; do not include it in your Excel file. Question 4: 15 points The superstructure production team needs some help understanding what their optimum outsourcing strategy should be. They have three main operations: welding, painting and assembly. Each operation must occur in order to produce a superstructure. They can outsource the welding, painting or both, but must do the assembly internally. The following table describes the hours required for each basic superstructure type, available hours, demand, and revenue received for each superstructure for the next two-week planning period. Hrs required Welding Painting Assembly Demand Revenue each Alpha 6.3 3.5 6.9 21 $3,600 Beta 7.3 5 9.1 36 $4,100 Gamma 7.8 5.9 7.2 15 $4,900 Available hrs 488 300 612 Their outsourcing option has given them the following cost per unit for welding and painting. Welding Painting Alpha $447 $408 Outsource Cost per unit Beta Gamma $550 $587 $428 $435 Develop an Excel model and solve to maximize their net revenue (revenue-outsourcing cost) and meet the constraints. Question 5: 15 points Marinette Marine made the mistake of hiring a non-MTU student intern to create a spreadsheet to determine how different rolls of sheet metal should be cut based on the 20200426171107icarbajo widths needed in the production line. The operations staff can’t figure out the resulting spreadsheet and would like you to update it so it is understandable, useful and correct. Use the concepts, techniques and Cardinal Rules you learned in BUS2300 to fix the Question5 worksheet in your Excel file. Hint: Start with the Solver inputs and work backwards. Question 6: 10 points The stack welding cell is having above normal defects during the summer months and the manufacturing engineer needs some assistance in analyzing the possible causes. The team does not work in a climate-controlled environment and is impacted by the changing weather in Marinette, WI where they are located. The engineer gathered data related to the weather and the defects for the summer months and can be found on the Question6 tab of your Excel file. Develop scatterplots for each independent variable and a regression model, using the types learned in the course, that best reflects the relationship between the relevant independent variables and the number of defects. If the engineer can only control one of the independent variables based on budget limitations, recommend the value you would suggest for that variable. Show the impact to predicted defects using the other actual data (holding the recommended value constant.) Include the 95% approximate prediction interval and calculate the average reduction in defects based on your recommendation, note any concerns that you might have concerning the analysis. Question 7: 15 Points A rush order just came in and the operations team is ramping up the workforce. This is going to require additional staffing in the assembly area and extending the usual workday by adding additional shifts. Due to the cyclical nature of this staffing, management wants to bring in temporary workers to fill out the demand. They are creating two daily shifts, a morning and afternoon shift. Each shift works 10 hours per day. The part-time employees only work 20 hours per week and the full-time employees work 40 per week. This means that there are full-time shifts that can work the morning or the afternoon (For example, a possible shift only works in the morning for 4 consecutive days). The part-time have possible shifts for each morning and each afternoon (a worker on a shift does not work in the morning and afternoon). Each parttime employee makes $8.30 per hour, each Full-time is paid $11.20 per hour. Both types of workers get an additional 20% for hours worked on Friday. Management wants to make sure that there at least as many full-time employees working on a given 10 hour portion of the day (Monday morning for example) as part-time 20200426171107icarbajo employees. Based on the planned workload you need the following number of employees on the following days and time of day. Required Morning Afternoon Monday 7 7 Tuesday 10 11 Wednesday 7 12 Thursday 6 12 Friday 8 7 Create an Excel model to determine the optimum staffing to minimize total staffing costs. Year Series 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) Country Value Armenia 3.012232 Azerbaijan 3.706506 Azerbaijan 3.706506 Bahrain 5.305718 Bangladesh 2.389269 Cambodia 3.122466 China 4.013536 Cyprus 4.400609 Georgia 2.763157 India 3.523963 Indonesia 3.628722 Israel 4.608332 Japan 5.264286 Jordan 4.245957 Kazakhstan 2.631874 Korea, Rep. 4.071846 Kuwait 3.851623 Kyrgyz Republic 1.984866 Malaysia 4.948716 Mongolia 2.200377 Nepal 2.645617 Pakistan 3.065851 Philippines 2.804591 Qatar 5.474945 Singapore 6.096157 Sri Lanka 4.086694 Thailand 3.087695 Turkey 2.723795 United Arab Emirates 5.0345 Vietnam 2.460472 Armenia 3.430632 Azerbaijan 3.919462 Azerbaijan 3.919462 Bahrain 5.101227 Bangladesh 2.430303 Cambodia 3.306097 China 3.940938 Cyprus 4.231121 Georgia 2.590542 India 3.674574 Indonesia 3.729093 Israel 4.764966 Japan 5.383704 Jordan 4.451142 Kazakhstan 3.184586 Korea, Rep. 4.331084 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2018 2018 2018 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) Kuwait 4.046397 Kyrgyz Republic 1.96449 Malaysia 4.856561 Mongolia 2.422682 Nepal 2.782714 Pakistan 2.966831 Philippines 3.244539 Qatar 5.750483 Singapore 6.086865 Sri Lanka 3.827973 Thailand 3.057649 Turkey 3.28536 United Arab Emirates 5.23841 Vietnam 2.632561 Armenia 3.601318 Azerbaijan 3.711204 Azerbaijan 3.711204 Bahrain 4.763042 Bangladesh 2.607875 Cambodia 3.170093 China 3.943955 Cyprus 4.381351 Georgia 2.714169 India 3.678587 Indonesia 3.903628 Israel 4.649997 Japan 5.733451 Jordan 4.561666 Kazakhstan 3.62126 Korea, Rep. 4.025284 Kuwait 3.791076 Kyrgyz Republic 2.331674 Malaysia 4.81442 Mongolia 2.486104 Nepal 2.854457 Pakistan 2.900425 Philippines 3.59057 Qatar 5.98356 Singapore 6.121031 Sri Lanka 3.787307 Thailand 3.132244 Turkey 3.612001 United Arab Emirates 5.349519 Vietnam 2.863597 Armenia 3.458405 Azerbaijan 3.518034 Azerbaijan 3.518034 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.02 Intellectual property protection, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) Bahrain 4.650791 Bangladesh 2.528266 Cambodia 2.794208 China 3.952852 Cyprus 4.349931 Georgia 3.04655 India 3.718591 Indonesia 4.120547 Israel 4.571172 Japan 5.952777 Jordan 4.570554 Kazakhstan 3.651473 Korea, Rep. 3.705182 Kuwait 3.462289 Kyrgyz Republic 2.634206 Malaysia 5.199916 Mongolia 2.703564 Nepal 2.928575 Pakistan 2.8532 Philippines 3.708437 Qatar 5.964257 Singapore 6.151738 Sri Lanka 3.939747 Thailand 3.057317 Turkey 3.663242 United Arab Emirates 5.475348 Vietnam 3.050503 Armenia 3.287283 Azerbaijan 3.32844 Azerbaijan 3.32844 Bahrain 5.801507 Bangladesh 2.520808 Cambodia 2.847853 China 4.104658 Cyprus 5.470417 Georgia 4.882799 India 3.717573 Indonesia 3.408487 Israel 6.048877 Japan 6.215933 Jordan 4.761435 Kazakhstan 3.482577 Korea, Rep. 4.618919 Kuwait 4.521791 Kyrgyz Republic 2.663771 Malaysia 4.468992 Mongolia 2.957077 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2016 2016 2016 2016 2016 2016 2016 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payments and bribes, 1-7 (best) 1.05 Irregular payme ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors