Download the data in the le titled \"AssigmnentlP'NTDataSet.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. rThe point of this exercise is to see hovr 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, 1/, = KAiNiVG 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 N; is employment during period t. Capital's share of output (if we think about the economy as characterised by perfectly competitive, classical economics assumptions) is given by or and labouris share of output. is 1 0-. 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 identied colunms 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 colunms 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 (K = Cf, + It). 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, at, 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 growtl'l rate, 11, 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 K1 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 yearis 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 'is: Ave savings rate\". . Assume that the labour share data included in the data set. represents 1 Ct; for each period. Construct a time-series for capital share, of, 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 2019 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 B. 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 68 with this number and label column S \"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 production function, calculate the savings rate that maximizes steady state consumption per effective wt'Jrker. Then using this optimal savings rate: ll out rows 3 through 68 of column 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).1 Title the plot L'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 2919. Place this data in column U under the label \"Consumption per Eitective \\Vorker". 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 YouTube videos explaining how to label axes of Excel plots. 18. 19. 20. 21. 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 TW \"Golden Rule Output per Effective W'orker". 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 'Worker". 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 W'orker - Optimal C per \"forker\". 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) Using the data for Y = C + I that you constructed along with the employment data and imputed TFP data. construct a timeLseries 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