Exp19_Excel_Ch05_ML1_RealEstate

Project Description:

You are a real estate analyst who works for Mountain View Realty in the North Utah County area. You have consolidated a list of houses sold during the past few months and want to analyze the data. For a simple analysis, you will outline the data and use the Subtotal feature. You will then create two PivotTables and a PivotChart to perform more in-depth analysis.

Details to the assignment uploaded.

Thank you!

  please see file 48hrs to complete???  

Guided Project 4-3

Windows   Mac

Clemenson Imaging analyzes expense reports from field representatives as well as patient and image data. To complete the worksheets, you format data as a table and build an advanced filter. You import a comma-separated text file (.csv) and use the Subtotal command. Finally, you display data in a PivotTable.

[Student Learning Outcomes 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.7, 4.8]

File Needed: Clemenson-04.xlsx (Available from the Start File link.), ClemensonText-04.csv and ClemensonWord-04.docx (Available from the Resources link.)

Completed Project File Names: [your name]-Clemenson-04.xlsx and [your initials] Excel 4-3Word.docx

Skills Covered in This Project

• Format data as an Excel table.
• Build an Advanced Filter.
• Apply conditional formatting to filtered results.
• Sort data by multiple columns.
• Import a comma-separated text file.
• Use the Subtotal command.
• Export data via the Clipboard.
• Create a PivotTable.

This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.

IMPORTANT: If you are a Canadian user, be sure to verify that your browser and Microsoft Office use the same country settings. See here for a Help topic on how to change your settings.

1. Open the Clemenson-04.xlsx start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
2. Copy the Past&Projected sheet, place the copy before the Criteria sheet, and name the copied sheet Adv Filter.
3. Create and format an Excel table.
   1. Select cells A4:E60 on the Adv Filter sheet.
   2. Click the Quick Analysis tool and choose Tables.
   3. Click Table.
4. Apply a table style.
   1. Click cell A5 in the table.
   2. Click the More button [Table Design tab, Table Styles group].
      Click the More button [Table Tools Design tab, Table Styles group].
   3. Select White, Table Style Medium 15.
5. Create an output range for an Advanced Filter.
   1. Select cells A4:E4 and copy and paste them to cell G4.
   2. Type Extract Range in cell G3 and set the font to Cambria 16 pt.
   3. Adjust column widths for columns G:K to show the labels.
   4. Adjust row 4 to a height of 33.75 (45 pixels).
6. Create an Advanced Filter.
   1. Click the Criteria sheet tab.
   2. Type >12/31/18 in cell A3 to find records after 2018.
   3. Type mri in cell B3.
   4. Type >12/31/19 in cell A4, to find records after 2019.
   5. Type ct scan in cell B4. This criteria will find records dated 2019 or later for MRIs and records for CT scans dated 2020 or later (Figure 4-94).Figure 4-94 Criteria for Advanced Filter
   6. Click cell A5 on the Adv Filter sheet.
   7. Click the Advanced button [Data tab, Sort & Filter group].
   8. Select the Copy to another location radio button.
   9. Verify that the List range is cells $A$4:$E$60. If the range is incorrect, click and drag to select the range including the header row.
   10. Click the Criteria range box and select cells A2:B4 on the Criteria sheet.
   11. Click the Copy to box and select cells G4:K4 on the Adv Filter sheet.
   12. Click OK in the Advanced Filter dialog box.
7. Sort data in the output range.
   1. Select cell G5 and click the Sort button [Data tab, Sort & Filter group].
   2. Click the Sort by arrow and choose Image for the first level.
   3. Verify that the Sort On selection is Cell Values and the Order is A to Z.
   4. Click Add Level and choose Month as the Then by selection.
   5. Verify or select Cell Values and Oldest to Newest for the second level.
   6. Click OK.
8. Apply conditional formatting and borders.
   1. Select the cells with values in column I.
   2. Click the Conditional Formatting button [Home tab, Styles group].
   3. Choose Highlight Cells Rules and Greater Than.
   4. Type 750 and choose Green Fill with Dark Green Text.
   5. Click OK.
   6. Select the extract range starting at cell G5 and apply All Borders (Figure 4-95). (Your results may be different from Figure 4-95 depending on the current date.)Figure 4-95 Advanced Filter results
   7. Press Ctrl+Home.
