Python API – Weather Py

/in /by

In this example, you’ll be creating a Python script to visualize the weather of 500+ cities across the world of varying distance from the equator. To accomplish this, you’ll be utilizing a simple Python library, the OpenWeatherMap API, and a little common sense to create a representative model of weather across world cities.

Your first requirement is to create a series of scatter plots to showcase the following relationships:

  • Temperature (F) vs. Latitude
  • Humidity (%) vs. Latitude
  • Cloudiness (%) vs. Latitude
  • Wind Speed (mph) vs. Latitude

After each plot add a sentence or too explaining what the code is and analyzing.

Your second requirement is to run linear regression on each relationship, only this time separating them into Northern Hemisphere (greater than or equal to 0 degrees latitude) and Southern Hemisphere (less than 0 degrees latitude):

  • Northern Hemisphere – Temperature (F) vs. Latitude
  • Southern Hemisphere – Temperature (F) vs. Latitude
  • Northern Hemisphere – Humidity (%) vs. Latitude
  • Southern Hemisphere – Humidity (%) vs. Latitude
  • Northern Hemisphere – Cloudiness (%) vs. Latitude
  • Southern Hemisphere – Cloudiness (%) vs. Latitude
  • Northern Hemisphere – Wind Speed (mph) vs. Latitude
  • Southern Hemisphere – Wind Speed (mph) vs. Latitude

After each pair of plots explain what the linear regression is modeling such as any relationships you notice and any other analysis you may have.

Optional You will be creating multiple linear regression plots. To optimize your code, write a function that creates the linear regression plots.

Your final notebook must:

  • Randomly select at least 500 unique (non-repeat) cities based on latitude and longitude.
  • Perform a weather check on each of the cities using a series of successive API calls.
  • Include a print log of each city as it’s being processed with the city number and city name.
  • Save a CSV of all retrieved data and a PNG image for each scatter plot.
 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

CS510 Week 2 Assignment

/in /by

Assignment 1: Error Handling in an Activity Diagram

Refer to the activity diagram Figure 2-15 on page 59 of the textbook. The diagram omits several error-handling pathways.

Write a two to three (2-3) page paper in which you:

  1. Using Microsoft Visio or an open source alternative such as Dia,  update the diagram to include two (2) error-handling pathways. Note: The  graphically depicted solution is not included within the required page  length.
  2. Produce a narrative which describes the added error-handling pathways that includes:
    1. an overview of the errors being checked
    2. an explanation of the main reasons why checking for such errors is important
    3. an overview of other possible errors
  3. Use at least three (3) quality resources outside of the suggested  resources in this assignment. Note: Wikipedia and similar Websites do  not qualify as quality resources.

Your assignment must follow these formatting requirements:

  • Be typed, double spaced, using Times New Roman font (size 12), with  one-inch margins on all sides; citations and references must follow APA  or school-specific format. Check with your professor for any additional  instructions.
  • Include a cover page containing the title of the assignment, your  name, the professor’s name, the course title, and the date. The cover  page and the reference page are not included in the required assignment  page length.
  • Include charts or diagrams created in Visio or an open source  alternative such as Dia. The completed diagrams / charts must be  imported into the Word document before the paper is submitted.

The specific course learning outcomes associated with this assignment are:

  • Analyze techniques for requirements determination, collection, and organization.
  • Use technology and information resources to research issues in systems analysis and design.
  • Write clearly and concisely about advanced systems analysis and  design topics using proper writing mechanics and technical style  conventions.
 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

LABSIMS

/in /by

Need 2 labs completed, the first sooner as soon as possible and the second by April 30th

ITCO101  Unit 1 Lab

 

Introduction LabSim PC Pro is used to provide a simulated, hands-on laboratory for the purposes of learning about important hardware and software concepts.  In an online environment it is impossible for students to work directly with hardware or software in administrative scenarios.  Therefore, LabSim is the tool that supports students in their pursuit to understand difficult concepts in an online environment.

Use of LabSim PC Pro requires a one-time registration process. You should have received an activation code from the bookstore (Words of Wisdom). Notify the bookstore immediately if you have not yet received your activation code. Contact them at aiuonlinebooks@wordsofwisdombooks.com.

