You have recently become the CFO for Beta Manufacturing, a small cap company that produces auto parts. As you step into your new position, you have decided to compile a report that details all aspects of the business, including: employee tax withholding, facility management, sales data, and product inventory. To complete the task, you will duplicate existing formatting, utilize various conditional logic functions, complete an amortization table with financial functions, visualize data with PivotTables, and lastly import data from another source.

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

Group all the worksheets in the   workbook and fill the range A1:F1 from the Insurance worksheet across all   worksheets including the formatting. Ungroup the worksheets after the fill is   complete and ensure the Insurance worksheet is active.

Click cell I5, and enter a   function that determines the number of full-time employees, (FT).

Enter a database function in   cell I6 that determines the average salary of all full-time employees with at   least one dependent. Format the results in Accounting Number Format.

Enter a lookup function in cell   E5 that returns the tax deduction amount for the number of dependents listed   in the cell C5. Use   the table in range H13:I17 to complete the function. The maximum deduction is   $500.00; therefore, employees with more than four dependents will receive no   additional deductions.

Use Auto Fill to copy the   function down, completing column E. Be sure to use the appropriate cell   referencing. Format the data in column E with the Accounting Number Format.

Enter a logical function in cell   F5 that calculates employee FICA withholding. If the employee is full-time   and has at least one dependent, then he or she pays 7% of the annual salary   minus any deductions. All other employees pay 5% of the annual salary minus   any deductions. Copy the function down through column F. Format the data in   column F with Accounting Number Format.

Apply conditional formatting to   the range C5:C34   that highlights any dependents that are greater than 3 with Light Red Fill and Dark Red Text.

Click cell H10, and enter an   AVERAGEIFS function to determine the average salary of full-time employees   with at least one dependent. Format the results in Accounting Number Format.

Use Advanced Filtering to   restrict the data to only display full-time employees with at least one   dependent. Place the results in cell A37. Use the criteria in the range   H24:M25 to complete the function.

Ensure that the Facilities   worksheet is active. Use Goal Seek to reduce the monthly payment in cell B6   to the optimal value of $6000. Complete this task by changing the Loan amount in cell E6.

Create the following three   scenarios using Scenario Manager. The scenarios should change the cells B7, B8,   and E6.
 

Good   
  B7 = .0325
  B8 = 5
  E6 = 275000
 

Most   Likely
  B7 = .057
  B8 = 5
  E6 = 312227.32
 

Bad
  B7 = .0700
  B8 = 3
  E6 = 350000
 

  Create a Scenario Summary Report based on the value in cell B6. Format the   new report appropriately.

Ensure that the Facilities   worksheet is active. Enter a reference to the beginning loan balance in cell   B12 and enter a   reference to the payment amount in cell C12. 

Enter a function in cell D12,   based on the payment and loan details, that calculates the amount of interest   paid on the first payment. Be sure to use the appropriate absolute, relative,   or mixed cell references.

Enter a function in cell E12,   based on the payment and loan details, that calculates the amount of   principal paid on the first payment. Be sure to use the appropriate absolute,   relative, or mixed cell references.

Enter a formula in cell F12 to   calculate the remaining balance after the current payment. The remaining   balance is calculated by subtracting the principal payment from the balance   in column B.

Enter a function in cell G12, based on the payment   and loan details, that calculates the amount of cumulative interest paid on   the first payment. Be sure to use the appropriate absolute, relative, or   mixed cell references.

Enter a function in cell H12,   based on the payment and loan details, that calculates the amount of   cumulative principal paid on the first payment. Be sure to use the   appropriate absolute, relative, or mixed cell references.

Enter a reference to the   remaining balance of payment 1 in cell B13. Use the fill handle to copy the functions created in   the prior steps down to complete the amortization table. 

Ensure the Sales worksheet is   active. Enter a function in cell B8 to create a custom transaction number.   The transaction number should be comprised of the item number listed in cell   C8 combined with the quantity in cell D8 and the first initial of the payment type in cell E8. Use   Auto Fill to copy the function down, completing the data in column B.

Enter a nested function in cell G8 that displays the word Flag if the Payment Type is Credit and the Amount is greater than   or equal to $4000. Otherwise, the function will display a blank cell. Use Auto   Fill to copy the function down, completing the data in column G. 

