Applying Excel - Goal Seek and PMT: Excel Worksheet (Part 1 of 2) Download the Applying Excel form below. Follow the tutorial on the first tab and then complete the questions using Excel tools and formulas on the second tab Use the Goal Seek function in Excel to solve for the missing value for March Units Solt in the income Statement. After running the Goal Seek function, verify that the amount match the example below for the first two requirements Total number of units to sell in March 2,313 Total number of units to sell in March after price increase Check your worksheet setup and formulas by changing the versatile expense per unit to 5075 w cells CM, OM, and ES as the Dass area, keeping all of the other date the seas in the original example. If your worksheet is operating property, the operating income for the entre quarter should be $16.266 Note: If you rounded up the units sold for March to the nearest whole unit, the operating income will be $16,268 due to rounding If you did not get these answers, reset the Variable expense per unit back to $100 and redo the Goal Seek function. Be sure that you are referencing cent F22 as the "Set col" for the targeted operating income of $15,000 The last two requirements can be calculated using the PMT formula and other basic Excel functions Your monthly payment in Requirement C should be $53457 and the new monthly payment in Requirement D should be $45017 Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse". Next click "Save". You will use this worksheet to answer questions in 2 Goal Seek-Student Paper Only Corp. has a compensation plan for its sales reps where if they meet a targeted operating income level for the quarter they receive a 10% bonus based on the net operating income goal! Jim just received his sales information from 5 January and February. He needs to determine the total sales he must make in March in order to receive a bonus, Use Goal Seek to help Jim determine the total sales he needs to make in March to reach the targeted income level for the first quarter B Targeted operating income for the quarter = $ 15,000 10 11 Income Statement January February March Quarter Totals 12 Units Sold 1,500 1,250 13 Price per unit S 5.00 $ 5.00 $ 5.00 14 Variable expense per unit S 0.75 $ 0.75 $ 0.75 15 16 Sales $ 7,500 $ 6,250 $ $ 13,750 17 Operating expenses: 18 Variable expenses 1,125 938 2,063 19 Fixed expenses 1,750 1,750 1,750 5,250 20 Total expenses $ 2,875 $ 2,688 $ 1,750 $ 7,313 21 22 Operating income S 4,625 $ 3,563 $ (1.750) $ 6,438 23 24 25 Required 20 determine the total number of units that Jim needs to sell in March in order to meet 25 Required 20 27 a. Using Goal Seek in Excel, determine the total number of units that Jim needs to set in March in order to meet his targeted operating income for the quarter 29 30 Total number of units to sell in March 31 32 33 b. If the company increases its price per unit by $1.00 in March, what is the minimum number of units that Jim needs to sell in order to meet his targeted operating income for the quarter? Use Goal Seek to find your answer 134 35 Total number of units to sell in March after price increase 2 37 30 c. Assume that Jim met his target net operating income of $15,000 and will now receive a $1,500 bonus. Jim plans to use his bonus as a down payment on a car. Using the PMT function, determine the monthly payment for his new car loan 40 41 Cost of car $ 30,000 42 Down payment $ 1,500 43 Loan amount $ 28,500 44 Term (in months) 45 Interest Rate (annual) 60 4.75% 46 Monthly Payment 41 48 49 150 51 d. Jim decides to wait to buy his car until he has a larger down payment. He wants to determine what his monthly payment would be if he earned a full year of quarterly bonuses. Assuming Jim received the same quarterly bonus for all four quarters based on the net operating income goals described above, determine the following (Hint: You can either use Goal Seek or the PMT function to calculate the new monthly payment) 29 30 Tas tangered operating income for the quarter. Total number of units to sell in March 33 31 b. If the company increases its price per unit by $1.00 in March, what is the minimum number of units that Jim needs to sell in order to meet his targeted operating income for the quarter? Use Goal Seek to find your answer 34 Total number of units to sell in March after price increase ? 37 30 39 c. Assume that Jim met his target net operating income of $15,000 and will now receive a $1,500 bonus Jim plans to use his bonus as a down payment on a car. Using the PMT function. determine the monthly payment for his new car loan. 41 Cost of car $ 30,000 42 Down payment $ 1,500 43 Loan amount $ 28,500 44 Term (in months) 60 45 Interest Rate (annual) 4.75% 46) Monthly Payment 2 47 4 49 50 51 d. Jim decides to wait to buy his car until he has a larger down payment. He wants to determine what his monthly payment would be if he earned a full year of quarterly bonuses Assuming Jim received the same quarterly bonus for all four quarters based on the net operating income goals described above, determine the following (Hint: You can either use Goal Seek or the PMT function to calculate the new monthly payment.) 52 53 Total bonus amount earned in one year ? ? 154 New loan amount 55 New monthly payment