9. Import a comma-separated values text file.
   1. Click the Expense Info sheet tab.
   2. Select cell A4.
   3. Click the From Text/CSV button [Data tab, Get & Transform Data group].
   4. Find and select the ClemensonText-04.csv file downloaded from the Resources link in the Import Data window.
   5. Click Import.
   6. Verify that Comma is selected as the Delimiter in the Navigator window.
   7. Click the Load arrow and select Load To. Select Existing worksheet.
   8. Verify that cell A4 displays as the destination and that Table is how the data will be imported.
   9. Click OK to import the text data (Figure 4-96).Figure 4-96 CSV file imported as a table
   10. Cut and paste (or drag and drop) the labels in row 3 to replace the labels in row 4.
   11. Select cell A5 and click the Convert to Range button [Table Design tab, Tools group]. Click OK to remove the query definition.
   12. Select cells A4:D31 and clear the formats [Home tab, Editing group].
   13. Select cells A4:D31 if necessary, and change the font size to 11 pt.
   14. Select the labels in row 4 and center align them.
   15. Format the dates in column C with the Short Date format.
   16. Select columns A:D and size each column to 12.14 (90 pixels) wide. Deselect the columns.
10. Use the Subtotal command.
    1. Close the Queries & Connections pane and click cell A5.
    2. Click the Sort A to Z button [Data tab, Sort & Filter group] to sort by last name.
    3. Click the Subtotal button [Data tab, Outline group].
    4. Verify or choose Last Name for the At each change in box.
    5. Click the Use function arrow and choose Average.
    6. Verify or choose the Amount box in the Add subtotal to area.
    7. Click OK.
    8. Format the values in column D as Currency with zero decimal places.
11. Collapse outline groups.
    1. Click the collapse symbol (−) for Allen in row 8.
    2. Click the collapse symbol (−) for McAllister (Figure 4-97).Figure 4-97 Subtotals added and outline collapsed for two groups
12. Create a PivotTable.
    1. Click the Past&Projected sheet tab.
    2. Select cells A4:E60.
    3. Click the Quick Analysis tool and choose Tables.
    4. Point to each PivotTable option to see the Live Preview.
    5. Choose the option that shows a sum of the employees, the total hours, and the patients by image (Figure 4-98).Figure 4-98 Suggested PivotTable choices from the Quick Analysis tool
    6. Rename the sheet PivotTable.
    7. Drag the Month field in the Rows area in the PivotTable Fields pane out of the pane and into the worksheet to remove the field from the PivotTable.
13. Format a PivotTable.
    1. Click cell C4.
    2. Click the Field Settings button [PivotTable Analyze tab, Active Field group].
       Click the Field Settings button [PivotTable Tools Analyze tab, Active Field group].
    3. Click Number Format.
    4. Choose Number as the Category.
    5. Select the Use 1000 Separator (,) box and set zero decimal places.
    6. Click OK to close each dialog box.
    7. Apply the same number format for the “Sum of Patients” field.
    8. Click the More button [PivotTable Design tab, PivotTable Styles group].
       Click the More button [PivotTable Tools Design tab, PivotTable Styles group].
    9. Select Dark Gray, Pivot Style Dark 9 [PivotTable Design tab, PivotTable Styles group].
       Select Dark Gray, Pivot Style Dark 9 [PivotTable Tools Design tab, PivotTable Styles group].
    10. Select the Banded Rows box [PivotTable Design tab, PivotTable Styles Options group].
        Select the Banded Rows box [PivotTable Tools Design tab, PivotTable Styles Options group].
    11. Select the Banded Columns box [PivotTable Design tab, PivotTable Styles Options group] (Figure 4-99).
        Select the Banded Columns box [PivotTable Tools Design tab, PivotTable Styles Options group] (Figure 4-99).Figure 4-99 PivotTable with new settings
