FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
10th Edition
ISBN: 9781259964947
Author: Libby
Publisher: MCG
Bartleby Related Questions Icon

Related questions

bartleby

Concept explainers

Question
100%

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,
expand button
Transcribed Image Text:### 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/Diagrams

This 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
expand button
Transcribed Image Text:### 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/Diagrams This 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
Expert Solution
Check Mark
Knowledge Booster
Background pattern image
Accounting
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Text book image
FINANCIAL ACCOUNTING
Accounting
ISBN:9781259964947
Author:Libby
Publisher:MCG
Text book image
Accounting
Accounting
ISBN:9781337272094
Author:WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:Cengage Learning,
Text book image
Accounting Information Systems
Accounting
ISBN:9781337619202
Author:Hall, James A.
Publisher:Cengage Learning,
Text book image
Horngren's Cost Accounting: A Managerial Emphasis...
Accounting
ISBN:9780134475585
Author:Srikant M. Datar, Madhav V. Rajan
Publisher:PEARSON
Text book image
Intermediate Accounting
Accounting
ISBN:9781259722660
Author:J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:McGraw-Hill Education
Text book image
Financial and Managerial Accounting
Accounting
ISBN:9781259726705
Author:John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:McGraw-Hill Education