Instructions for accessing LabSim can be found in the Course Materials section. You can also contact your instructor with any questions. You will have access to PC Pro for 36 months (3 years) from the date of registration.

Note that LabSim PC Pro is aligned with CompTIA’s A+ certification. Completing this lab will not qualify you to complete the exam. One must complete several courses in order to cover all the concepts included in the certification exam. Further, completing the coursework does not conclude with automatic certification. Those students who wish to pursue certification may do so outside the classroom. For those students that qualify, they may contact the Registrar’s office for receipt of a discounted certification voucher honored by CompTIA.

Hardware Requirements:  Refer here to verify that you meet the necessary Hardware Requirements. Note that you will need speakers.

Expected Time for Completion:  Eight (8) hours or more depending on time necessary to familiarize yourself with tool and the particular Sections you choose to complete. You may complete the tasks in numerous iterations. You do not need to complete all the lab tasks in one sitting.

Lab Description Upon completion, you will:

 navigate LabSim modules;  experience and utilize a variety of learning tools including videos, Fact sheets, images, etc.;  interact with hardware and software in a simulated environment;  describe the various types of computer system components;  install various components including power supplies, motherboards, processors, memory, BIOS/UEFI, expansion cards, video cards, and sound cards .

 

Lab Tasks Each module contains numerous Sections. Each section contains sub-sections. The last sub-section contains Practice Questions related to the topic of that sub-section. The Practice Questions are not required. Completing the Practice Questions are optional. The results of the Practice Questions do not impact your course grade one way or the other.

Module 1.0 Computing Overview

Complete the following three sections and the specified sub-sections to become familiar with the LabSim environment and basic computer hardware. For example, for section 1.2, complete sub-sections  1.2.1, 1.2.2, 1.2.3, 1.2.4.

 

Section 1.1 Course Introduction

Section 1.2 Using the Simulator (Review demos and complete simulations: Sections 1.2.1, 1.2.2, 1.2.3, 1.2.4)

Section 1.3 Hardware Basics (Review videos and complete simulation: Sections 1.3.1, 1.3.3, 1.3.6, 1.3.7)

Module 3.0 System Components

Below are numerous sections covering System Components. Many of these sections include handson simulations. You may complete any or all of these sections. You are required to choose seven sections, at a minimum. For example, you may choose Section 3.1, Section 3.2, Section 3.3, Section 3.5, Section 3.7, Section 3.8, and Section 3.14. Again, choose what is of interest to you.  Review all videos, demos and simulations included in your chosen sections.

You are required to choose and complete seven sections only. But you may choose to complete anywhere from seven sections to all the sections, depending upon your time availability.  Note that you are permitted to return to this module at any time throughout the course and the program if you wish to complete more than your chosen sections required for this assignment. However, the grade for this will be determined based upon the chosen sections completed during Unit 1.

 

Section 3.1 Cases and Form Factors (Review video only: Section 3.1.1)

Section 3.2 Power Supplies (Review videos and complete simulation:  Sections 3.2.1, 3.2.3, 3.2.4, 3.2.5)

Section 3.3 Motherboards and Buses (Review videos and simulation: Sections 3.3.1, 3.3.3, 3.3.5)

Section 3.5 Processors (Review video, demo and simulations: Sections 3.5.1, 3.5.5, 3.5.7, 3.5.8)

Section 3.7 Memory (Review videos and RAM Facts: Sections 3.7.1, 3.7.2, 3.7.4)

Section 3.8 Memory Installation (Review video, demos, and complete simulations: Sections 3.8.1, 3.8.3, 3.8.4, 3.8.5, 3.8.7)

Section 3.10 BIOS/UEFI (Review videos, demos, and complete simulations: Sections 3.10.1, 3.10.2, 3.10.4, 3.10.5, 3.10.6, 3.10.7, 3.10.8)

Section 3.11 Expansion Cards (Review videos and complete simulation: Sections 3.11.1, 3.11.3, 3.11.4)

Section 3.12 Video (Review video, demo and complete simulation: Sections 3.12.1, 3.12.3, 3.12.5)

Section 3.13 Audio (Review videos, demo and complete simulation: Sections 3.13.1, 3.13.2, 3.13.4, 3.13.7)

Section 3.14 Cooling (Review video: Section 3.14.1)