14. Create and format a PivotChart.
    1. Click the PivotChart button [PivotTable Analyze tab, Tools group].
       Click the PivotChart button [PivotTable Tools Analyze tab, Tools group].
    2. Select Bar as the chart type and Stacked Bar as the subtype.
    3. Click OK.
    4. Position the chart object so that its top-left corner is at cell A12.
    5. Drag the bottom-right selection handle to reach cell J30.
    6. Click the More button [PivotChart Design tab, Chart Styles group] and choose Style 8.
       Click the More button [PivotChart Tools Design tab, Chart Styles group] and choose Style 8.
    7. Click the Change Colors button [PivotChart Design tab, Chart Styles group].
       Click the Change Colors button [PivotChart Tools Design tab, Chart Styles group].
    8. Select Monochromatic Palette 1 in the Monochromatic group.
    9. Deselect the # of Employees box in the PivotChart Fields pane (Figure 4-100).Figure 4-100 PivotChart object
    10. Click cell A1.
15. Export data using the Clipboard.
    1. Click the Adv Filter sheet tab.
    2. Starting at cell G4, select the cells in the Extract Range and click the Copy button [Home tab, Clipboard group].
    3. Open the ClemensonWord-04 Word document downloaded from the Resources link.
    4. Press Ctrl+End to position the insertion point.
    5. Click the arrow with the Paste button [Home tab, Clipboard group] and choose Paste Special.
    6. Select Microsoft Excel Worksheet Object in the Paste Special dialog box.
    7. Select the Paste link radio button in the Paste Special dialog box and click OK. (The Excel data in your document may be different from the figure.)
    8. Click the Center button [Home tab, Paragraph group].
    9. Save the Word document as [your initials] Excel 4-3Word in your usual folder (Figure 4-101). You will not be uploading this file to SIMnet.Figure 4-101 Word document with pasted Excel object
    10. Close Word.
16. Save and close the Clemenson-04 Excel workbook.
17. Upload and save the Clemenson-04 Excel workbook.
18. Submit project for grading.

This discussion forum focus on the many dimensions of building a properly functioning system that meets the customer demands according to Software Engineering principles outlined in the assigned textbook. 

Discuss some of the “Software Engineering ” constraints that must be overcome in order build a successful system.

Search the peer-reviewed literature  for examples of this. You may select any topic relating to technology that illustrates the potential for really messing things up. Include, in your description, an analysis of what might have caused the problems and potential solutions to them. Be sure to provide supporting evidence, with citations from the literature  

 Do a bit  if research into File Inclusion Vulnerability.

• What is it and  Why it is dangerous  and what are  the difference of low and remote inclusion .What methods can me employed to prevent a security breach?

 In order to complete assignment #3 you will need to answer the below questions. Please complete the questions in a Word document and then upload the assignment for grading. When assigning a name to your document please use the following format (last name_Assignment #3). Use examples from the readings, lecture notes and outside research to support your answers. The assignment must be a minimum of one (1) full page in length with a minimum of two (2) outside sources. Please be sure to follow APA guidelines for citing and referencing source. Assignments are due by 11:59 pm Eastern time on Sunday.

                                ( Question below)

Using the Internet as a resource, develop a security checklist for an SQL Server. 

    20

1. Review Chapter 14 in Principles of Information Systems.
2. In the discussion linked below, respond to the following prompts:
   1. From the textbook reading, what concerns are apparent in the use of technology and the internet and one’s expectation of privacy? Of the concerns described, which is most relevant to you? Consider why this is important to you. How does this affect your personal life?
   2. What are the most significant laws and regulations in place to protect people and organizations? After thinking about the current legal environment, what do you believe still needs to be addressed regarding the personal privacy of a user’s information?
   3. As you think about current and emerging technologies (artificial intelligence, for example), what privacy issues will require some kind of remediation?
3. Your initial post should be a minimum of 300 words and is due by the fourth day of the workshop week.

Exp22_Excel_Ch12_HOE – Invoice 1.0

Exp22 Excel Ch12 HOE Invoice 1.0

Excel Chapter 12 Hands-On Exercise – Invoice 

Project Description:

