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 Column B Column C 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 Year Sardine stock at the end of the year with 10 boats fishing in the bay Sardine stock at the end of the year with 20 boats fishing in the bay 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 as you move down columns or across rows You will of course need to translate all of this into cell notation to accommodate changes 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 boats you can allow on Monterey Bay while …