Directions: You should complete all the calculations using the excel formula functions in the appropriate worksheet. You should answer any questions in the worksheet, not on separate paper. You should submit the spreadsheet in the Canvas assignment in the link “Excel Homework 2.” Complete each problem in a separate worksheet. Name your homework with your name and homework 2. For example, I would name the submission “dj_homework2.”
Problem # 1 Make the appropriate calculations, graphs/charts, or other changes described in the directions to the data in the spreadsheet. Make sure to completely label the graphs/charts.
A. This data shows milk production in the United States by state in 2020. Milk production is in millions of pounds.
(1) You should sort the data by milk production from largest to smallest value. Hint: You need to highlight the states and milk production to sort both together.
(2) After sorting the data, find the average production for all states. After finding the average, divide each state’s milk production by that average to get the ratio of the state’s production to the average. A ratio of greater than 1.0 indicates that the state’s production is greater than the average, while a ratio less than 1.0 indicates the state’s production is less than the average.
(3) Highlight the states with an average greater than 1.5 using conditional formatting.
B. This data shows the acres harvested of four crops (almonds, grapes, citrus, and pistachios) in Kern County between 2000 and 2016. Find the mean of each crop over the period. Create 2 graphs: (1) the trends in the acres harvested of the crops over the period and (2) the per centages of mean harvested acres of each crop over the period. For the second graph, you will need to first get the mean for each crop, sum all the means, and divide each crop mean by the sum of all the means to get the percentages. You should add a chart title and axis labels (if appropriate) (3)
C. This data contains the life expectancy and literacy rates for 205 countries. You are asked to investigate this relationship to determine if it positive or negative. You should graph this relationship using the appropriate graph. Add a trend line with the equation. Add the following chart title: “Relationship Between Life Expectancy and Literacy.” Change the scale of the x-axis from 0-100 to 40-100. Label the axis. (3)
Problem # 2 This problem is designed so that you can calculate the future value and present value of some investments at different interest rates. Make sure to add a minus sign to PV and FV to generate a positive value of PV and FV. We are assuming that you are investing (not paying a loan). Assume that all payments occur at the end of the period. (5)
A. You were given 4 tables and should calculate the values in the shaded cells in column A. In tables 1and 3, you are asked to calculate the present value (PV) of some future income. In table 3, assume that we have quarterly compounding.
B. In tables 2 and 4, you are asked to calculate the future value of a principal (present value). In table 4, assume that we have monthly compounding.
Problem # 3 Use the farm profitability spreadsheet to answer this question. (8)
A. Fill in the cells highlighted in Note (light tan). Assume that the farm is leasing acres to another farmer, so this is an additional source in revenue. Record the results for total revenue, total costs, and profit in cells H11-H13.
B, What will happen to the total revenue, total costs, and profit if the price of the output falls to $ 3.10 per pound and wages increase to $ 15.00 per hour? Compare your results to the results in part A in cell H15.
Problem # 4 Fill in the loan analysis table to get the values in the shaded cells. (8)
A. Row 6: The shaded cell in row 6 tells you the payment required to pay down a loan to $ 100,000 if paid periodically for 5 years.
B. Row 7: The shaded cell in row 7 tells you how much of the loan you have left to pay (FV) if you make a periodic payment for 5 years. Assume that you make a $ 22,000 payment each period.
C. Row 8: The shaded cells in row 8 tells the number of payments (and years) needed to pay off a loan if you make a $ 20,000 payment each period.
D. Row 9: The shaded cell in row 9 tells you how much you could afford to borrow if you make periodic payments for 5 years to be able to pay off the loan at the end of the 5-year period. Assume that you make a $ 18,500 payment each period for 5 years.
Problem # 5 This dataset contains the salaries and player statistics of players in the National Basketball Association (NBA). We are interested in determining the impact of a variety of factors (e.g., the number of years in the league, rebounds per game) on the salaries of NBA players. Using the data set in the spreadsheet, estimate the following model and answer the following questions. You should include the regression output in the spreadsheet. If you cannot add the data analysis tool, send me an email and I will send you the regression output. Type in the answers to the questions to Problem # 5 below in these directions and upload the directions with the spreadsheet (10)
Salaryi = βo + β1*EXPi + β2*AGEi + β3*GAMESi + β4*MINUTESi + β5*PPGi + β6*RBDSGi + β7*ASTGi + β8*ALLSTARi
The variables are defined as follows:
Salaryi = the salary (in thousands of dollars) of the ith player in the NBA
EXPi = the number of years in the NBA for the ith player
AGEi = the age of the ith NBA player
GAMESi = the average number of games played by the ith NBA player
MINUTESi = the average number of minutes played by the ith NBA player
PPGi = average points per game scored by the ith NBA player
RBDSGi = average rebounds per game of the ith NBA player
ASTGi = average assists per game of the ith NBA player
ALLSTARi = a dummy variable if the ith NBA player had been selected to play in the all-star game (D=1 if the player had been selected and D=0 if the player had not been selected)
A. Which variables are significant at a critical value of 1.96.
B. How much of the variation in salary is explained by this model?
C. Interpret the coefficient estimate on GAMESi.
D. Interpret the coefficient estimate on RBDSGi.
E. Interpret the coefficient estimate on ALLSTARi.
F. Assume that the variables in this model take on the following values: EXPi = 8; AGEi = 30; GAMESi = 68; MINUTESi = 2550; PPGi = 22.5; RBDSGi = 2.4; and ASTGi = 1.8. Also assume that this player had been an all-star at one point in his career. What would be your best forecast of salary of this NBA player?
Do you need urgent help with this or a similar assignment? We got you. Simply place your order and leave the rest to our experts.