PLEASE SOLVE THIS CASE IN EXCEL AND SHOW THE WORK!!!

Case Problem 1: TEXTBOOK PUBLISHING ASW Publishing, Inc., a small publisher of college textbooks, must make a decision regarding which books to publish next year. The books under consideration are listed in the following table, along with the projected three-year sales expected from each book: Book Subject Business calculus Finite mathematics General statistics Mathematical statistics Business statistics Finance Financial accounting Managerial accounting English literature German Type of Book New Revision New New Revision New New Revision New New Projected Sales ($1000s) 20 30 15 10 25 18 25 50 20 30 The books listed as revisions are texts that ASW already has under contract; these texts are being considered for publication as new editions. The books that are listed as new have been reviewed by the company, but contracts have not yet been signed. Three individuals in the company can be assigned to these projects, all of whom have varying amounts of time available; John has 60 days available, and Susan and Monica both have 40 days available. The days required by each person to complete each project are shown in the following table. For instance, if the business calculus book is published, it will require 30 days of John's time and 40 days of Susan's time. An X indicates that the person will not be used on the project. Note that at least two staff members will be assigned to each project except the finance book. Book Subject Business calculus Finite mathematics General statistics Mathematical statistics Business statistics Finance Financial accounting Managerial accounting English literature German Ell John 30 16 24 20 10 X X Susan 40 24 X Monica X 30 24 16 14 26 30 30 36 X 50 ASW will not publish more than two statistics books or more than one accounting text in a single year. In addition, management decided that one of the mathematics books (busi- ness calculus or finite math) must be published, but not both. Managerial Report ASW Publishing currently estimates that their profit margin is 17.5% of total sales. Prepare a report for the managing editor that describes your findings and recommendations regarding the best publication strategy for next year in order to maximize profit. Be sure to attach your problem formulation with clearly defined variables in the appendix of your report. ---- IMPORTANT Comments on this Assignment: The decision variables you should define are: Xi = 1 if book type i is to be published, and 0 if it is not to be published. This is called a binary program, since the decision variables can only take on two possible values: 1 or 0, (i.e., publish or not). To specify inside of Excel that you are working with binary decision variables, you will need to add the binary constraint to your list of constraints inside of Solver. To correctly do this, you will go to Add constraint, highlight your row of decision variable values (which are zeroed out to begin with), and then choose the Binary option from the drop down menu that we used previously for Sand 2. This will alert Solver that it can only choose O's and 1's for the values of the decision variables. Case Problem 1: TEXTBOOK PUBLISHING ASW Publishing, Inc., a small publisher of college textbooks, must make a decision regarding which books to publish next year. The books under consideration are listed in the following table, along with the projected three-year sales expected from each book: Book Subject Business calculus Finite mathematics General statistics Mathematical statistics Business statistics Finance Financial accounting Managerial accounting English literature German Type of Book New Revision New New Revision New New Revision New New Projected Sales ($1000s) 20 30 15 10 25 18 25 50 20 30 The books listed as revisions are texts that ASW already has under contract; these texts are being considered for publication as new editions. The books that are listed as new have been reviewed by the company, but contracts have not yet been signed. Three individuals in the company can be assigned to these projects, all of whom have varying amounts of time available; John has 60 days available, and Susan and Monica both have 40 days available. The days required by each person to complete each project are shown in the following table. For instance, if the business calculus book is published, it will require 30 days of John's time and 40 days of Susan's time. An X indicates that the person will not be used on the project. Note that at least two staff members will be assigned to each project except the finance book. Book Subject Business calculus Finite mathematics General statistics Mathematical statistics Business statistics Finance Financial accounting Managerial accounting English literature German Ell John 30 16 24 20 10 X X Susan 40 24 X Monica X 30 24 16 14 26 30 30 36 X 50 ASW will not publish more than two statistics books or more than one accounting text in a single year. In addition, management decided that one of the mathematics books (busi- ness calculus or finite math) must be published, but not both. Managerial Report ASW Publishing currently estimates that their profit margin is 17.5% of total sales. Prepare a report for the managing editor that describes your findings and recommendations regarding the best publication strategy for next year in order to maximize profit. Be sure to attach your problem formulation with clearly defined variables in the appendix of your report. ---- IMPORTANT Comments on this Assignment: The decision variables you should define are: Xi = 1 if book type i is to be published, and 0 if it is not to be published. This is called a binary program, since the decision variables can only take on two possible values: 1 or 0, (i.e., publish or not). To specify inside of Excel that you are working with binary decision variables, you will need to add the binary constraint to your list of constraints inside of Solver. To correctly do this, you will go to Add constraint, highlight your row of decision variable values (which are zeroed out to begin with), and then choose the Binary option from the drop down menu that we used previously for Sand 2. This will alert Solver that it can only choose O's and 1's for the values of the decision variables