Recently, you took a position as the senior account manager at Wilhelmina’s Total Lawn Care (WTLC), a lawn care company located in Leland, Michigan. WTLC specializes in full service lawn care ranging from landscaping to weekly maintenance. The previous manager used a paper-based system to prepare expense reports, invoices, and payroll statements. However, this was a time-consuming process and required manual recalculation when any values changed. You want to start automating tasks using your extensive experience with Excel. You decide to start with the client invoice report form. Because each of the company’s billing representatives utilizes the same procedures, you will adapt an Excel template to use as a model for creating invoices. The template needs to be generic enough to accommodate a range of options, but it also needs to maintain a standard design to facilitate easy data entry. You will download an existing Excel invoice template, customize the template for your business needs, and inspect the worksheet for accessibility. Finally, you will create macros to perform a series of tasks, such as clearing the values to reset the form and finalizing the invoice for management approval.

Start Excel. Download and open   the file named Exp22_Excel_Ch12_HOE_Invoice.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

The Invoice template provides   the basic design you need, but you want to add your company’s contact   information. You also want to delete the sample data and remove the developer’s   data validation comments.
  Make the following edits to the template:
 

  Type Wilhelmina’s   Total Lawn Care   in cell B2
  Type   Every Lawn Needs TLC   in cell B3
  Type Lot   Area in cell C5
  Type 1234   Caesar Parkway, Leland, MI 49654 in cell B27
  Apply Italics format to cell B3
  Delete the values in the range D6:D10

Continue editing the template by   making the following structural changes.
 

  Insert a row before row 1
  Insert a column before column A
  Set the column width of column A to 2
  Add an outside border to the range B2:F29
  Clear all data validation that was included in the original template
  Rename the worksheet Customer Invoice

Prior to distributing your   template, you want to remove all personal information. You will use the   Document Inspector to complete the task.
 

  Use the Document Inspector to remove all Document Properties and Personal   Information.
 

  Mac users, on the Excel menu, click Preferences and then click the Security   icon. In the Security dialog box, click Remove personal information from this   file on save. 

The invoice will be sent   electronically, and you want to make sure the Invoice template does not   contain content that might cause difficulties for users. To ensure this   document is compliant with accessibility standards, you will check   accessibility.
 

  Use the Accessibility Checker to check the template for issues. Once   completed, make the following changes to improve the template’s   accessibility.
 

  Unmerge the following ranges C27:D27, C28:D28, C4:C5, C2:F2, D12:E12,   D13:E13, D6:E6.
  Set the text in cell C2 to left alignment.
  Type Due   on: in cell   D13.

You know that some of remote   staff members are using Excel 2010. You want to make sure the workbook does   not have critical features that might not display when opened with the older   version of the software.
 

  Use the Compatibility Checker to check the template for compatibility with   Excel 2010-2016 versions.
 

  Mac users, on the Tools menu (or the Review tab), click Check Accessibility.

You want to add notes to the   template to provide instruction to your staff. To do this you will annotate   the worksheet using comments.
 

  Clear the contents of cell C13. Then add the following comments to the   template.
 

  Cell C13 – Enter Business or Residential
  Cell E3 – Insert Date
  Cell E6 – Use the custom area function to calculate lot area
  Cell E13 – Business jobs are due in 30 days, all others due upon   receipt  

After inserting comments, you   decide you will automate the insertion of the date in a later step.   Therefore, you will remove the comment from cell E3. You also want to edit   the comment in cell E13 to make a small grammatical change.
 

Delete the comment in cell E3.
  Edit the comment in cell E13 to reflect the following changes Business jobs   are due in 30 days. All others due upon receipt

For your last step, you want to   hide the comments to reduce screen clutter. Once the comments are hidden, the   comment indicators will still be visible.
 

  Hide all comments in the template.