Create a data validation list in   cell D5 that displays Quantity, Payment Type, and Amount (in that order).

Type the Trans# 30038C in cell B5, and select Quantity from the validation list in   cell D5.

Enter a nested lookup function   in cell F5 that evaluates the Trans # in cell B5 as well as the Category in   cell D5, and returns the results based on the data in the range A8:F32.

Create a PivotTable based on the   range A7:G32. Place the PivotTable in cell I17 on the current worksheet.   Place Payment Type in the Rows box and Amount in the Values box. Format the   Amount with Accounting Number Format.

Insert a PivotChart using the   Pie chart type based on the data. Place the upper-left corner of the chart   inside cell I22. Format the Legend of the chart to appear at the bottom of   the chart area. Format the Data Labels to appear on the Outside end of the   chart.

Insert a Slicer based on Date.   Place the upper-left corner of the Slicer inside cell L8.

Use PowerQuery to connect to the   Access database Exp19_Excel_AppCapstone_Comprehensive_Inventory.accdb.   Load the Inventory table into a new worksheet named Inventory.
 

  Note, Mac users, download and import the delimited Inventory.txt file into a new worksheet named Inventory starting in cell A1.

Create a footer with your name   on the left, the sheet code in the center, and the file name on the right for   each worksheet.

Save the file Exp19_Excel_AppCapstone_ComprehensiveAssessment-   Manufacturing.xlsx. Exit Excel. Submit the file as directed.

Discuss:

• Different perspectives on number (6,7,8 or less or more) of hats
• Your perspective on the ideal number of hats for a data visualization project
• Your perspective on the realistic number of people used to fill “hats” for a data visualization project in real working environment. 

Use this link: https://www.visualisingdata.com/2012/06/talk-slides-the-8-hats-of-data-visualisation/

 After completing the reading this week, we reflect on a few key concepts this week:

1. Discuss Howell and Mendez’s three perspectives on followership.  Note how these behaviors influence work productivity.
2. What is the big five personality model?
3. What is the Myers-Briggs test?  How is it similar to the Big five model?  How is it different than the Big five model?

Please be sure to answer all the questions above in the initial post. 

1. This is a required assignment, worth 45 points, and must be submitted by the due date.
   Review the Grading Rubric before completing this assignment.
   Research a scholarly paper on the impact of “Emerging Technologies on SAD” and reflect on only one of the following topics:
   • “Impact”: How do recent or emerging technologies impact SAD? 
   • “Cloud”: Is the SAD process different when dealing with Cloud Systems? 
   • “Analytics-centric”: Is the SAD process different when dealing with Analytics-centric organizations? 
   • “DM-Warehouses”: Is the SAD process different when advanced Data Management and Warehouses are involved?
   • NOTE:
     You must copy and paste the topic (“Impact” or “Cloud” or “Analytics-centric” or “DM-Warehouses”) at the start of your paper to provide a context for your answer
     This paper must be between 250-300 words on what caught your eye and reflect on what you read. 
     Do not add extraneous text that does not address the question – do not add an introduction or conclusion.
     Do not copy and paste text from the referenced resource.
     You must provide at least one APA reference for your resource and corresponding in-text citations..
     You must provide the referenced resource URL/DOI in the APA reference.
     Do not use the Textbook as a referenced resource.

Exp19_Excel_Ch07_ML1_Admissions  

Exp19_Excel_Ch07_ML1_Admissions

Project Description:

You work in the Admissions Office for a small regional university in Massachusetts Your assistant entered a list of college applicants for the Fall 2021 semester. You determine if a student qualifies for early admission or early rejection based on SAT and GPA. After determining the immediate admissions and rejections, you calculate a total score based on SAT and GPA to determine regular admissions and rejections.

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

First, you want to calculate the   number of days between the Initial Deadline and the Date Received.
 

  In cell D11, insert the DAYS function using the Initial Deadline that is   stored in cell B8 and the Date Received that is stored in cell C11. Use mixed   and relative references correctly.

Copy the DAYS function from cell   D11 to the range D12:D67. A negative value indicates the application was   received after the initial deadline.

