Download the data in the le titled \"AssignmenthWTDataSet.xlsx". Consider the Solow model with both population growth and technological progress. In this assignment you will use the data set from the Excel spreadsheet and, taking the equations of the Solow model (overly) seriously, (i) back out the difference between imputed savings rates for the Australian economy and the optimal savings rate as implied by the Solow model, (ii) construct the difference between the observed level of consumption per worker and the golden rule level of consumption per worker and (iii) construct and plot a timeseries for output per effective worker. In conducting the exercise, you will be ignoring data about the amount of govern ment consumption and investment as well as net experts. This is because the model that has been covered in class also neglects these features. Thus, you don't want to take the results too seriously. The point of this exercise is to see how economists can use data. with their models but also to appreciate the limitations that modelling assumptions can impose on the resulting implications. For this assignment, assume that. the aggregate production function is represented by a Cobb-Douglas form, rt = K?(AiNi)1_\" where Y, is output, Kt is the period t capital stock= A; is a measure of the level of technology or productivity in period t, and N1 is employment during period t. Capital's share of output (if we think about the economy as characterized by perfectly competitive, classical economics assumptions) is given by or and labouris share of output is l tr. For simplicity, use the continuoustime model's equations even though you are using annual data. As you carry out the instructions below, ensure that you are storing the data in the correctly identified columns and rows. Failure to do so will likely result in mark deductions. When constructing capital's share, growth rates and savings rate data, do not multiply by 100. This ensures that 1 s is not a large negative number and that n and g are in similar scale as the savings rate. 1. Copy the data (including headings) to sheet one of a new Excel workbook. You should have columns A through G and rows 1 through 68 lled with data. How 1 is occupied by headings and Rows 2 through 68 are comprised of data spanning the years 1953 through 2019. 2. Using data on the capital stock (see legend) construct a measure of investment for the years 1954 through 2019. To do so, you will need to calculate the change in the capital stock between each consecutive year and add the amount U! 10. of depreciation that occurs. Total depreciation over a year can be constructed by multiplying the size of the capital stock from the preceding year by the rate of depreciation for the current year. This amounts to taking the model's capital accumulation equation literally to back out a timeseries for investment given a time series for the capital stock and the depreciation rate of capital. Save this imputed investment data in a column H and label it \"I(t) : Imputed I\" in your spreadsheet . . Construct an output measure for each year by adding your constructed measure of investment to the timeseries for consumption that is included in the data set (E = C}, + 1;). Save this output data in a column I of your spreadsheet and label it Y = C+I. . Construct the annual growth rate of the labour force, m, by calculating the growth rate of the labour force of each year between 1954 through 2019. Save this data in column J of your spreadsheet labelled \"n(t) : emp growth rate\". . For simplicity, construct the constant population growth rate, a: as the average of the population growth rates between 19-54 and 2019. Populate rows 3 through 68 of column K in your spreadsheet with this value of n. replicated for each year and in cell Kl label this column as \"Ave 11\". . Using the Solow model's equation relating output to investment along with your constructed output and investment data, back out a time-series featuring each year's savings rate from 1954 through 2019. Store this data in a column L of your spreadsheet labelled \"s[t) : Imputed Savings Rate\". . Calculate the average of the imputed savings rates. Fill rows 3 through 68 of row M with this number. Label this column \"5: Ave savings rate\". . Assume that the labour share data included in the data set represents 1 at for each period. Construct a time-series for capital share, at, for each year from 1954 through 2019. Store this data in column N of your spreadsheet and label it \"Imputed Capital Share\". The constructed data should occupy rows 3 through 68. . Calculate the average of the imputed capital share data. F ill rows 3 through 68 of column 0 in your spreadsheet with this average value of the capital share repeated for every year. Label this column \"Ave Capital Share". You now need to construct a measure for the rate of techonological progress, 9: from the model. To do so= use the equation above for the Cobb-Douglas production function along with your data from columns I, D: E, G and N to 11. 12. 13. 14. 15. 16. 17. create a time series for A; for each year from 1954 through 2019. Put this data in rows 3 through 68 of column P and label this column \"Imputed TFP". Use the lmputed TFP data of column P to construct the growth rate of TFP for every year from 1955 through 2010 and store this data in rows 4 through 68 under column Q of your spreadsheet. Label this column \"TFP Growth Rate\". Calculate the average growth rate of TFP from column Q. Fill rows 3 through 68 of column R. with this number. Label column R \"Ave TFP Growth Rate\". (Note that you are assuming that g is constant over time and hence are using it for row 3 too.) Take the average value of the depreciation rate data of column F [column F is labelled \"delta\"). Fill rows 3 through 58 with this number and label column 3 \"Ave Delta\" . Now we can have some fun. Label column T, \"Optimal s - Savings Rate\". Using what you know about the Solow growth model with population growth and technological progress, along with the Cobb-Douglas prmluctitm function, calculate the savings rate that maximizes steady state consumption per effective worker. Then using this optimal savings rate: fill out rows 3 through 68 of colunm T by taking the difference between this optimal savings rate and your timeseries for the imputed savings rate (column L) for each year spanning 1954 through 2019. Plot the data in column T (vertial axis) against the years of the corresponding observations (horizontal axis);l Title the plot \"Optimal s - Savings Rate". (5 Marks) Given the data in your spreadsheet, construct a time-series for consumption per effective worker for every year between 1954 and 2019. Place this data in column U under the label \"Consumption per Elfective lWorker". Assume that the data that you have constructed by taking averages represent steady state values. For example, the average employment growth rate can be used to represent the steady state employment growth rate of the Solow model, the steady state depreciation rate can be viewed as the steady state depreciation rate of the capital stock, etc. Calculate the golden rule steady state capital stock per effective worker. Fill rows 3 through 68 of column V with this number and label the column \"Golden Rule Capital Stock per Effective W'orker\". 1There are plenty of YouTubc videos explaining 110w to label axes of Excel plots. 18. Construct the golden rule steady state level of output per effective worker and populate rows 3 through 68 of column W" with this number. Label column W \"Golden Rule Output per Effective W'orker\". 19. Construct the golden rule steady state level of consumption per effective worker and populate rows 3 through 68 of column X with this number. Label column X \"Golden Rule Consumption per Effective W'orker\". 20. Calculate the difference between consumption per worker and the optimal level of consumption \"per worker\" using the data that you have constructed in your spreadsheet. Store the results in column Y under the label \"Consumption per \\NOI'kQI' - Optimal C per TWorl'rer\". Plot this difference in a line graph with the horizontal axis featuring the year and the vertical axis measuring the data in column Y. (5 marks) 21. Using the data for Y = C + I that you constructed along with the employment data and imputed TFP data. construct a timevseries for output per effective worker for each year between 1954 and 2019. Plot this data. in a line plot featuring the year of observation on the horizontal axis and output per effective worker on the vertical axis. Does it look like Australia shifted from one steady state to another during this period? If so, how would this affect your steady state calculations above? Type your answer at the bottom of the spreadsheet below your data plots. (5 Marks) Ensure that your plots do not overlap with the data on your spreadsheet. Simply drag your plots so that they sit below the data on your spreadsheets. Submit your Excel spreadsheet through Turn-It In. Remember, don't take these numbers seriously but do take the exercise as a useful learning experience. Have fun and learn something! \fVariable definition Year Number of persons engaged (in millions) Average annual hours worked by persons engaged Real GDP at constant 2017 national prices (in mil. 2017US$) Capital stock at constant 2017 national prices (in mil. 2017US$) Capital services at constant 2017 national prices (2017=1) Share of labour compensation in GDP at current national prices