Use excel

ii. Change the cost to prepare bid (Cell B3) from 300 to 1600 in 100 increments and find the bid decision in each case. [15 marks] Submit a screenshot of the data table only. iii. Use a 2-way data table and make the changes to inputs (cells B15 and B3) simultaneously and state the best bid decision. 1. For the best decision, you should refer to cell $G$18. You should ensure that in that cell you have a VLOOKUP formula that looks up the value in cell G17 (which includes a MAX formula (MAX(G10:G13)) in table array G10:H13. and submit the excel file for Question 3 with the pdf file of your assignment. 3. Excel Data Table [30 marks] Excel File: BUS336 Spring 2023 A1 Pauline's Painting is considering whether to bid for renovations of a condo in Calgary. Naturally, the condo owner may ask a competitor to bid for the project. The bids are delivered independently so no company knows what the others are bidding. Pauline's Painting is considering bidding $5,800,$6,400, or $7,000 (or perhaps not bidding at all) on this job. The condo owner will choose the lowest bid. Pauline's Painting estimates that it will cost $330 to prepare a bid and labour and material costs of $4,100 if she wins the contract. Based on past contracts of this type, Pauline's Painting believes the possible low bids from the competition, if there is any competition, and the associated probabilities are those shown in the table below. In addition, Pauline's Painting believes there is a 15% chance that there will be no competing bids. To calculate the values in the "blue cells" use logic to determine the profit for Pauline's Painting in each cell. Don't use "if" statements. submit both your solution \& formula spreadsheets for each part (a,b, and c). For the formula sheets, ensure that you resize them appropriately so that all of the formula can be seen. Take a screenshot of both and ensure that they are readable. Use Excel and efficient cell formulas (SUMPRODUCT, MAX and VLOOKUP would be examples). No statement is necessary for the choice in each case. You should start with the first sheet which is for MAXIMAX calculations. Once you fill the blue cells on this sheet, the other sheets will be updated accordingly. a. Calculate the MAXIMAX value and determine the bid decision (fill in the template). Briefly state the monetary risk in this approach. (Not just "high risk, high return". Consider the most likely scenario that could happen.) b. Calculate the MAXIMIN value and determine the bid decision (fill in the template). Briefly state the monetary opportunity cost in this approach. (Not just "low risk, low return". Consider what happens with this strategy.) c. Calculate the EMV by calculating the joint probabilities with the information given above and determine the bid decision (fill in the template). [15 marks] d. Sensitivity Analysis: i. Change the probability of no bid by competitors (Cell B15) (currently 0.15), from 0.1 to 0.9 in 0.05 increments. Find out when your best bids value changes by changing that probability. [15 marks] Submit a screenshot of the data table only. 1 ol Dailina'c Daintinc - nn 1 Yinn Y 1 b) Pauline's Painting - MAXIMIN \begin{tabular}{c|c|} \hline Cost to Prepare Bid & $4,100 \\ \hline I ahn ir \& Mat'I C.nat & $330 \\ \hline \end{tabular} The drawback (risk) with this strategy is that 1 d) Pauline's Painting - EMV \&g pue slg s\| u! suey