Next, you want to determine if a   student should be admitted early.
 

  In cell G11, insert the IF function with a nested AND function to display   either Yes   or No in the Admit Early column. The   university admits a student early if that student meets both the Early   Admission criteria for the SAT (cell B3) and GPA (cell B4). That is, the   student’s SAT score must be 1400 or higher, and the GPA must be 3.80 or   higher. Use relative and mixed references to the cells in the Admission   Criteria range. Based on the requirements, the first student, 2020005, will   be admitted early.

Now that you determined which   students are admitted early, you want to determine if a student should be   rejected early.
 

  In cell H11, enter the IF function with a nested OR function to display   either Yes   or No in the Reject Early column. The   university rejects a student early if that student has either an SAT score   less than 800 (cell C3) or a GPA below 1.80 (cell C4). Use relative and mixed   references to the cells in the Admission Criteria range.

Use column I to calculate an   applicant’s admission score.
 

  In cell I11, enter a formula to calculate an applicant’s admission score.   Multiply the GPA (cell F11) by the multiplier (cell B7) and then add that   result to the SAT score (cell E11). The first score is 3925. Use relative and   mixed references appropriately.

In column J, you want to display   Early Admission, Early Rejection, Admit, or Reject, respectively, to indicate   the final decision.
 

  In cell J11, enter the IFS function. If Admit Early is Yes, display the text Early   Admission. If   Reject Early is Yes, display the text Early Rejection. If the score is greater than the threshold score   in cell B6, display Admit. If the score is less than or equal to the threshold score in   cell B6, display Reject. Use mixed reference to cell B6.

You are ready to copy the   formulas down their respective columns.
 

  Copy the formulas down the Admit Early, Reject Early, Score, and Final   Decision columns. 

You want to enter formulas in   the Summary Data section to summarize key points. First, you want to   determine the number of Early Admissions and the number of Admits.
 

  In cell H3, insert the COUNTIF function to count the number of Early   Admissions in the range J11:J67. Use mixed reference for the Range argument   and use cell E3 as the condition. Copy the function to cell H4. It should   adjust automatically to count the number of Admit in the range J11:J67.

Next, you want to calculate the   average SAT score for Early Admissions.
 

  In cell I3, enter the AVERAGEIF function to calculate the average SAT score   in the range E11:E67 where Final Decisions in the range J11:J67 are Early   Admission. Use mixed references so that the row numbers in the ranges do not   change when copied down. Use a relative reference to cell E3 for the   condition.

You want to calculate the   average GPA score for Early Admissions.
 

  In cell J3, insert the AVERAGEIF function to calculate the average GPA in the   range F11:F67 where Final Decisions in the range J11:J67 are Early Admission.   Use mixed references so that the row numbers in the ranges do not change when   copied down. Use a relative reference to cell E3 for the condition.

Apply Number format with zero   decimal places to cell I3. Then, copy the functions in the range I3:J3 to the   range I4:J4.

You want to count the number of   applications that meet two conditions.
 

  In cell H5, insert the COUNTIFS function to count the total number of   applications that meet
  two conditions: Scores in the range I11:I67 are greater than or equal to 3500 and Final Decisions in the   range J11:J67 are Early Admissions (cell E3).

You want to count the number of   applications that meet two more conditions.
 

  In cell H6, insert the COUNTIFS function to count the number of applications   that meet
  two conditions: Residences in the range B11:B67 are In State and Final Decisions in the
  range J11:J67 are Early Admissions (cell E3).

You want to identify the highest   score based on two conditions.
 

  In cell H7, insert the MAXIFS function to identify the highest score in the   range I11:I67 that meets two conditions: Residences in the range B11:B67 are In State and Final Decisions in the   range J11:J67 are Early Admissions (E3).

In cell H8, insert the   AVERAGEIFS function to calculate the average score in the range I11:I67 that   meets two conditions: Residences in the range B11:B67 are In State and Final   Decisions in the range J11:J67 are *Adm*. Use the asterisks as wildcards so that it   includes both Early Admission and Admit.

In cell I5, insert the   AVERAGEIFS function to calculate the average SAT score that meets two   conditions: Scores in the range I11:I67 are greater than or equal to 3500 and Final Decisions in the   range J11:J67 are Early Admissions (cell E3). Use mixed references   appropriately.