The above Sections include the following hands-on simulations.

1.2.2 Explore the Lab Interface 1.2.4 Connect Internal Components 1.3.6 Set Up a Computer

3.2.5 Install a Power Supply 3.3.5 Choose and Install a Motherboard 3.5.7 Select and Install a Processor 1 3.5.8 Select and Install a Processor 2 3.8.3 Select Memory by Sight 3.8.7 Install Triple Channel Memory 3.10.7 Find BIOS/UEFI Settings 3.10.8 Clear CMOS Settings 3.11.4 Install Expansion Cards 3.12.5 Upgrade a Video Card 3.13.7 Select and Install a Sound Card

A word document goes with it

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Computer Homework

/in /by

Documentation

Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a
Merchandise Projections
FORMATTING, FORMULAS, AND CHARTS
Author: Jorge Miami
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Sheet1

Aggregate Unit Averages
Sales Units Sold Percent of Sales Selling Price Production Cost Profit
Discs
DVDs $49,093 12.9500 $1.05 $11.90
CD Soundtracks $52,637 7.9500 $0.70 $7.25
Clothing
Hoodies $75,863 29.9500 $13.80 $16.15
T-shirts $22,298 15.9500 $7.19 $8.76
Tank tops $20,074 13.9500 $5.34 $8.61
Hats
Baseball Caps $37,525 24.9500 $8.69 $16.26
Bennies $34,413 18.9500 $3.51 $15.44
Bags $53,835 14.9500 $4.65 $10.30
Plushies $19,853 17.9500 $10.02 $7.93
Posters $25,991 11.9500 $4.47 $7.48
Buttons $29,923 5.9500 $1.28 $4.67
Vinyl Decals $40,724 4.9500 $1.93 $3.02
TOTAL $462,229
High:
Low:
Average:

A Little Purple Productions

Projection Data

Convention Booth – Sales Forecast
2018 2019 Trend
Discs 101730.4 104500 107200 109900 112600
Clothing 118235.5 119000 123000 127000 131000
Hats 71938 74000 76000 78000 81000
Bags 53834.95 54000 57200 58000 59500
Plushies 19852.7 20500 22000 23500 25000
Posters 25991.25 28000 30000 36000 40000
Buttons 29922.55 32000 39000 44000 47000
Vinyl Decals 40723.65 43000 44500 47000
Total
55000

Projection Chart

Sales Forecast by Year

Discs

2018 2019 101730.4 104500 107200 109900 112600 Clothing

2018 2019 118235.49999999999 119000 123000 127000 131000 Hats

2018 2019 71938 74000 76000 78000 81000 Bags

2018 2019 53834.95 54000 57200 58000 59500 Plushies

2018 2019 19852.7 20500 22000 23500 25000 Posters

2018 2019 25991.25 28000 30000 36000 40000 Buttons

2018 2019 29922.55 32000 39000 44000 47000 Vinyl Decals

2018 2019 40723.65 43000 44500 47000

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Week 4 Assignments

/in /by

Do the following review questions:

4.1 List ways in which secret keys can be distributed to two communicating parties.

 

4.2 What is the difference between a session key and a master key?

 

4.3 What is a key distribution center?

 

4.4 What entities constitute a full-service Kerberos environment?

 

4.5 In the context of Kerberos, what is a realm?

 

4.6 What are the principal differences between version 4 and version 5 of Kerberos?

 

4.7 What is a nonce?

 

4.8 What are two different uses of public-key cryptography related to key distribution?

 

4.9 What are the essential ingredients of a public-key directory?

 

4.10 What is a public-key certificate?

4.11 What are the requirements for the use of a public-key certificate scheme?

 

4.12 What is the purpose of the X.509 standard?

 

4.13 What is a chain of certificates?

 

4.14 How is an X.509 certificate revoked?

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

ER Diagram

/in /by

HW-1

1. Draw an ER diagram for the given scenario

Scenario:

a. Suppose that you are designing a schema to record information about reality shows on TV. Your database needs to record the following information:

b. For each reality show, its name, genre, basic_info and participant’s name. Any reality show has at least two or more participants.

c. For each producer, the company name, company country. A show is produced by exactly one producer. And one producer produces exactly one show.

