Exp22_Excel_Ch07_CumulativeAssessment_Variation_Shipping

Exp22_Excel_Ch07_CumulativeAssessment_Variation_Shipping

Exp22 Excel Ch07 CumulativeAssessment Variation Shipping

Excel Chapter 7 Cumulative Assessment – Variation Shipping

  

Project Description:

You work for a company that sells cell phone accessories. The company has distribution centers in three states. You want to analyze shipping data for one week in April to determine if shipping times are too long. You will perform other analysis and insert a map. Finally, you will prepare a partial loan amortization table for a new delivery van.     

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

 

The Week worksheet contains data   for the week of August 5.
 

  In cell D7, insert the appropriate date function to calculate the number of   days between the Date Arrived and Date Ordered. Copy the function to the   range D8:D35. 

 

You want to display the weekday   for the arrival dates.
 

  In cell E7, insert the WEEKDAY function to identify an integer representing   the weekday of the Date Arrived. Copy the function to the range E8:E35. 

 

You need to format the WEEKDAY   function results with a custom number format.
 

  Select the range E7:E35, apply the custom number format dddd, and apply center horizontal   alignment.c 

 

Next, you want to display the   city names that correspond with the city airport codes.
 

  In cell G7, insert the SWITCH function to evaluate the airport code in cell   E7. Include mixed cell references to the city names in the range G2:G4. Use   the airport codes as text for the Value arguments: AUS for Austin, DFW for Dallas-Fort Worth, and IAH for Houston. Copy the function   to the range G8:G35. 

 

Now you want to display the   standard shipping costs by city.
 

  In cell I7, insert the IFS function to identify the shipping cost based on   the airport code and the applicable shipping rates in the range H2:H4. Enter   the airport codes in this sequence: AUS, DFW, IAH (with their respective   costs). Use relative and mixed references correctly. Copy the function to the   range I8:I35. 

 

Finally, you want to calculate a   partial shipping refund if two conditions are met.
 

  In cell J7, insert an IF function with a nested AND function to determine   shipping refunds. The AND function should ensure both conditions are met:   Total Days is greater than Total Days Delivery Goal (cell C3) and Order Total   is equal to or greater than Order Total Threshold (cell C2). If both   conditions are met, the refund is 50% (cell C4) of the Shipping Cost.   Otherwise, the refund is $0. Use mixed references as needed. Copy the   function to the range J8:J35. 

 

The Stats worksheet contains   similar data. Now you want to enter summary statistics.
 

  In cell B2, insert the COUNTIF function to count the number of shipments for   Austin (cell B1). Use appropriate mixed references to the range argument to   keep the column letters the same. Copy the function to the range C2:D2. 

 

In cell B3, insert the SUMIF   function to calculate the total orders for Austin (cell B1). Use appropriate   mixed references to the range argument to keep the column letters the same.   Copy the function to the range C3:D3. 

 

In cell B4, insert the AVERAGEIF   function to calculate the average number of days for shipments from Austin   (cell B1). Use appropriate mixed references to the range argument to keep the   column letters the same. Copy the function to the range C4:D4. 

 

Now you want to focus on   shipments from Houston where the order was greater than $1,000.
 

  In cell C7, insert the COUNTIFS function to count the number of orders where   the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000.   

 

In cell C8, insert the SUMIFS   function to calculate the total orders where the Airport Code is IAH (Cell   D1) and the Order Total is greater than $1,000. 

 

In cell C9, insert the MAXIFS   function to return the highest order total where the Airport Code is IAH   (Cell D1) and the Order Total is greater than $1,000.

 

On the Map worksheet, insert a   map for the states and revenues. Cut and paste the map in cell C1.

 

Format the data series to show   only regions with data and show all map labels.

 

Change the map title to August 5-9   Gross Revenue.

 

Use the Loan worksheet to   complete the loan amortization table.
 

  In cell F2, insert the IPMT function to calculate the interest for the first   payment. Copy the function to the range F3:F25. (The results will update   after you complete the other functions and formulas.)

 

In cell G2, insert the PPMT   function to calculate the principal paid for the first payment. Copy the   function to the range G3:G25.

 

In cell H2, insert a formula to   calculate the ending principal balance. Copy the formula to the range H3:H25.

 

Now you want to determine how   much interest was paid during the first two years.
 

  In cell B10, insert the CUMIPMT function to calculate the cumulative interest   after the first two years. Make sure the result is positive.

 

In cell B11, insert the CUMPRINC   function to calculate the cumulative principal paid at the end of the first   two years. Make sure the result is positive.

 

You want to perform a what-if   analysis to determine the rate if the monthly payment is $1,150 instead of   $1,207.87.
 

  In cell B15, insert the RATE function to calculate the necessary monthly rate   given the NPER, proposed monthly payment, and loan. Make sure the result is   positive.

 

Finally, you want to convert the   monthly rate to an APR.
 

  In cell B16, insert a formula to calculate the APR for the monthly rate in   cell B15.

 

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

Unit 1 DB: Core Principles

You have been hired as the new Chief Information Security Officer (CISO) for PostCyberSolutions (PCS) LLC to overhaul the cyber security program. In addition to local PCS information systems, the CISO is responsible for ensuring the various program managers maintain security compliance for customer systems under PCS security cognizance. PCS holds several government contracts in various regulatory environments making CISO a challenging position.

