Economics 1 Project 5 Due Date: February 22 before 9:00pm Pacific Daylight Time (late assignments will not be accepted). You can submit your Excel spreadsheet AS MANY TIMES AS YOU WANT. You will see your score each time. Autograder will score the numeric parts of the assignment. Note that this assignment has NO graphs. Overview This project will help you think about the tragedy of the commons (when rival goods are non-excludable). It will also give you practice using more sophisticated functions and running simple simulations to evaluate situations. You will talk about policy options for dealing with these sorts of situations in section. You must complete all parts of the assignment in the required format so that it can be graded. Failure to submit all work and answers in the required format will result in you receiving no points for the sections that are incorrectly formatted as Autograder cannot make value judgements or adjustments. Your TA will discuss the insights we hope you gain from these exercises in the section after the project is due. This assignment is based on the real-life rise and fall of the sardine canning industry in Monterey Bay made famous in part by novels by Nobel Prize winning American author John Steinbeck. The sardine canning industry grew and boomed in the first half of the twentieth century, on what was nicknamed Cannery Row. But by the early-1950s the sardine stock began to dwindle and was then quickly decimated. Your data includes just one data point, reported in cell B2. This is the sardine stock in Monterey Bay at the end of 1940. Below is a short description of the variables you will be using for Project 5: Column A Year Column B Sardine stock at the end of the year with 10 boats fishing in the bay Column C Sardine stock at the end of the year with 20 boats fishing in the bay Note that the variable names are in row 1. The data sent too you will include these three variable names and the sardine stock at the end of 1940 with 10 boats fishing. Other than variable names in row A, all other cells will be blank. To Receive Credit General Instructions: • Make sure your capitalization is correct for any non-numeric entries as the Autograder is case sensitive.Excel Instructions: • The Excel spreadsheet you submit must have the following title: project5.xlsx. Note that there is no space between the word and the number and there are NO capital letters. • The sheet in within the excel file that has the data, your calculations, and your answers must have the following title: Sheet 1. Note that Autograder will say there is an error if the sheet has any other title. • Make sure Sheet 1 is formatted as specified in the instructions; Autograder will only grade entries that are in the correct cells. • You MUST use Excel, NOT Google Sheets. Make sure you download the file as an Excel spreadsheet. If you accidently open it in Google Sheets download it to your computer as an Excel file, do NOT cut and paste to an Excel spreadsheet as you might mess up the format and create errors that make it impossible for Autograder to read your file. Required Elements (all elements must be in Sheet 1 in your Excel file) We will model the sardine stock at the end of each year in a simplified way. Key elements: • each boat catches exactly 1,000,000 fish per year • the sardine stock growth rate from one year to the next is 1.2 time the number of sardines left at the end of the fishing season (within each year the annual cycle is fishing followed by spawning) o consider the following simplified example: § the sardine stock at the end of year 1 is 10 § there are 2 boats that catch 2 sardines each § then the number of sardines at the end of year 2 will be: (10-2*2)*1.2=6*1.2=7.2, which we will report rounded to 7 • we will also assume that it is impossible for the stock of sardines to rise above the 1940 size or below 0 o going back to our simple example, this means our equation (model) is a bit more complicated: § MAX(MIN(((10-2*2)*1.2), 10),0) • You will, of course, need to translate all of this into cell notation to accommodate changes as you move down columns, or across rows (1) We begin assuming that there are 10 boats fishing in Monterey Bay. Use the model described above the calculate the number of sardines in the bay at the end of each year, assuming that you start with the number of sardines existing at the end of 1940 given to you in your data set. Make this calculation for all years from 1941-1965. These formulas and values must be in cells B3-B27. (2) Now imagine that the price of sardines rises in 1949 causing 10 new boats to start fishing for sardines in Monterey Bay starting in 1950. Use a formula to enter the sardine stock at the end of 1949 with 10 boats in cell C11 as your starting point for the 20 Boats column. (3) Using the sardine stock with 10 boats at the end of the 1949 as your starting point, report the sardine stock at the end of each year from 1950-1965 with 20 boats fishing in cells C12-C27.(4) With 10 boats fishing, in what year does the sardine stock fall to zero? Enter 9999 if it does not fall to zero in the given time period (if the stock is above zero at the end of 1965). Report this year in cell B31. (5) With 20 boats fishing, in what year does the sardine stock fall to zero? Enter 9999 if it does not fall to zero in the given time period (if the stock is above zero at the end of 1965). Report this year in cell B32. (6) Now imagine that you were in charge of managing the sardine stock in Monterey Bay and could choose the number of boats that could fish for sardines starting in 1950. If you goal is to maintain the stock at the 1940-level forever, while at the same time allowing the maximum possible number of boats to fish, how many boats would you allow? Note that you can only allow whole boats for entire years, not fractions of boats or parts of fishing seasons. To figure this out, you must create an 11 Boats column in column E, a 12 Boats column in column F, and so on out to a 19 boats column in column M. You must have formulas and numbers in all cells from E11 (the stock at the end of 1949 with 10 boats fishing) through M27. • All columns (E through M) must be named using the following format: o 11 Boats, 12 Boats,….,19 Boats (Autograder is case sensitive so Boats must be capitalized exactly as shown) (7) Report the maximum number of