d. For each television, its name, start year, head office. A television may broadcast multiple shows. Each show is broadcasted by exactly one television.

e. For each user, his/her username, password, and age. A user may rate multiple shows, and a show may be rated by multiple users. Each rating has a score of 0 to 10.

f. Draw an entity relationship diagram for this database

 

 

 

2. Assume we have the following application that models soccer teams, the games they play, and the players in each team. In the design, we want to capture the following:

· We have a set of teams, each team has an ID (unique identifier), name, main stadium, and to which city this team belongs.

· Each team has many players, and each player belongs to one team. Each player has a number (unique identifier), name, DoB, start year, and shirt number that he uses.

· Teams play matches, in each match there is a host team and a guest team. The match takes place in the stadium of the host team.

· For each match we need to keep track of the following:

· The date on which the game is played

· The final result of the match o The players participated in the match. For each player, how many goals he scored, whether or not he took yellow card, and whether or not he took red card.

· During the match, one player may substitute another player. We want to capture this substitution and the time at which it took place.

· Each match has exactly three referees. For each referee we have an ID (unique identifier), name, DoB, years of experience. One referee is the main referee and the other two are assistant referee.

 

 

From above ERD diagram identify the following:

 

a. List all entities with their identifiers

b. List all weak entities

c. Identify recursive relation

d. Identify all multi-valued attributes

e. Identify an identifying relationship type

f. Identify One-to-one (1:1) relationship

g. Identify One-to-many (1:N)

h. Identify Many-to-many

i. Identify derived attribute(s)

j. Identify Composed attribute(s)

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Exp19_Excel_App_Cap_Comp_Tech_Store

/in /by
 Exp19_Excel_App_Cap_Comp_Tech_Store

 

Project Description:

After graduating from college, you and three of your peers founded the software company TechStore Unlimited (TSU). TSU provides an online market place that fosters business to business (B2B), business to consumer (B2C), and consumer to consumer sales (C2C). As one of the company’s principal owners, you have decided to compile a report that details all aspects of the business, including: employee payroll, facility management, sales data, and product inventory. To complete the task you will duplicate existing formatting, import data from an Access database, utilize various conditional logic functions, complete an amortization table, visualize data with PivotTables and Power Maps, connect and transform several external data sources, and lastly you will inspect the workbook for issues.

 

Start   Excel. Open Exp19_Excel_AppCapstone_Comp.xlsx. Grader has automatically added   your last name to the beginning of the filename.

 

Fill the range A1:E1 from the   Employee_Info worksheet across all worksheets, maintaining the formatting.

 

Make the New_Construction   worksheet active and create Range Names based on the data in the range A6:B9.

 

Ungroup the worksheets and   ensure the Employee_Info worksheet is active. Click cell G6 and enter a   nested logical function that calculates employee 401K eligibility. If the   employee is full time (FT) and was hired before the 401k cutoff date 1/1/19,   then he or she is eligible and Y should be displayed, non-eligible employees should be indicated   with a N. Be sure to utilize the date   located in cell H3 as a reference in the formula. Use the fill handle to copy   the function down completing the range G6:G25.

 

Apply conditional formatting to   the range G6:G25 that highlights eligible employees with Green Fill with Dark   Green text. Eligible employees are denoted with a Y in column G.

 

Create a Data Validation list in   cell J7 based on the employee IDs located in the range A6:A25. Add the Input   Message Select Employee ID and use the Stop Style Error Alert.

 

Enter a nested INDEX and MATCH   function in cell K7 that examines the range B6:H25 and returns the   corresponding employee information based on the match values in cell J7 and   cell K6. Note K6 contains a validation list that can be used to select   various lookup categories. Use the Data Validation list in cell J7 to select   Employee_ID 31461 and select Salary in cell K6 to test the   function.

 

Enter a conditional statistical   function in cell K14 that calculates the total number of PT employees. Use the range E6:E25   to complete the function.

 

Enter a conditional statistical   function in cell K15 that calculates the total value of PT employee salaries. Use the range   E6:E25 to complete the function.

 

Enter a conditional statistical   function in cell K16 that calculates the average value of PT employee salaries. Use the range   E6:E25 to complete the function.

 

Enter a conditional statistical   function in cell K17 that calculates the highest PT employee salary. Use the range E6:E25 to complete   the function.

 

