Did I do this question correctly? If not please show where I went wrong and how to fix it.The government is auctioning off oil leases at two sites. At each site, 150,000 acres of land are to be auctioned. Cliff Ewing, Blake Barnes, and Alexis Pickens are bidding for the oil. Government rules state that no bidder can receive more than 45% of the land being auctioned. Cliff has bid $2000 per acre for site 1 land and $1000 per acre for site 2 land. Blake has bid $1800 per acre for site 1 land and $1500 per acre for site 2 land. Alexis has bid $1900 per acre for site 1 land and $1300 per acre for site 2 land.a. Determine how to maximize the government’s revenue with a transportation model.b. Use SolverTable to see how changes in the government’s rule on 45% of all land being auctioned affect the optimal revenue. Why can the optimal revenue not decrease if this percentage required increases? Why can the optimal revenue not increase if this percentage required decreases? ### Oil Lease Auction: Optimization Analysis Using Excel SolverThis educational segment covers how to use Excel for optimizing bid allocations in an oil lease auction, aimed at maximizing revenue based on given constraints. Below is a step-by-step transcription of the provided Excel sheet with the integration of Solver parameters to achieve optimal decisions.#### Spreadsheet Section1. **Header:** - **Title:** Oil lease auction2. **Bids Overview:** - The bidding amounts for three sites are organized in dollars per acre. ``` | | Site 1 | Site 2 | |-------|--------|--------| | Ewing | 2000 | 1500 | | Barnes| 1800 | 1500 | | Pickens| 1900 | 1800 | ```3. **Operational Constraints:** - **Max % that can be sold to any bidder:** 45%4. **Bid Allocation:** This part lists the number of acres potentially awarded to each bidder for each site. ``` | Bidders | Site 1 | Site 2 | Total | |---------|--------|--------|-------| | Ewing | 135000 | 0 | 135000| | Barnes | 0 | 0 | 0 | | Pickens | 0 | 135000 | 135000| ```5. **Capacity Constraints:** - Indicates the total available acres per site: ``` | | Site 1 | Site 2 | |----------|--------|--------| | Available| 150000 | 150000 | ```6. **Revenue Calculation:** - This is calculated using the `SUMPRODUCT` formula: ```excel =SUMPRODUCT(B5:C7, B13:C15) / 1000000 ```#### Solver Parameters Section**Objective:**- **Set Objective (B20):** Optimize the total revenue.- Goal: Maximize (To: Max)**Variable Cells:**- `By Changing Variable Cells`: B13:C15 (Indicates the number of acres sold to each bidder for each site.)**Constraints:**Numerous constraints are set to ensure feasible solutions within given operational limits:- `Subject to the Constraints`: - The sum of acres sold for ### Oil Lease Auction#### OverviewThis document provides a detailed breakdown of bids for oil lease sites and allocations based on those bids. The table below outlines the key figures involved in the auction process, including bids, site allocations, and revenue generation.#### Bids for Sites (dollars/acre)| Bidder | Site 1 | Site 2 ||---------|--------|--------|| **Ewing** | $2,000 | $1,000 || **Barnes** | $1,800 | $1,500 || **Pickens** | $1,900 | $1,300 |#### Max % that can be sold to any bidder- **45%**#### Number of Acres Sold to Bidders| Bidder | Site 1 | Site 2 | Total | Max Allowed ||---------|--------|--------|-------|-------------|| **Ewing** | 0 | 0 | 0 | <= 135000 || **Barnes** | 0 | 0 | 0 | <= 135000 || **Pickens** | 0 | 135000 | 135000 | <= 135000 |#### Total Acres Allocated- **135000 acres**#### Available Acreage and Revenue| Site | Available | Revenue ($millions) ||-------|-----------|----------------------|| **Site 1** | $150,000 | || **Site 2** | $150,000 | **$473** || **Total** | $300,000 | |This table represents the acreage allocated to different bidders (Ewing, Barnes, and Pickens) across two sites. Pickens acquired 135,000 acres of Site 2, yielding revenue of $473 million.#### Key Observations1. **Bidding Distribution**: - Pickens has been allotted the entire permissible extent of land for Site 2. - Other bidders, Ewing and Barnes, were not allocated any acres.2. **Revenue Calculation**: - The total revenue generated from the auction of Site 2 amounts to $473 million.3. **Constraints**: - A maximum of 45% of land could be allocated to any single bidder, ensuring a competitive and fair distribution process.This structured auction setup ensures transparent, competitive,

Find the Given details below: Given details: Cliff Ewing Blake Barnes Alexis Pickens Total…

Answered: The government is auctioning off oil…

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...