In cell I6, insert the   AVERAGEIFS function to calculate the average SAT score that meets two   conditions: Residences in the range B11:B67 are In State and Final Decisions in the range   J11:J67 are Early Admissions (cell E3). Use mixed references appropriately.

Copy the functions from the   range I5:I6 to the range J5:J6.

You want to insert a map that   depicts admissions by state.
 

  Display the Map worksheet. Create a map chart from the range A1:B5. Change   the chart title to Admissions by State. Display the Format Data Series task pane and   select Only regions with data as the map area.

You want to place the top-left   corner of the map in cell C1 and change the map size.
 

  Cut the map and paste it in cell C1. Set 3.12″ height and 3.26″ width for the map.

Create a footer with your name   on the left side, the sheet name code in the center, and the file name code   on the right side on all sheets.

Save and close Exp19_Excel_Ch07_ML1_Admissions.xlsx.   Exit Excel. Submit the file as directed.

  This project is divided into several  parts, each with a deliverable. The first four parts are drafts. These  documents should resemble business reports in that they are organized by  headings, include source citations (if any), be readable, and be free  from typos and grammatical errors. However, they are not final, polished  reports. Please see attached. 

Project Part 2: Risk Assessment Plan

After creating an initial draft of the risk management plan, the next step is to create a draft of the risk assessment plan. For this part of the project: 

1. Research risk assessment approaches.
2. Create an outline for a basic qualitative risk assessment plan.
3. Write an introduction to the plan explaining its purpose and importance.
4. Define the scope and boundaries for the risk assessment.
5. Identify data center assets and activities to be assessed.
6. Identify relevant threats and vulnerabilities. Include those listed in the scenario and add to the list if needed.
7. Identify relevant types of controls to be assessed.
8. Identify the key roles and responsibilities of individuals and  departments within the organization as they pertain to risk assessments.
9. Develop a proposed schedule for the risk assessment process.
10. Complete the draft risk assessment plan detailing the information  above. Risk assessment plans often include tables, but you choose the  best format to present the material. Format the bulk of the plan similar  to a professional business report and cite any sources you used.

Submission Requirements

• Format: Microsoft Word (or compatible)
• Font: Arial, size 12, double-space
• Citation style: Your school’s preferred style guide
• Estimated length: 4–6 pages

´In this case study, assume that Circuit City hired you as the new chief information officer (CIO) to help relaunch the company into a global e-commerce multinational company. 

´Documentation  

´10 to 12 pages 

´APA format 

´References

 The following information should be covered in the presentation 

´1. History of Circuit City

´2. What were the issues and challenges that caused the collapse of Circuit City in 2009?

´3. What are the issues and challenges with Circuit City 2018 latest e-commerce initiative?

´4. Discuss the major competitors in global e-commerce.

´5. How would IT deliver value to the business?

´6. Proposed the IT infrastructure required to support global e-commerce

´7. Proposed the IT budget for the e-commerce initiative

´8. Discuss the IT based risks associated with global e-commerce

´9. Discuss how IT can leverage innovative technologies such as social media, big data and business intelligence to provide value to the business

´10. Discuss the future or emerging technologies that would be leveraged to give Circuit City a competitive advantage.

This assignment is a written assignment where students will demonstrate how this course research has connected and put into practice within their own career. 

Assignment:

Provide a reflection of at least 500 words (or 2 pages double spaced) of how the knowledge, skills, or theories of this course have been applied, or could be applied, in a practical manner to your current work environment. If you are not currently working, share times when you have or could observe these theories and knowledge could be applied to an employment opportunity in your field of study.

Requirements:

Provide a 500 word (or 2 pages double spaced) minimum reflection.

Use of proper APA formatting and citations. If supporting evidence from outside resources is used those must be properly cited.

Share a personal connection that identifies specific knowledge and theories from this course.

Demonstrate a connection to your current work environment. If you are not employed, demonstrate a connection to your desired work environment. 

You should not provide an overview of the assignments assigned in the course. The assignment asks that you reflect how the knowledge and skills obtained through meeting course objectives were applied or could be applied in the workplace. 

Deadline:  June 12th (Saturday)

No Plagiarism

Can I have Turnitin report as well?

Writing Expectations:

