IL_EX365_2021_11a_ZacharySutton_Report_1

.xlsx

School

Northern Arizona University *

*We aren’t endorsed by this school

Course

360

Subject

Computer Science

Date

Dec 6, 2023

Type

xlsx

Pages

15

Uploaded by MegaResolve8522

Report
Zachary Sutton GE ver. 17.1.0-rc0000 1. 0/1 Correct a formula. Correct multiple formulas. In the Tampa worksheet, one or more cells in the range G7:G14 contains an incorrect formula. Correct a formula. Correct a formula. 2. 1/1 Update a value in a cell. 3. 1/1 Create a formula using a function. Create a formula using a function. Create a formula using a function. 4. 1/1 Create a formula referencing an external workbook. 5. 1/1 Edit a defined name for a cell. 6. 0/1 Create a defined name for a range of cells. Create a formula without a function. Copy a formula into a range. In the Orlando worksheet, one or more cells in the range E7:E14 contains an incorrect formula. 7. 1/1 Remove defined names from a range. 8. 1/1 Create a formula using a function. Copy a formula into a range. 9. 1/1 Create a formula using a function. Copy a formula into a range. 10. 1/1 Create a formula using a function. Copy a formula into a range. Create a formula using a function. Copy a formula into a range. 11. 1/1 Create a formula using a function. 12. 1/1 Enter text in a cell. Use Flash Fill. Enter a value in a cell. Use Flash Fill. Delete a column. 13. 1/1 Create a formula using a function. Copy a formula into a range. Create a formula using a function. 14. 2/2 Fill a series using a linear trend. Fill a series using a growth trend. Create a formula. Fill a series using a growth trend. 15. 2/2 Enter text in a cell. Fill a range using an AutoList. 16. 0/2 Create defined names for a cell. In the Expansion worksheet, the named ranges for the range B5:B10 have not been set up correctly. 17. 2/2 Create a formula using a function. 18. 2/2 Create a formula using a function. 19. 2/2 Create a formula using a function. SUBMISSION #1 | SCORE IS: 21 OUT OF 25 Darius Beckman is the service manager for SmartEnergy Services in Tampa, Florida. The company provides electrical energy equipment and services to cities and other organizations. In an Excel workbook, Darius is tracking the service contracts he manages in Tampa and Orlando. He asks for your help in analyzing the contract data and correcting errors. Go to the Tampa worksheet. Correct the errors in the worksheet as follows: a. In cell G6, trace the errors in the formula to identify the cause of the error message. Correct the formula so that the IF function tests whether the contract date (the range named Contract_Tampa) is less than the date in cell B3. If it is, multiply the standard price (cell F6) by the sale price rate (cell G3) and return the result. If it is not, return the standard price (cell F6) in cell G6. b. Copy the formula in cell G6 into the range G7:G14 to fix the errors in that range. c. In cell G15, use Error Checking to find the formula error, and then correct the formula. d. Correct the formula error in cell F15. After entering the equipment code data in the range D6:D14, Darius applied data validation to the range to make sure he and others entered the correct codes. Check for data-entry errors and correct them as follows: a. Circle invalid data in the worksheet. b. Change the circled value to 104 to use the correct equipment code. In the range A18:B23, Darius has created an area for analyzing the service agreements for specific types of equipment. He wants to analyze the sales of substation agreements because they are the most expensive. Create formulas to analyze this data as follows: a. In cell B20, enter a formula using the COUNTIF function to count the number of substation agreements sold. Use the list of equipment codes (range D6:D14 ) as the range and the code for substations (cell I9 ) as the criteria in your formula. b. In cell B21, enter a formula using the AVERAGEIF function to find the average sale price of a substation agreement. Use the list of equipment codes as the range, the code for substations as the criteria, and the sale prices (range G6:G14 ) as the range to average in your formula. c. In cell B22, use the SUMIF function to find the total sale price of substation agreements. Use the list of equipment codes as the range, the code for substations as the criteria, and the sale prices as the range to sum in your formula. In cell B23, Darius wants to display the profit margin for substations, which is contained in another worksheet. Create a formula using an external reference as follows to include the profit margin: a. Open the workbook Support_EX365_2021_11a_Substations.xlsx . b. In cell B23 of the Tampa worksheet, insert a formula that references cell B6 in the Support_EX365_2021_11a_Substations.xlsx workbook. c. Close the Support_EX365_2021_11a_Substations.xlsx workbook. Darius has defined a name for cell B23, but wants to change it to one more similar to the text in cell A23. Edit the defined name for cell B23 to use Profit_Margin as the name. [Mac Hint: Delete the old defined name and create a new defined name.] Go to the Orlando worksheet. In the range E6:E14, Darius needs to enter a formula to calculate the expiration dates for the service agreements, which expire after one year. He wants to use a defined name in the formula as he did on the Tampa worksheet. Calculate the expiration dates as follows: a. Examine the formula in cell G6, which uses a defined name to calculate the sale price. b. Assign the same defined name ( Contract_Orlando ) to the range C6:C14, which resolves the errors in column G. c. In cell E6, enter a formula without using a function to determine the expiration date by adding 365 to the defined name @Contract_Orlando . [Hint: To avoid an #SPILL error, enable implicit intersection by placing the This Row specifier (@ character) in front of the defined name.] d. Fill the range E7:E14 with the formula in cell E6. The workbook contains a defined name Darius no longer needs. Delete the defined name invoice_number (but not the data) from the workbook. In the range I5:L10, Darius wants to list data about new Orlando customers who have not signed a contract yet. On a separate worksheet named New Clients , he imported the data, but it did not appear in the correct format. For example, the company names appear in all lowercase, though each name should begin with an uppercase letter. Examine the imported data on the New Clients worksheet, and then return to the Orlando worksheet to incorporate the imported company name data in the range I6:I10 using worksheet references as follows: a. In cell I6 on the Orlando worksheet, enter a formula using the PROPER function to capitalize the first letter in each word in the Company text in cell B3 of the New Clients worksheet. b. Fill the range I7:I10 with the formula in cell I6 to show the company names. The imported contact name data separates the first and last names, but Darius wants to list the full name in one cell. List the first and last names of each contact in a single cell using worksheet references as follows: a. In cell J6, enter a formula using the CONCAT function that displays the first name shown in cell C3 of the New Clients worksheet followed by a space ( " " ), and then the last name shown in cell D3 of the New Clients worksheet. b. Fill the range J7:J10 with the formula in cell J6 to list the full names of the remaining contacts. List the number of years in business and the state where the company is located using worksheet references as follows: a. In cell K6, enter a formula using the LEFT function to insert the first 2 characters on the left of cell E3 of the New Clients worksheet. b. Fill the range K7:K10 with the formula in cell K6. c. In cell L6, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell F3 of the New Clients worksheet. d. Fill the range L7:L10 with the formula in cell L6. Darius asks you to calculate the total service agreement sales for both offices. Go to the Summary worksheet. In cell A4, create a formula using the SUM function and 3-D references to total cell G15 on the Tampa worksheet and cell G15 on the Orlando worksheet. Next, Darius wants to separate the customer names from their invoice numbers and format the names with the last name first. Modify the data on the Summary worksheet as follows: a. Enter the text Foley, Dean in cell D5. b. Select the range D5:D22, and then use the Flash Fill command to automatically enter names into the remaining cells in the range. ( Hint : You must use the Flash Fill command to receive credit for this step.) c. Enter the invoice number 2101 in cell B5. d. Select the range B5:B22, and then use the Flash Fill command to automatically enter invoice numbers into the remaining cells in the range. ( Hint : You must use the Flash Fill command to receive credit for this step.) e. Delete column C, which is no longer necessary. Go to the Projections worksheet, which includes the income and expenses for the current year. Darius also wants to include projections for next year. First, he asks you to correct the error on the worksheet. Trace the error in cell F17, which divides the net profit in cell F16 by the total income in cell F5. The formula is correct, but cell F5 needs a value to prevent the error. Correct the error as follows: a. In cell F17, add the IFERROR function to the formula. b. If dividing the net profit (cell F16 ) by the total income (cell F5 ) results in an error, display "Missing total" as an error message. c. Copy the formula in cell F17 to the range B17:E17. d. In cell F5, enter a formula using the SUM function to total the Q1–Q4 income amounts (range B5:E5 ). Darius thinks the Tampa and Orlando offices have a good chance of increasing income in the next year. In the range B21:E23, he wants to make three projections based on different assumptions. Project the quarterly income for Darius as follows: a. First, determine the income needed in Quarters 2 and 3 to achieve an income of $7,500 in Quarter 4. Project the income in the four quarters by filling the series for the first projection (range B21:E21) with a linear trend. b. Next, determine the income in Q2–Q4 if it increases by 2 percent each quarter, starting with an income of $6,300. Project the income in the four quarters by filling the series for the second projection (range B22:E22) based on a growth trend using 1.02 as the step value. c. Finally, extrapolate the income based on the income in Q4 of the current year and a growth rate of 3 percent. In cell B23, enter a formula without using a function to reference the total income in Q4 of the current year (cell E5 ). Project the income in the four quarters by filling the series for the third projection (range B23:E23) based on a growth trend using 1.03 as the step value. Darius has prepared the range H1:M17 for detailed projections to complete later. For now, he asks you to enter the expense categories, which he will use in other workbooks. Build a custom list of categories as follows: a. Create a custom list by importing the range A4:A17 . b. Enter the text Income in cell H4, and then use the new AutoList to fill cells H5:H17. SmartEnergy Services is considering expanding the business to add a third Florida office. Darius asks you to help with the analysis of loan options to fund the expansion. Go to the Expansion worksheet, and then create defined names based on the range A5:B10 , using the values in the left column only. Calculate the monthly payment for Scenario 1 as follows: a. In cell B9, enter a formula using the PMT function to determine the monthly payment. b. In the formula, use the monthly interest rate (cell B7 ) as the rate, the term in months (cell B8 ) as the nper, and the loan amount (cell B5 ) as the pv. In Scenario 2, Darius wants to make a monthly payment of $13,800 at an annual interest rate of 4.15 percent. He wants to know how much he can borrow on those terms. Calculate the loan amount for Darius as follows: a. In cell C5, enter a formula using the PV function to determine the loan amount. b. In the formula, use the monthly interest rate (cell C7 ) as the rate, the term in months (cell C8 ) as the nper, and the monthly payment (cell C9 ) as the pmt. In Scenario 3, Darius wants to know the total amount spent for a loan of $750,000 at an annual interest rate of 3.95 percent and a monthly payment of $15,000. Calculate the future value of the loan for Darius as follows: a. In cell D10, enter a formula using the FV function to determine the future value. b. In the formula, use the monthly interest rate (cell D7 ) as the rate, the term in months (cell D8 ) as the nper, and the monthly payment (cell D9 ) as the pmt. Illustrated Excel 365/2021 | Module 11: SAM Project 1a
Author: Note Illustrate
SmartEnergy Services ANALYZE DATA AND CORRECT FORMULAS Zachary Sutton e: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the ed Excel 365/2021 | Module 11: SAM Project 1a
SmartEnergy Services Service Agreements - Quarter 1 2/15/2024 87.15% Contact Name Sale Price Dean Foley 2101 1/5/2024 101 1/4/2025 $588.00 $512.44 Rafael Nunez 2557 1/7/2024 104 1/6/2025 $819.00 $713.76 Ahmad Farrah 2254 1/9/2024 104 1/8/2025 $819.00 $713.76 Charlene Ingersoll 2479 2/1/2024 103 1/31/2025 $395.00 $344.24 Lei Song 2086 2/8/2024 102 2/7/2025 $735.00 $640.55 Greg Spahn 2699 3/1/2024 101 3/1/2025 $588.00 $588.00 Ron Remish 2158 3/10/2024 101 3/10/2025 $588.00 $588.00 Kerry Johnson 2265 3/17/2024 104 3/17/2025 $819.00 $819.00 Brianna Gaines 2077 3/21/2024 104 3/21/2025 $819.00 $819.00 Totals $6,170.00 $5,738.75 Service Agreement Statistics Substations Number 4 Average Sale Price $766.38 Total Sale Price $3,065.52 Profit Margin 5% Discount offered for purchases before: Sale price rate: Invoice Number Contract Date Equipment Code Expiration Date Standard Price
Code Equipment 101 Meters 102 Poles 103 Wires 104 Substations
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help