Apply Currency Number Format to   the range K15:K17.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Assignment Help Example on Foundations of Algorithms Homework 6

/in /by

Assignment Help Example on Foundations of Algorithms Homework 6

(Assignment Help Example on Foundations of Algorithms Homework 6) Homework #6

All members of the collaboration group are expected to participate fully in solving collaborative problems, and peers will assess performance at the end of the assignment. Note, however, that each student is required to write up their solutions individually. Common solution descriptions from a collaboration group will not be accepted. Furthermore, to receive credit for a collaborative problem, each student in the collaboration group must actively and substantively contribute to the collaboration. (Assignment Help Example on Foundations of Algorithms Homework 6)

Self-Study Problems All of the following problems come from the textbook and have solutions posted on the web at

http://mitpress.mit.edu/algorithms.

You are permitted to use this site to examine solutions for these problems as a means of self-checking your solutions. These problems will not be graded. Only problems from Chapter 15 are included since the authors have not provided sample problems for chapter 29.

Problems: 15.2-5, 15.3-1, 15.4-4, 15-4, 16.1-4, 16.2-2, 16.2-7,

Problems for Grading 1. [20 points] Suppose you are consulting for a company that manufactures PC equipment and ships it

to distributors all over the country. For each of the next n weeks, they have a projected supply si of equipment (measured in pounds) that has to be shipped by an air freight carrier. Each week’s supply can be carried by one of two air freight companies, A or B.

• Company A charges a fixed rate r per pound (so it costs r × si to ship a week’s supply si). • Company B makes contracts for a fixed amount c per week, independent of the weight. However,

contracts with company B must be made in blocks of four consecutive weeks at a time.

A schedule for the PC company is a choice of air freight company (A or B) for each of the n weeks with the restriction that company B, whenever it is chosen, must be chosen for blocks of four contiguous weeks in time. The cost of the schedule is the total amount paid to company A and B, according to the description above.

Give a polynomial-time algorithm that takes a sequence of supply values s1, s2, . . . , sn and returns a schedule of minimum cost. For example, suppose r = 1, c = 10, and the sequence of values is

11, 9, 9, 12, 12, 12, 12, 9, 9, 11.

Then the optimal schedule would be to choose company A for the first three weeks, company B for the next block of four contiguous weeks, and then company A for the final three weeks.

2. [20 points] CLRS 29.4-3: Write down the dual of the maximum-flow linear program, as given in lines (29.47)–(29.50) on page 862 of the textbook. Explain how to interpret this formulation as a minimum- cut problem.

3. [20 points] Collaborative Problem: As some of you know well, and others of you may be interested to learn, a number of languages (including Chinese and Japanese) are written without spaces between the words. Consequently, software that works with text written in these languages must address the word segmentation problem—inferring likely boundaries between consecutive words in the text. If English were written without spaces, the analogous problem would consist of taking a string like “meetateight” and deciding that the best segmentation is “meet at eight” (and not “me et at eight” or “meet ate ight” (Assignment Help Example on Foundations of Algorithms Homework 6)

1

 

 

or any of a huge number of even less plausible alternatives). How could we automate this process?

A simple approach that is at least reasonably effective is to find a segmentation that simply max- imizes the cumulative “quality” of its individual constituent words. Thus, suppose you are given a black box that, for any string of letters x = x1x2 · · ·xk, will return a number quality(x). This number can be either positive or negative; larger numbers correspond to more plausible English words. (So quality(“me”) would be positive while quality(“ight”) would be negative.)

Given a long string of letters y = y1y2 · · · yn, a segmentation of y is a partition of its letters into contiguous blocks of letters, each block corresponding to a word in the segmentation. The total quality of a segmentation is determined by adding up the qualities of each of its blocks. (So we would get the right answer above provided that quality(“meet”) + quality(“at”) + quality(“eight”) was greater than the total quality of any other segmentation of the string.) Give an efficient algorithm that takes a string y and computes a segmentation of maximum total quality. You can treat a single call to the black box computing quality(x) as a single computational step. Prove the correctness of your algorithm and analyze its time complexity.