There is a craft to writing. Spelling, grammar, punctuation and diction (word usage) are all tools of that craft. Writing at the collegiate level will show careful attention to these elements of craft. Work that does not exhibit care with regard to these elements will be considered as inadequate for college writing and graded accordingly. 

All assignments, unless otherwise instructed, should be submitted in APA format.

TextBook:

Title: Network Security, Firewalls, and VPNs

Authors: J. Michael Stewart, Denise Kinsey

Publisher: Jones & Bartlett Learning

Publication Date: 2021

Edition: 3rd edition

Exercise 9.1

Consider the following algorithm:
 

   Input s
    begin
        k := LEN(s);
        if k is even then
        begin
            j := k/2;
            u := “”;
 

           while j>0 do
            begin
                u := ADDCHAR(CHAR(s),u);
                s := REST(s);
                j := j – 1;
            end
 

           s := REVERSE(s);
            u := REVERSE(u);
 

           while LEN(s)>0 do
            begin
                u := ADDCHAR(CHAR(s),u);
                s := REST(s);
            end
        end
        else
            u := s;
    end
    Output u

REVERSE(“bin”) = “nib”.]

(a) Trace through what happens in the algorithm when theinput string s is “biscotti”. What is the resulting output string u?

(b) Describe in words the effect of the algorithm on an arbitrary string s.

Exercise 9.2

Each of the formulae below describes the number of stepsan algorithm takes, in terms of n, the size of the input to the algorithm.
 

For each formula, determine the least order of magnitudeto which it can be assigned.

William Montgomery Gardner III (WG) is the owner of Gardner Novelties, Inc., a large family business that designs and manufactures specialty items for sale at roadside restaurants around the country. The company has over 2,500 employees in 13 locations around the world. They have recently begun to outsource a lot of their manufacturing to China, Mexico and India. In spite of a bad economy, WG has been able to consistently grow the company’s revenues at a rate of over 10% per year over the last ten years. As a result, WG has decided to take the company public. The Initial Public Offering (IPO) is scheduled to take place next month.

WG calls you, Joseph K. Barnes (JB) into his office. You have been in charge of all Information Technology services for the company for the past 17 years. You have a staff of 12 direct reports in your department, of which 6 are located in the company headquarters office with you. Since the items being manufactured are not technical in nature, there has never been a lot of emphasis placed on protecting proprietary data in the corporate network. Most of your time is spent dealing with service calls and Internet disruptions, maintaining the firewall, and uploading software patches.

WG tells you of the plans for the company. You try to act surprised, but the rumor has been floating around the company for months now so it is anything but a surprise. You are a little alarmed by the fact that the IPO is planned for next month, however.

WG explains that once the IPO takes place, they will need to have everything in place to be in compliance with Sarbanes-Oxley. WG asks if you are familiar with Section 404 of SOX.

You struggle to find the right words. You’ve heard of Section 404 from friends and colleagues who have had to deal with it for years now, but you never though it was something you needed to know. You mention that you know Section 404 deals with management’s assessment of internal controls, and that the IT network will need to have some controls put in place.

WG tells you he needs a plan from you in one week on how to get the IT system in compliance with SOX. You will need to make a presentation to the Executive Committee (ExCom) on your plan. The ExCom will consist of, among others, WG, the COO, VP of Human Resources, and the CFO of the company. WG warns you not to let him down…there is a lot riding on this IPO and the investors and analysts want to know that the company will be in compliance with SOX.

JB walks out of the meeting and his head is spinning. He has one week to develop a plan to bring the company’s entire IT system into compliance with SOX. How is he supposed to do that? He decides that the first course of action will be to call a friend, Robert Rodes (RR), who works for a publicly-traded company. Maybe he can help point JB in the right direction.

Read the scenario and decide on a response. Your goal is to identify the critical issues in the scenario and determine how you would resolve. Write a 3 to 4 page paper that addresses the following issues:

1. Explain your plan for implementing COBIT for Gardner Novelties. Your plan should include a discussion of the following elements:
   1. What will Gardner Novelties be required to do with IT to remain in compliance with SOX?
   2. What is COBIT and how can it help keep Gardner Novelties in compliance with SOX?
   3. What are the benefits of using COBIT as opposed to other control frameworks?
2. What questions would you expect to receive from the Executive Committee of Gardner Novelties? Identify two questions that you could be asked and address how you would answer them.