Did I do this question correctly? If not please show where I went wrong and how to fix it.A company is taking bids on four construction jobs. Three contractors have placed bids on the jobs. Their bids (in thousands of dollars) are given in the file P05_52.xlsx. (A blank indicates that the contractor did not bid on the given job.) Contractor 2 can do only one job, but contractors 1 and 3 can each do up to two jobs. Determine the minimum cost assignment of contractors to jobs(Note that the data from P05_52.xlsx is already in the excel sheet (B5:E7). ### Excel-Based Contractor and Job Assignment Solver**Bids on Contracts (in $1000s)**This section lists the bids from different contractors for four separate jobs. Each cell represents the bid amount a contractor would charge for a specific job.| Contractor/Job | Job 1 | Job 2 | Job 3 | Job 4 ||----------------|-------|-------|-------|-------|| Contractor 1 | 54 | | 48 | 52 || Contractor 2 | 47 | | 46 | 55 || Contractor 3 | 47 | | 45 | |**Assignment of Contractors to Jobs**This allocation table uses binary values (1 and empty cells) to indicate whether a contractor is assigned to a particular job. A value of 1 means the contractor is assigned to the specified job.| Contractor/Job | Job 1 | Job 2 | Job 3 | Job 4 | Total ||----------------|-------|-------|-------|-------|-------|| Contractor 1 | | | | | =SUM(B11:E11) || Contractor 2 | | | | | =SUM(B12:E12) || Contractor 3 | | | | | =SUM(B13:E13) |**Constraints (Total Assignments)**- Total contractors per job, as stipulated by "=SUM(B11:E11)"- Constraint limit per contractor "=SUM(B11:E13)"**Objective Function**The total cost, defined by the formula:```excel=SUMPRODUCT(B5:E7, B11:E13)```This function multiplies each bid by its corresponding assignment and provides the total cost of assignments.**Solver Parameters**The Solver is set to minimize the total cost (`$B$18`) by adjusting the binary allocation matrix in `$B11:$E13`.**Constraints:**1. Contractors cannot be assigned more than twice (<= 2) as indicated in columns F and J.2. Jobs must be assigned exactly once.**Graphical and Visual Explanation**- The blue-shaded cells indicate where bids are present and viable for consideration.- The red-shaded area highlights the assignment matrix that the Solver will adjust to achieve the optimal cost solution.By using Solver functionalities and correctly setting up the constraints and objective functions, ### Bidding on Contracts and Assignment of Contractors#### Bids on Contracts (in $1000s)- **Note:** Large values mean the contractor did not bid for that job.| Contractor | Job 1 | Job 2 | Job 3 | Job 4 ||--------------|-------|-------|-------|-------|| Contractor 1 | 54 | 48 | 52 | || Contractor 2 | | 47 | 46 | || Contractor 3 | 47 | 45 | 52 | 55 |#### Assignment of Contractors to Jobs| Contractor | Job 1 | Job 2 | Job 3 | Job 4 | Total | Max ||--------------|-------|-------|-------|-------|-------|-----|| Contractor 1 | 0 | 0 | 1 | 0 | 1 | <= 2 || Contractor 2 | 0 | 1 | 0 | 0 | 1 | <= 1 || Contractor 3 | 1 | 0 | 0 | 1 | 2 | <= 2 |#### Required Assignments| | Job 1 | Job 2 | Job 3 | Job 4 ||--------------|-------|-------|-------|-------|| Required | 1 | 1 | 1 | 1 |#### Total Cost ($1000s)- **Total Cost:** 186### Explanation of Graphs/DiagramsThis spreadsheet details the allocation of jobs to different contractors based on the bids they have placed. 1. **Bids on Contracts section (Rows 4-8):** - It shows the bids of three contractors (Contractor 1, Contractor 2, and Contractor 3) for four jobs. - For example, Contractor 1 bid $54,000 for Job 1, $48,000 for Job 2, $52,000 for Job 3, and did not place a bid for Job 4. - If a contractor has not bid for a specific job, it is indicated by a large value or absence of a value.2. **Assignment of Contractors to Jobs section (Rows 10-17):** - This section shows

Job costing is a accounting method to account for those firms which perform various jobs in the same…

Answered: A company is taking bids on four…

10th Edition

ISBN:9781259964947

Author:Libby

Publisher:Libby

Chapter1: Financial Statements And Business Decisions

Section: Chapter Questions

Problem 1Q