4. Collaborative Problem. In the course content, we explained how we can solve two-player zero-sum games using linear programming. One of the games we described is called “Rock-Paper-Scissors.” In this problem, we are going to examine this game more closely. Suppose we have the following “loss” matrix for Player 1 (i.e., we are showing how much Player 1 loses rather than gains, so reverse the sign): (Assignment Help Example on Foundations of Algorithms Homework 6)

A =

 0 1 −1−1 0 1 1 −1 0

 . (a) [10 points] What is the expected loss for Player 1 when Player 1 plays a mixed strategy x =

(x1, x2, x3) and Player 2 plays a mixed strategy y = (y1, y2, y3)?

(b) [10 points] Show that Player 1 can achieve a negative expected loss (i.e., an expected gain) if Player 2 plays any strategy other than y = (y1, y2, y3) =

( 1 3 ,

1 3 ,

1 3

) .

(c) [10 points] Show that x = ( 1 3 ,

1 3 ,

1 3

) and y =

( 1 3 ,

1 3 ,

1 3

) form a Nash equilibrium.

(d) [10 points] Let x = ( 1 3 ,

1 3 ,

1 3

) as in part (c). Is it possible for (x,y) to be a Nash equilibrium for

some mixed strategy y′ ̸= ( 1 3 ,

1 3 ,

1 3

) ? Explain. (Assignment Help Example on Foundations of Algorithms Homework 6)

2

References

https://www.youtube.com/watch?v=05GSr4LBsOY

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Computer Science Assignments 6 & 8

/in /by

Assignment #8

1. Using the Employees table of Figure 14.6, what is the result of the following SQL query?

SELECT *

FROM Employees

WHERE HoursWorked < 100;

2. Write an SQL query that retrieves first and last names and pay rate, ordered by PayRate, from the Employees table of Figure 14.6.

