Independent Project 6-5

Windows   Mac

Classic Gardens and Landscapes counts responses to specialty promotions to determine effectiveness. You use SUMIFS and an IFS formula to complete the summary. You also calculate insurance statistics and display full names in one cell.

[Student Learning Outcomes 6.1, 6.2, 6.3, 6.6, 6.7]

File Needed: ClassicGardens-06.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-ClassicGardens-06.xlsx

Skills Covered in This Project

• Nest MATCH and INDEX functions.
• Create DSUM formulas.
• Build an IFS function.
• Build SUMIFS formulas.
• Use TEXTJOIN to join labels.

1. Open the ClassicGardens-06 start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Create a nested INDEX and MATCH function to display the number of responses from a city.
   1. Click the Mailings sheet tab and select and name cells A3:D28 as Responses.
   2. Click the Mailing Stats sheet tab.
   3. Click cell B21 and type Carthage.
   4. Click cell C21, start an INDEX function, and select the first argument list option.
   5. Choose or type the Responses range name for the Array argument.
   6. Click the Row_num box and nest a MATCH function. Select cell B21 for the Lookup_value and cells A3:A28 on the Mailings sheet for the Lookup_array. Click the Match_type argument box and type 0.
   7. Click INDEX in the Formula bar. Click the Column_num box and nest a second MATCH function to look up cell D3 on the Mailings sheet in the lookup array A3:D3.
   8. Click the Match_type box and type 0 (Figure 6-106) and click OK. The result displays as 343.00.Figure 6-106 Nested MATCH and INDEX functions
   9. Format the results to show zero decimal places.
   10. Type Smyrna in cell B21.
