4) The chart has sales data on 15 employees of a small firm. If the firm pays a bonus of 0.5% on sales above $400,000, 1% on sales above $600,000, 1.5% on sales above $800,000, and 2% on sales above $1,000,000, calculate each employee’s bonus for this year in the yellow cells.

AGEC 4213 2020 Final Exam

Download the spreadsheet AGEC4213__Final_2020.xlxs and save as FL42132020_Final to use to complete the exam, where FL is your initials. Do all your calculations in the spreadsheet and place the spreadsheet in the assignment box.  You may use your notes and homework to complete the exam along with any other sources, except you may not discuss the exam with anyone else including your classmates. If you need clarification, send me an e-mail or WeChat and I will supply an answer. Upload your completed spreadsheet with your answers to the assignment box before 11:59 a.m. on December 9.

First thing you need to do is you must put your CWID in the red cells on the Bonus tab. Some of the calculations are dependent upon those cells. Also place your name in the red box where showed to agree that all work is your own.

Do not add any tabs to the worksheet except as needed for problem 4.

1. (20) On the Bonus tab, you will calculate bonuses for a firm’s employees. You may add extra columns as needed, but do not move columns A-E.
   1. (4) The chart has sales data on 15 employees of a small firm. If the firm pays a bonus of 0.5% on sales above $400,000, 1% on sales above $600,000, 1.5% on sales above $800,000, and 2% on sales above $1,000,000, calculate each employee’s bonus for this year in the yellow cells.
   2. (8) If the firm introduces no new products and you assume that the sales will stay the same next year, how high can the commissions on sales be, if you want to maintain the 0.5% difference between each sales category and you want to give a total bonus of $100,000 to all employees combined? Calculate each employee’s bonus for next year in the green cells and put the bonus percentage for the lowest sales group in the green cell.
   3. (8) If you expect the sales to increase by 10% on average for each employee, how high can the commission on sales be if you want to maintain the 0.5% difference between each sales category and you want to give out a total bonus of $125,000 to all employees combined? Calculate each employee’s bonus for next year in the blue cells and put the bonus percentage for the lowest sales group in the blue cell.
2. (20) On the break-even tab I have supplied data for an acre of corn production including sales of grain and pasture from the acre. If your profits before taxes are greater than zero, you pay taxes otherwise you pay no taxes. Harvesting cost consists of three charges, a per acre charge of $30, hauling cost of $0.25 per bushel, and an extra charge of $0.25 per bushel on yield above 200 bushels per acre. For example, if yield is 250 bushels per acre, the total cost of harvest charge would be 30+ 0.25*250+0.25*(250-200).
   1. (4) Calculate the profit after taxes per acre in the dark green cell using the orange cells to calculate the revenues, costs, and taxes for the given values.
   2. (4) Calculate the break-even yield at a $5.00 per bushel corn price in the light green cell (have net income =0).
   3. (4) Calculate the break-even price at a 250-bushel corn yield in the blue cell (have net income=0).
   4. (4) The selling price per bushel and yield per acre are highly variable. What combinations of selling price per unit and yield per acre are profitable for the firm? Fill in the profit per acre in the yellow cells for various combinations of selling price and yield.
   5. (4) Have Microsoft Excel fill in an orange color instead of yellow for the various combinations of selling price per unit and yield per acre that are profitable, net income >=$0.00.
   6. Make sure what you save has the 250 bushels and $5.00 per bushel price in cells C2 and C4.
3. (25) Fancy Hats produces three kinds of hats using three types of labor and two kinds of material. I have supplied the prices and requirements for each item in the Mix spreadsheet. Find the production that maximizes profit and the allocation of the resources. Fill out the orange cells.
4. (35) Use the tab oats to perform the following calculations. The variables include: Oats measured in bushels of oats per acre, nitrogen applied measured in pounds per acre, phosphate (P2O5) applied measured in pounds per acre, and potash (K2O) applied measured in pounds per acre. You calculate the other variables from these variables. You may answer all the questions in the spreadsheet if you make sure you label your answers, so I know what they are.
   1. (10) Estimate oats yields as a function of nitrogen, phosphate, potash, the square of each fertilizer, and all combinations of the three fertilizers except do not include the combination of the three fertilizers. You should have nine variables. You may place the regression on a new tab.
   2. (10) Discuss the significance of each variable in the regression result and the meaning of the coefficients for each variable. Since there are nine variables, you should have at least eighteen statements.
   3. (10) Assume all the coefficients have acceptable significant. Assume phosphate application and potash application are both 100 pounds per acre. From the estimated equation calculated in a, build a table showing the oat yield for nitrogen levels from 0 to 240 pounds in increments of 10 pounds. Also, calculate the marginal physical productivity (MPP) of nitrogen for each nitrogen level. Use calculus to calculate the MPP. It is the derivate of the equation estimated in a with respect to nitrogen. In the table, you must refer to the cells from the regression. Do not round the coefficients and type them in your equations you use for calculations.
   4. (5) Using your equations, assume the price of oats is $9.00 per bushel and the price of nitrogen is $0.60 per pound, calculate the amount of nitrogen to the nearest tenth (or smaller if you want) of a pound per acre that you should apply to maximize profits if your phosphate application and potash application are both 100 pounds per acre.

Again, you may use your notes and homework to complete the exam along with any other sources, except discussing the exam with anyone one else including your classmates. You may consult agricultural economics, economics, or statistic textbooks. Exam is due in assignment box by 11:59 a.m. December 7.