3. Using the Employees table of Figure 14.6 and the Insur-ancePolicies table of Figure 14.7, what is the result of the following SQL query? (The # marks allow the date to be treated numerically.)

SELECT ID, PlanType

FROM Employees, InsurancePolicies

WHERE Birthdate > #1/01/1960# AND ID = EmployeelD;

Table 14.6

ID LastName FirstName BirthDate PayRate HoursWorked

116 Kay Janet 3/29/1956 $16.60 94
123 Perreira Francine 8/15/1987 38.50 185
149 Takasano Frederick 5/23/1966 $12.35 250
171 Kay John 11/17/1954 $17.80 245
165 Honou Morris 6/9/1988 $6.70 53

Table 14.7

EmployeeID PlanType DateIssued

171 B2 10/18/1974
171 C1 6/21/1982
149 B2 8/16/1990
149 A1 5/23/1995
149 C2 12/18/1999

Assignment #6

1. Discuss some situations where a sequential storage device such as tape could be a useful form of mass storage and why?

2. Assuming a square two-dimensional memory organization, what are the dimensions of a memory containing 1 MB (2^20) bytes of storage?

How large would the MAR be?

How many bits are sent to the row and column decoders?

How many output lines would these decoders have?

3. Consider the following structure of the Instruction Register:

OPCODE Address 1 Address 2

6 bits 18 bits 18 bits

a. What is the maximum number of distinct operation codes that can be recognized and executed by the processor on this machine?

b. What is the maximum memory size on this machine?

c. How many bytes are required to store each complete instruction?

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

3rd Excel Exam SU1 2021

/in /by

3rd Excel Exam SU1 2021

After graduating from college, you and three of your peers founded the software company TechStore Unlimited (TSU). TSU provides an online market place that fosters business to business (B2B), business to consumer (B2C), and consumer to consumer sales (C2C). As one of the company’s principal owners, you have decided to compile a report that details all aspects of the business, including: employee payroll, facility management, sales data, and product inventory. To complete the task you will duplicate existing formatting, import data from an Access database, utilize various conditional logic functions, complete an amortization table, visualize data with PivotTables and Power Maps, connect and transform several external data sources, and lastly you will inspect the workbook for issues.

 

Start   Excel. Open 3rd Excel Exam SU1 2021.xlsx. Grader has automatically added   your last name to the beginning of the filename.

 

Create a Data Validation list in   cell J7 based on the employee IDs located in the range A6:A25. Add the Input   Message Select Employee ID and use the Stop Style Error Alert.
Hint: Data Validation is located on the Data tab in the Data Tools group.
In cell K7, enter an INDEX   functions with two nested MATCH functions. The first MATCH function will look up the row number of the employee   number in cell J7 in the range A6:A25. The second MATCH function will look up the column number of the value   in cell K6 in the range B5:H5. Note   K6 contains a validation list that can be used to select various lookup   categories.
Hint: The arguments of the INDEX are array (B6:H25), Row Number,Column   Number. The purpose of the two MATCH   functions is to look up the ROW number and COLUMN number. I suggest you do the first MATCH function   to ensure it works and then nest it inside of the INDEX function. Then nest the second MATCH function in the   INDEX function. To test the INDEX   function, use the Data Validation list in cell J7 to select Employee_ID 31461 and select Salary in cell K6. Did it   look up the correct salary for the specified employee number?
Click cell K11 and type FT. Click cell A28 and type Full Time   Employees.

 

Use the Format Painter to apply   the formatting from the cell A3 to the range A28:B28.
Hint: The Format Painter is located on the
Home tab in the Clipboard group.

 

Use Advanced Filtering to   restrict the data to only display FT employees based on the criteria in the   range K10:K11. Place the results in cell A29.
Hint: Advanced Filtering is located on the Data tab in the Sort & Filter   group.
Enter a database function (DCOUNTA)   in cell K18 to determine the total number of FT employees. To complete the   function, use the range A5:H25 as the database argument, cell E5 for the   field, and the range K10:K11 for the criteria.
Hint: To access DATABASE functions, click insert function, and select   Database from the function category menu.
Enter a database function in   cell K19 (DSUM) to determine the total value of FT employee salaries. To   complete the function, use the range A5:H25 as the database argument, cell H5   for the field, and the range K10:K11 for the criteria.
Hint: To access DATABASE functions, click insert function, and select   Database from the function category menu.
Enter a database function   (DAVERAGE) in cell K20 to determine the average FT employee salary. To   complete the function, use the range A5:H25 as the database argument, cell H5   for the field, and the range K10:K11 for the criteria.
Hint: To access DATABASE functions, click insert function, and select   Database from the function category menu.
Enter a database function in   cell K21(DMAX) to determine the highest FT salary. To complete the function,   use the range A5:H25 as the database argument, cell H5 for the field, and the   range K10:K11 for the criteria.
Hint: To access DATABASE functions, click insert function, and select   Database from the function category menu.
Format the range K19:K21 with   Currency Number Format.
Hint: Currency Number Format is located on the
Home tab in the Number Group.

 

Use PowerQuery to connect and   open the Orders table in the eApp_Cap_Orders.accdb   database. Use the Query editor to format column A with Date number format and   load the table. Rename the worksheet Orders.
Hint: Data can be imported using Get & Transform data tools located on   the Data tab in the Get & Transform data group.
Adapt the previous step to   connect and load the Warehouse table.
Hint: Data can be imported using Get & Transform data tools located on   the Data tab in the Get & Transform data group.
Connect to, but don’t load the   Inventory table from the eApp_Cap_Orders.accdb   database.

 

Create the following   relationships.
Relationship 1
Table Name
Inventory
Column (Foreign)
Warehouse
Table
Warehouse
Column (Primary)
Warehouse
Relationship 2
Table
Orders
Column (Foreign)
Item_Number
Table
Inventory
Column (Primary)
Item_Number
Hint: Relationships are located on the Data tab in the Data Tools group.
Use PowerPivot to create a blank   PivotTable on a new worksheet. Add the following fields to the PivotTable.
Rows
Warehouse: Location
Warehouse: Warehouse
Inventory:
Item_Number
Values
Inventory:
Current_InventoryInventory:
Total_Value

 

Insert a Slicer based on   Warehouse. Place the upper left corner of the Slicer inside the borders of   cell F3.
Hint: Slicers are located on the Insert tab in the Filters group.
Create a 3D PowerMap that   displays the location of all warehouses based on the City geographic type.   Rename the worksheet Inventory.
Hint: 3D Maps is located on the Insert tab in the Tours group.
Save and close the 3rd Excel   Exam SU1 2021. Go to the Test   Presentation Window. Upload the exam   and submit it for grading

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!