One of your first actions to improve the security program is to create a working group between the Information Technology, Engineering, and Security departments to foster better communication, address information system security challenges, and to work off action items. During this week’s working group meeting, an informative debate breaks out regarding the three core principles of information security and which one is most important: confidentiality, integrity, or availability. The working group debate stemmed from discussions regarding security protections for PCS’s new contract to overhaul the early warning missile detection system for the Ministry of Defense in Istanbogus.

Rank each of the three security principles in the order of importance for this system. Explain your rationale for each ranking to include any factors, criteria, and assumptions you made in selecting the order of precedence. Include at least one research reference and associated in-text citation using APA standards. In your replies to your peers discuss their ranking/rationale.

ECONOMIC ANALYSIS2

 

Your assignment should be about two pages, double-spaced.

Prompt 

For the final paper, you are required to pick a current economic topic that relates to the material we have covered in this course. You will research and find an article that covers the topic you have chosen. You can use an article online or offline from any reputable source. You will write up a review of the article and integrate course concepts into your review. Please make sure you both summarize the article and discuss how it relates to the course.

Operation excellence

 

OE-5

1.  Describe the role of a systems analyst.

2.  What are some of the different roles for a computer engineer?

3.  What are the duties of a computer operator?

4.  What does the CIO do?

5.  Describe the job of a project manager.

6.  Explain the point of having two different career paths in information systems.

7.  What are the advantages and disadvantages of centralizing the IT function?

8.  What impact has information technology had on the way companies are organized?

9.  What are the five types of information-systems users?

10. Why would an organization outsource?

11. How is the IT function organized in your school or place of employment? Create an organization chart showing how the IT organization fits into your overall organization. Comment on how centralized or decentralized the IT function is.

12. What are the steps in the SDLC methodology?

13. What is RAD software development?

14. What makes the lean methodology unique?

15. What are three differences between second-generation and third-generation languages?

16. Why would an organization consider building its own software application if it is cheaper to buy one?

17. What is responsive design?

18. What is the relationship between HTML and CSS in website design?

19. What is the difference between the pilot implementation methodology and the parallel implementation methodology?

20. What is change management?

21. What are the four different implementation methodologies?

22. Which software-development methodology would be best if an organization needed to develop a software tool for a small group of users in the marketing department? Why? Which implementation methodology should they use? Why?

100

23. How is the IT function organized in your school or place of employment? Create an organization chart showing how the IT organization fits into your overall organization. Comment on how centralized or decentralized the IT function is.

100

24. Review the section on dealing with multiple locations and outsourcing.  Review figure 7.2 and note how virtual team communications further reiterates the importance of this model. 

250 with references

25. Review the Siemens AG case study.  Note the importance of understanding the interrelationships amongst all the senior leaders at every location.  Pay special attention to Figure 8.1 and Figure 8.2. Note how the corporate CIO should engage with each of the regional leaders.  Why is this important?

250 with references

FOUNDATIONS OF DATA SCIENCE

  

There are concerns among industrialists who are benefiting from dams and dam constructions that there will be claims that high mercury levels in fish are related to the presence of dams in the reservoir’s drainage. Does the data support or refute this claim? Provide some justifications.

C# Polling

 In this exercise, you’ll write a simple polling program that allows users to rate five social-consciousness issues from 1 (least important) to 10 (most important). Pick five causes that are important to you (for example, political issues, global environmental issues). Use a one-dimensional array topics (of type String) to store the five causes.  To summarize the survey responses, use a 5-row, 10-column two-dimensional array responses (of type Integer), each row corresponding to an element in the topics array. When the program runs, it should ask the user to rate each issue. Have your friends and family respond to the survey. Then have the program display a summary of the results, including:  

  1. A tabular report with the five topics down the left side and the 10 ratings across the top, listing in each column the number of ratings received for each topic. 
  2. To the right of each row, show the average of the ratings for that issue. 
  3. Which issue received the highest point total? Display both the issue and the point total. 
  4. Which issue received the lowest point total? Display both the issue and the point total.

Data

Stephanie: Gun control 8, immigration 10, health care 6, taxes 2, global warming 9 

Hassan: Gun control 7, immigration 6, health care 8, taxes 9, global warming 5

Ryan: Gun control 2, immigration 3, health care 8, taxes 9, global warming 1 

Lori: Gun control 3, immigration 2, health care 8, taxes 9, global warming 1 

David: Gun control 7, immigration 9, health care 8, taxes 6, global warming 10 

MKT455 week 10

 

You have heard of the 4P’s of Marketing? Price, Place, Promotion, and Product…

Did you know that there are also 7R’s called the 7R’s of Relationship Marketing?

Read the following brief article on the 7R’s of Relationship Marketing:

https://smallbusiness.chron.com/seven-rs-marketing-mix-64543.html (Links to an external site.)

Please answer the following questions:

  1. Briefly define each of the 7 R’s using any Internet references of your choosing;
  2. In your opinion, and supported by 1-2 Internet references, should marketers use the 4P’s of marketing in selling a product, the 7R’s of Relationship Marketing or both of them together (4P’s and 7R’s)?