You do not want to assume the   level of Excel expertise throughout your company; therefore, you want to   craft a macro that will automate as much as possible. The macro you will   create automatically clears existing values in the workbook. Although the   template is empty to start, users might open the template, save a workbook,   and then want to use that workbook to prepare more invoices. Therefore, you   want the macro to clear cells even though the original template has no   values. Once completed, save the workbook as a macro enabled template.
 

  Record a macro named ClearInvoice. Add the description: This macro clears existing values in the   current invoice.   when creating the macro. When run, the macro should do the following:
 

  Delete the values in the cells E6, C13, E13, and the ranges C7:C11 and   C15:E25. After deleting the values, the macro should enter the following   default values.
 

  C7 = Name
  C8 = Company   Name
  C9 = Street   Address
  C10 = City,   ST Zip Code
  C11 = Phone

You want to make sure the   ClearInvoice macro does what you want it to do. You will add some sample data   and run the macro.
 

  Enter the following sample values, then run the ClearInvoice macro.
 

  C8 = John   Doe Inc.
  C9 = 123   Sample Street
  C10 = Leland,   MI 49654
  C11 = (231)   555-5555

Your sales reps may not be Excel   experts and not know how to run a macro. To make it easier to clear values   from the form, you want to assign the ClearInvoice macro to a button. The   users can click the button to clear the form to use it for another customer.   You will also add a button to be utilized later in the project.
 

  Insert a Button (Form Control) in the range H2:J2. Edit the button label to   display the text Clear Form and assign the ClearInvoice macro. Next insert a   Button (Form Control) spanning the range H4:J5. Edit the button text to   display the text Insert Date.
 

  Type Customer   Name in cell C7   and then click the Clear Form button to run the macro.

In order to use VBA to   automatically insert and format the date, you will first create a new module.   You will then use the range object to insert the current date and adjust the   font property to bold the current date.
 

  Create a new module in the VBA Editor named DateStamp. Enter the following code to   create the desired sub procedure.
 

Sub   DateStamp ()
 

‘Unprotect   worksheet for editing
Worksheets(“Customer   Invoice”).Unprotect Password:=”Expl0r!ng”
 

‘Insert   current date in cell E3
Range(“E3”)=Date
 

‘Adds   bold format to cell E3
Range(“E3”).Font.Bold=True
 

‘Protects   worksheet using the password Expl0r!ng
Worksheets(“Customer   Invoice”).Protect Password:=”Expl0r!ng”
 

End   Sub
 

  After entering the code exit the VBA Editor and run the macro to test your   work.

After running the sub procedure,   you decide that the inserted date should be italic instead of bold. You will make   this change in the VBA Editor by changing the Italic property. You will also   add a statement to the ClearInvoice Macro to unprotect the worksheet when   clearing its data.
 

  Open the DateStamp module in the VBA Editor. Locate the code Range(“E3”).Font.Bold=True   and replace the word Bold with italic.
 

  Next open Module 1. Press enter twice at the end of the first line (Ln1,   Col9) and type the following code.
 

‘Unprotect   worksheet for editing.
Worksheets(“Customer   Invoice”).Unprotect password:=”Expl0r!ng”
 

  After entering the code save and exit the VBA Editor. Next assign the   DateStamp macro to form control button 2. 

You decide to create a custom   function to enable users to manually calculate the area of a yard being   serviced.
 

  Open the VBA Editor and create a new module named CustomFunction then enter the following code:
 

FunctionArea(Length,Width)
 

‘This   function will calculate the area of the lawn being serviced
Area   = Length * Width
 

End   Function
 

  Save and exit the VBA Editor. Type =area(20,45) in cell E6 to test your work.

Insert a new worksheet named Code.

Open the VBA Editor, open the   ClearInvoice module, and copy the code. Paste the code in the Code worksheet   starting in cell A1 then remove any rows containing blank cells. When   complete, the code block should appear continuous with no blank cells between   lines. 

Open the DateStamp module, and   copy the code. Paste the code in the Code worksheet starting in cell A31 then   remove any rows containing blank cells. When complete, the code block should   appear continuous with no blank cells between lines.

Open the Function module, and   copy the code. Paste the code in the Code worksheet starting in cell A44 then   remove any rows containing blank cells. When complete, the code block should   appear continuous with no blank cells between lines.

Close EXP22_Excel_Ch12_HOE_Invoice.xlsx. Be sure to save the copy as a .xlsx file, not a macro-enabled   workbook. Exit Excel. Submit the .xlsx   file as directed.