3. Use DSUM to summarize mailing data.
   1. Select the Mailings sheet and note that number mailed is located in the third column and response data is in the fourth column.
   2. Click the Criteria sheet tab. Select cell B2 and type lan* to select data for the Landscape Design department.
   3. Type law* in cell B5 for the Lawn & Maintenance department.
   4. Type pat* in cell B8 for the Patio & Furniture department.
   5. Click the Mailing Stats sheet tab and select cell B7.
   6. Use DSUM with the range name Responses as the Database argument. Type 3 for the Field argument (# Mailed column), and enter an absolute reference to cells B1:B2 on the Criteria sheet as the Criteria argument.
   7. Copy the formula to cell C7 and edit the Field argument to use the fourth column (# Responses).
   8. Use DSUM in cells B8:C9 to calculate results for the two remaining departments.
4. Use SUM in cells B10:C10.
5. Format all values as Comma Style with no decimal places.
6. Create an IFS function to display a response rating. Note: If your version of Excel does not include the IFS function, build the following nested IF function =IF(C7/B7>=20%,$C$15,IF(C7/B7>=15%,$C$16,IF(C7/B7>=11%,$C$17,$C$18))) to display the rating.
   1. Click cell D7. The response rate and ratings are shown in rows 14:18.
   2. Start an IFS function and select C7 for the Logical_test1 argument. Type / for division and select cell B7. Type >=20% to complete the test.
   3. Click the Value_if_true1 box, select C15, and press F4 (FN+F4) (Figure 6-107).Figure 6-107 First Logical_test and Value_if_true arguments
   4. Click the Logical_test2 box, select C7, type /, select cell B7, and type >=15%.
   5. Click the Value_if_true2 box, click cell C16, and press F4 (FN+F4).
   6. Complete the third and fourth logical tests and Value_if_true arguments (Figure 6-108).Figure 6-108 Completed IFS function arguments
   7. Copy the formula in cell D7 to cells D8:D10.
7. Use SUMIFS to total insurance claims and dependents by city and department.
   1. Click the Employee Insurance sheet tab and select cell E25.
   2. Use SUMIFS with an absolute reference to cells F4:F23 as the Sum_range argument.
   3. The Criteria_range1 argument is an absolute reference to cells E4:E23. The Criteria1 argument is bre* to select the city of Brentwood.
   4. The Criteria_range2 argument is an absolute reference to cells D4:D23, the department column, with criteria of lan* to select the Landscape Design department.
   5. Click OK. The result for cell E25 is 10.
   6. Build SUMIFS formulas for cells E26:E28 based on the criteria displayed in cells C26:C28.
   7. Format borders to remove inconsistencies, if any, and adjust column widths to display data.
8. Use TEXTJOIN to display names. Note: If your version of Excel does not include the TEXTJOIN function, use CONCAT or CONCATENATE to build the function.
   1. Click the Full Names sheet tab and select cell E4.
   2. Start a TEXTJOIN function and press Spacebar for the Delimiter argument.
   3. Click the Text1 box and select cell C4.
   4. Complete the Text2 and Text3 arguments to show middle and last names and click OK (Figure 6-109).Figure 6-109 Delimiter is a space
   5. Copy the formula to display full names in column E.
9. Save and close the ClassicGardens-06 Excel workbook (Figure 6-110).

1.Start Excel. Download and open the workbook named go16_xl_ch04_grader_4g_hw.xlsx, and then save it in your Excel Chapter 4 folder as Lastname_Firstname_4G_Loan_Staff_Form.

2.Display the second worksheet named Warehouse Payment Table. In cell B8, enter a PMT function using cell B4 divided by 12 as the rate, cell B3 as the number of payment periods, and cell B2 as the present value of the loan. Display the result as a positive number.

3.Create a two-variable data table in the range B8:H16. Set cell B3 as the row input cell, and cell B4 as the column input cell. From the Cell Styles gallery, apply the Currency cell style to the range C9:H16. Select the payment option closest to but less than $10,000 per month for a 120-month loan—cell D16—and format the option with the Note cell style. Save your workbook.

4.Display the fourth worksheet named Job Information, select the range A4:C11, and then sort the range by Job Code in ascending order. Create a range named Job_Code using the data in cells A5:A11.

5.Display the Staffing Plan worksheet, and then select the range A9:A18. Display the Data Validation dialog box, and validate from a List using the Source =Job_Code.

6.Click cell A9, click the list arrow, and then click M-MG. Click cell B9 to make it the active cell, and then insert a VLOOKUP function that will look up the Description of the Job Code in cell A9 using the information in the Job Information worksheet as the table array. After selecting the table array, be sure to press F4 to make it an absolute cell reference. The Description to be looked up is in column 2 of the table array.

7.With cell B9 as the active cell, copy the VLOOKUP formula down through cell B18. In cell C9, type 1 as the # of Positions and in cell D9, type Management as the Type.

8.In cell E9, insert the VLOOKUP function to look up the Salary of the Job Code in cell A9 by using the information in the Job Information sheet as the table array; the Salary is in column 3 of the table array. Copy the VLOOKUP formula in cell E9 down through cell E18.

9.Beginning in cell A10, add these staff positions:

Item   # of Positions   Type
C-CASH            3        Cashier
C-CSA              1        Customer Service
M-AMG            3        Management

10.Delete any unused rows between the last item and the Total row. Sum the Budget Amount column and apply the Total cell style. Save your workbook.

11.Display the Bracelet Revenue worksheet. Click cell I5, and then on the Formulas tab, click Trace Precedents. On the ribbon, in the Formula Auditing group, click Error Checking, and then click Edit in Formula Bar. Edit the formula so that the formula is using the Growth Assumption for Beaded Bracelets, not for Crystal Bracelets.

12.In the Error Checking dialog box, click Resume. In cell M6, notice the formula is trying to divide by cell L10, which is empty. Click Edit in Formula Bar, change 10 to 9 Ensure that the reference to L9 is an absolute reference, and then in the Error Checking dialog box, click Resume.

13.In cell F7, examine the error information, and then click Copy Formula from Above. Examine the error in cell J8, and then click Copy Formula from Left. Click OK. Use Format Painter to copy the format in cell M5 to cell M6. Save your workbook.

14.In the sheet tab row, right-click any sheet tab, and then click Select All Sheets. Display the Page Setup dialog box, click the Margins tab, and then under Center on page, click Horizontally. Click the Header/Footer tab, click Custom Footer, and then in the Left section, insert the File Name. Click OK two times. Ungroup the sheets, and then for these three worksheets—Warehouse Payment Table, Staffing Plan, and Bracelet Revenue—set the Orientation to Landscape. Select the Bracelet Revenue sheet, and on the Page Layout tab, set the Scale to 90%. Add the tag New York revenue to the properties.

15.Ensure that the worksheets are correctly named and placed in the following order in the workbook:  Warehouse Purchase, Warehouse Payment Table, Staffing Plan, Job Information, Revenue. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Directions

The manager at James Otis Tax Associates has asked you to submit a report describing the technology hardware and software needed to build a computer network for the new office. The manager has asked you to describe the computer system for each new employee, including an explanation of the purpose for each selected technology component, and to identify specific software programs that the employees would need. He has also asked you to describe the security measures needed to support the systems. In your explanation, include how your knowledge of human-computer interaction (HCI) impacted your choices for each component.

The manager has provided the following employee descriptions:

• Certified Public Accountant (CPA): The CPA is a senior position at a tax preparation company. The CPA will be not only handling sensitive customer data from personal tax returns but also preparing tax returns for corporate clients. The CPA is also responsible for the personal tax returns filled out by the tax and data-entry specialist employees.
• Tax Specialist: The tax specialist has the dual role of entering data and verifying that tax returns are correct. This position involves customer interaction in gathering data for minimizing taxes owed or maximizing tax refunds.
• Data-Entry Specialist: This position requires data entry for personal tax returns for customers who walk in the door. Although the data-entry specialist works with sensitive customer data, they do not have access to the tax returns once the returns are submitted to the tax specialist for review.

For each employee, you must:

• Describe all hardware components, including peripherals, needed to create the systems
• Identify specific software programs (more than one) that would be beneficial to each identified employee. Programs could include:
  • Operating systems
  • Applications such as:
    • Office automation products
    • Security products
• Describe security measures needed to support this system. Security measures could focus on:
  • Access
  • Administration and user passwords
  • Firewalls

Along with each description, explain the purpose of each technology component in each system. In your explanation, include how your knowledge of human-computer interaction (HCI) impacted your choices for components.

Finally, choose a network topology that could be used to connect all of the new employees identified by the manager. Create a Visio diagram to identify the networking equipment needed to enable the workflow between the employees.

This’s a problem for Concepts of Programming Language

For Building A Scanner

Source code can be of C, C++ or Java or Python
There should be Design/Report Initially, then the project, please read the pdf posted below on how to proceed further.