Introduction To BIG DATA Midterm Exam Solution

/in , /by

Introduction To BIG DATA Midterm Exam Solution

QUESTION 1

What are the three characteristics of Big Data, and what are the main considerations in processing Big Data?

 

QUESTION 2

Explain the differences between BI and Data Science.

 

QUESTION 3

Briefly describe each of the four classifications of Big Data structure types. (i.e. Structured to Unstructured)

 

QUESTION 4

List and briefly describe each of the phases in the Data Analytics Lifecycle.

 

QUESTION 5

In which phase would the team expect to invest most of the project time? Why? Where would the team expect to spend the least time?

 

QUESTION 6

Which R command would create a scatterplot for the dataframe “df”, assuming df contains values for x and y?

 

QUESTION 7

What is a rug plot used for in a density plot?

 

QUESTION 8

What is a type I error? What is a type II error? Is one always more serious than the other? Why?

 

QUESTION 9

Why do we consider K-means clustering as a unsupervised machine learning algorithm?

 

QUESTION 10

Detail the four steps in the K-means clustering algorithm.

 

QUESTION 11

List three popular use cases of the Association Rules mining algorithms.

 

QUESTION 12

Define Support and Confidence

 

QUESTION 13

How do you use a “hold-out” dataset to evaluate the effectiveness of the rules generated?

 

QUESTION 14

List two use cases of linear regression models.

 

QUESTION 15

Compare and contrast linear and logistic regression methods.

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

Computer Science homework help

/in , /by

Computer Science homework help

COM520 Written Assignment 4

Assignment: Security Audit Procedure Guide

Assignment Requirements

This assignment requires you to prepare a procedure guide for a security audit in Ken 7 Windows Limited. Ken 7 Windows Limited has acquired several new servers and workstations to support the new enterprise resource planning (ERP) software. You want to ensure the new computers comply with Microsoft’s initial secure baseline. You choose to use the Microsoft baseline security analyzer (MBSA) tool to assess the basic security for all of your Windows computers. MBSA will identify many of the basic vulnerabilities found in Windows environments. MBSA’s vulnerability report provides a good starting point for securing new and existing Windows computers. You need to develop procedures to ensure that each computer in your environment has no reported vulnerabilities. Using the format below, describe the steps to follow to scan multiple computers for security vulnerabilities using MBSA. Include steps to research and address any reported vulnerabilities. Assume you plan to run MBSA on a new server that does not have MBSA installed. Fill in the details for each procedural step to audit each computer and address discovered vulnerabilities.

1) Acquire and install MBSA.

2) Scan computers.

3) Review scan results.

4) Identify vulnerabilities you need to address.

5) Document the steps to address each vulnerability.

 

Submission Requirements

 Format: Microsoft Word

 Font: Arial, Size 12, Double-Space

 Citation Style: APA Style

 Length: 1–2 pages

 

Self-Assessment Checklist

 I have properly organized the steps necessary for malware-free environment.

 I have given a proper justification of the following steps in the security audit procedure guide:

o Download and install MBSA.

o Use MBSA to scan multiple computers.

o Review the scan results.

o Identify vulnerabilities to mitigate.

o Document the steps necessary to mitigate selected vulnerabilities.

 

 

 

COM520 Written Assignment 4

Case Scenario:

Ken 7 Windows Limited is a manufacturer of Windows for residential and commercial builders. Ken 7 Windows Limited carries a variety of Windows and related products. It supplies builders with all of the tools and supplies to install finished Windows in any type of building. Ken 7 Windows Limited has just purchased a new enterprise resource planning (ERP) software package to help control costs and increase both quality and customer responsiveness. The ERP software collects and stores information including:

• Raw material costs

• Labor costs

• Materials and labor requirements for products

• Purchasing requirements

Ken 7 Windows Limited has identified six basic roles for users in the new ERP software:

• Administrators—maintain ERP data and system operation.

• Planners—run planning software and generate requirements reports.

• Shop Floor users —enter operational data (receiving, shipping, and product progress during manufacturing).

• Managers—manage department personnel.

• Purchasing users—generate purchasing documents based on planning requirements.

• Accounting users—maintain cost and accounting data.

Access controls limit what users or roles can do with different types of data. For example, consider the following types of data:

• Cost information—raw materials and labor costs, including the cost of finished goods.

• Manufacturing details—cost, amount of labor, and time required to produce finished goods.

• Purchasing requirements—rules for determining when raw materials, components, or supplies should be purchased.

Through access control:

• Cost information can be viewed only by Accounting users.

• Manufacturing details can be viewed only by Shop Floor users.

• Purchasing requirement can be viewed only by Purchasing users.

During the analysis phase of the ERP implementation, Ken 7 Windows Limited raised concerns about users being able to access restricted data.

• Accounting users are able to login to shop floor computers.

• Purchasing users are able to access human resource (HR) applications and data.

The ERP implementation team suggested the following access control measures to protect restricted data.

• Create an organizational unit (OU) in Active Directory for shop floor computers.

• Deploy Group Policy Objects (GPOs) to restrict shop floor users to the shop floor OU.

• Define data access controls in the ERP software to deny access for all non-HR users to restricted data.

 

 

COM520 Written Assignment 4

Implementation of several access control measures helped Ken 7 Windows Limited to restrict the data access. Hence access control and authentication is important, as it helped Ken 7 Windows Limited in reducing costs and increasing profits.

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

Computer Science homework help

/in , /by

Computer Science homework help

CSci 430 Spring 2019

Objectives:

ˆ Explore the Process state models from an implementation point of

view.

ˆ Practice using basic queue data types and implementing in C.

ˆ Use C/C++ data structures to implement a process control block and

round robin scheduling queues.

ˆ Learn about Process switching and multiprogramming concepts.

Description:

In this assignment you will simulate a Three-State process model (ready,

running and blocked) and a simple process control block structure as dis-

cussed in Chapter 3. Your program will read input and directives from a

le. The input describes a time sequence of events that occur. These are the

full set of events you will simulate:

1

Event Description

cpu The processor executes for 1 time step the currently running process

new A new process is created and put at tail of the ready queue

done The currently running process has nished

wait X The currently running process has done an I/O operation and

is waiting on event X

event X Event X has occurred, the process waiting on that event should

be made ready.

The input le will simply be a list of events that occur in the system, in

the order they are to occur. For example:

—– simulation-01.sim ——–

new

cpu

cpu

cpu

new

cpu

cpu

cpu

cpu

wait 1

cpu

cpu

event 1

cpu

cpu

done

cpu

cpu

cpu

cpu

exit

———————————-

Your task is to read in the events, and simulate the creation and execution

of processes in the system as they move through the various three-states of

their process life cycle. You need to:

2

ˆ Dene a simple process control block (PCB) to hold information about

all processes currently running in your system. The PCB can be a

simple C struct or a C++ class. At a minimum you need to have a

eld for the process identier and the process state (Ready, Running or

Blocked). You need to also keep track of the time step that the process

entered the system, and the number of steps the process has been

running. Minimal credit will be given to programs that at least handle

new events and create a process in a simulated PCB. You probably

need a list or an array to hold the current processes that have been

created and are being managed by your simulated system.

ˆ You will need a ready queue of some kind. You should use a C++

Standard Template Library (STL) container to manage your ready

queue.

ˆ You will need to implement a simple dispatcher function. Whenever

a cpu event occurs, and no process is currently running, you should

select the next Ready process from the head of your ready queue and

start it running on the processor.

ˆ You need to also implement a simple time slicing mechanism. The

time slice value to use will be passed into your program when it is

started. At the end of a cpu cycle, you should check if the currently

running process has executed for its full time quantum. In that case,

the currently running process should timeout, and be returned to the

end of the Ready queue.

ˆ new events should cause a new process to be created (including creating

its PCB and lling it in). New processes should be placed on the tail

of the ready queue after being created. You should assign each new

process a process identier. The process identier should be a simple

integer value, and you should start numbering processes from 1.

ˆ For a done event, if a process is currently running it should then be

released. It should be removed from the CPU, and not placed back on

the ready or blocked queue. If a done occurs when the CPU is idle,

then nothing will happen as a result of this event.

ˆ A wait event simulates the currently running process performing some

I/O operation. If a wait occurs, the currently running process should

become blocked and put on the blocked queue. You also need an entry

in the PCB so you know what event the process is waiting for. The

3

wait event is followed by an integer number, which is an indication of

the type of event the process has requested.

ˆ Likewise the event directive simulates the nishing of some I/O oper-

ation. When an event occurs, you should scan your blocked processes

and make any process ready that was waiting on that event. The in-

teger value following an event indicates the type of event that just

occurred.

You have been given some example event sequences (simulation-01.sim,

simulation-02.sim, etc.) along with the expected output for those sequence

of events (simulation-01.res, simulation-02.res, etc.). The output of your

program should be sent to standard output. The correct output for the

simulation-01.sim simulation is:

Time: 1

CPU (currently running):

pid=1, state=RUNNING, start=1, slice=1,

Ready Queue:

EMPTY

Blocked Queue:

EMPTY

Time: 2

CPU (currently running):

pid=1, state=RUNNING, start=1, slice=2,

Ready Queue:

EMPTY

Blocked Queue:

EMPTY

Time: 3

CPU (currently running):

pid=1, state=RUNNING, start=1, slice=3,

Ready Queue:

EMPTY

Blocked Queue:

EMPTY

Time: 4

CPU (currently running):

4

pid=1, state=RUNNING, start=1, slice=4,

Ready Queue:

pid=2, state=READY, start=4, slice=0,

Blocked Queue:

EMPTY

Time: 5

CPU (currently running):

pid=1, state=RUNNING, start=1, slice=5,

Ready Queue:

pid=2, state=READY, start=4, slice=0,

Blocked Queue:

EMPTY

Time: 6

CPU (currently running):

pid=2, state=RUNNING, start=4, slice=1,

Ready Queue:

pid=1, state=READY, start=1, slice=5,

Blocked Queue:

EMPTY

Time: 7

CPU (currently running):

pid=2, state=RUNNING, start=4, slice=2,

Ready Queue:

pid=1, state=READY, start=1, slice=5,

Blocked Queue:

EMPTY

Time: 8

CPU (currently running):

pid=1, state=RUNNING, start=1, slice=1,

Ready Queue:

EMPTY

Blocked Queue:

pid=2, state=BLOCKED, start=4, slice=2, event=1

Time: 9

CPU (currently running):

5

pid=1, state=RUNNING, start=1, slice=2,

Ready Queue:

EMPTY

Blocked Queue:

pid=2, state=BLOCKED, start=4, slice=2, event=1

Time: 10

CPU (currently running):

pid=1, state=RUNNING, start=1, slice=3,

Ready Queue:

pid=2, state=READY, start=4, slice=2,

Blocked Queue:

EMPTY

Time: 11

CPU (currently running):

pid=1, state=RUNNING, start=1, slice=4,

Ready Queue:

pid=2, state=READY, start=4, slice=2,

Blocked Queue:

EMPTY

Time: 12

CPU (currently running):

pid=2, state=RUNNING, start=4, slice=1,

Ready Queue:

EMPTY

Blocked Queue:

EMPTY

Time: 13

CPU (currently running):

pid=2, state=RUNNING, start=4, slice=2,

Ready Queue:

EMPTY

Blocked Queue:

EMPTY

Time: 14

CPU (currently running):

6

pid=2, state=RUNNING, start=4, slice=3,

Ready Queue:

EMPTY

Blocked Queue:

EMPTY

Time: 15

CPU (currently running):

pid=2, state=RUNNING, start=4, slice=4,

Ready Queue:

EMPTY

Blocked Queue:

EMPTY

Your output to standard out should look exactly the same as this output

(i.e. if I do a di and your program is generating the correct output, then

there will be no dierence between the output your program generates and

the above output format). The output is generated by displaying the system

state after each cpu cycle executes. Basically we print out the system time.

Then we show which process (if any) is currently running on the CPU (or

say it is IDLE if no process is running). Then we display the queue of

processes currently on the Ready and Blocked queues. Note that the queues

are displayed in order. The top of the output corresponds to the head of the

queue. Thus when a new process is dispatched, the next one selected should

be the rst process listed from the ready queue in the previous system cycle.

I have given you some template code (p2-start.cpp) to get you started

The code is meant to be run from the command line, thus from a shell or

dos prompt you would do something like:

$ p2-start simulation-01.sim 5

i.e. the program expects two parameters on the command line, which

should be the name of a le that holds the events to be simulated, and the

value to be used for the time slice quantum. If you need to test your program

and can’t gure out how to invoke running it from the command line, you

can change the line in ‘p2-start.cpp’ to explicitly run a particular simulation

le, like this:

runSimulation(“simulation-01.sim”, time_slice_quantum)

7

However, you need to make sure that your program correctly works using

the command line invocation, as shown in `p2-start.cpp`.

I have given some template code to get you started in the le called

p2-start.cpp. I have already provided you with the code needed in order to

correctly parse the command line parameters for the program, and to open

and read in the simulation le events. Your job is to implement the necessary

actions and data structures to handle the simulated events described. The

runSimulation() in ‘p2-start.cpp holds example code and indicates locations

where you need to write your own functions to implement the simulation.

You can use this as a starting point to implement your solution.

Assignment Submission and Requirements

All source les you create for you solution (.c or .cpp/.c++ and .h header

les) should be uploaded to the MyLeo Online submission folder created for

this assignment by the deadline. You should not attach any les besides the

source les containing your C/C++ code. But you should make sure you

attach all needed les you create to your submission, so that I can compile

and run your solution.

You are required to write the program in standard C/C++ programming

language. You should use a relatively recent version of the C/C++ compiler

(C90 C++98 is ne, or the more recent C99 C++11 will also be acceptable),

and/or recent IDE that has an up to date compiler. You should only use

standard C/C++ libraries, do not use Microsoft specic or other third-party

developed external libraries. This page http://en.cppreference.com/w/

provides a good up to date reference of the libraries in the standard C++ and

C languages. You may use the C++ standard template library containers

(like the list and queue items) to implement the ready queue you need. We

will go over a simple implementation of a queue using pointers and/or arrays

in class, if you would like an example implementation in plain C that might

be simpler to use than learning the STL.

8

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

Solved Exp19_Excel_Ch07_HOEAssessment_Employees Assignment Help

/in , /by

Solved Exp19_Excel_Ch07_HOEAssessment_Employees Assignment Help

Exp19_Excel_Ch07_HOEAssessment_Employees

Project Description:

You work for a clothing distributor that has locations in Iowa, Minnesota, and Wisconsin. You will use date and logical functions to complete the main employee data set, use database functions to calculate key summary statistics and create a map, and use financial functions to complete a loan amortization table.

 

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

 

The 1-Data worksheet contains   employee data. You will insert several functions to complete this worksheet.   Column C contains the actual hire dates for the employees. You want to   extract only the year in column G.
In cell G9, insert the appropriate date function to extract the year from the   date in cell C9. Copy the function from cell G9 to the range G10:G33.

 

Next, you want to determine how   many years each employee has worked for the company.
In cell H9, insert the YEARFRAC function to calculate the years between the   hire date and the last day of the year contained in cell G2. Use a mixed   reference to cell G2. Copy the function from cell H9 to the range H10:H33.

 

You want to determine what day   of the week each employee was hired.
In cell I9, insert the WEEKDAY function to display the day of the week the   first employee was hired. Use 2 as the return_type. Copy the function from   cell I9 to the range I10:I33.

 

The value returned in cell I9 is   a whole number. You want to display the weekday equivalent.
In cell J9, insert a VLOOKUP function to look up the value stored in cell I9,   compare it to the array in the range H2:I6, and return the day of the week.   Use mixed references to the table array. Copy the function from cell J9 to   the range J10:J33.

 

Column D contains the city each   employee works in. You want to display the state.
In cell F9, insert the SWITCH function to switch the city stored in cell D9   with the respective state contained in the range C2:C4. Switch Des Moines for Iowa, St. Paul for Minnesota, and Milwaukee for Wisconsin. Use mixed   references to cells C2, C3, and C4. Copy the function from cell F9 to the   range F10:F33.

 

Your next task is to calculate   the bonus for the first employee. If the employee was hired before 1/1/2010,   the employee’s salary is multiplied by 3%. If the employee was hired before   1/1/2015, the employee’s salary is multiplied by 2%. If the employee was   hired before 1/1/2020, the employee’s salary is multiplied by 1%.
In cell K9, insert the IFS function to create the three logical tests to   calculate the appropriate bonus. Use mixed references to cells within the   range K2:L4. Copy the function from cell K9 to the range K10:K33.

 

Top management decided to ensure   all representatives’ salaries are at least $62,000 (cell G2).
In cell L9, nest an AND function within an IF function. If the job title is Representative   and the salary   is less than the minimum representative salary, calculate the difference   between the minimum representative salary and the actual salary. If not,   return zero. Use a mixed reference to cell G3. Copy the function from cell L9   to the range L10:L33.

 

The 2-Summary worksheet contains   data to insert conditional math and statistical functions to provide summary   data. First, you want to count the number of employees in each state.
Click the 2-Summary worksheet. In cell J3, insert the COUNTIF function to   count the number of employees in Iowa, using the state abbreviation column   and the state abbreviation in cell I3. Use mixed references for the range and   cell to keep the row numbers the same. Copy the function from cell J3 to the   range J4:J5.

 

Next, you want to calculate the   total payroll for each state.
In cell K3, insert the SUMIF function to total the salaries for employees who   work in Iowa, using the state abbreviation column and the state abbreviation   in cell I3. Use mixed references for the ranges and cell to keep the row   numbers the same. Copy the function from cell K3 to the range K4:K5.

 

Next, you want to calculate the   average salary for executives and the average salary for representatives.
In cell J8, insert the AVERAGEIF function to calculate the average salary for   executives. Use mixed references to the range and for the cell containing   Executives. Copy the function to cell J9.

 

You want to identify the number   of representatives hired before 1/1/2015.
In cell J12, insert the COUNTIFS function to count the number of   representatives who were hired before 1/1/2015. Use ranges for the criteria   ranges and the reference to cell H9 that contains Representatives. Enter the   date condition directly in the criteria box.

 

In cell J13, insert the SUMIFS   function to calculate the total salaries for representatives hired before   1/1/2015. Use ranges for the criteria ranges and the reference to cell H9   that contains Representatives. Enter the date condition directly in the   criteria box. Use mixed references to the ranges and cell reference so that   you can copy the function in the next step.

 

Copy the function from cell J13   to the range J14:J16. Edit the function in cell J14 to calculate averages.

 

Edit the function in cell J15 to   calculate the lowest salary.

 

Edit the function in cell J16 to   calculate the highest salary.

 

Insert a map to display total   salaries by states.
Select the ranges I2:I5 and K2:K5 and insert a filled map. Change the chart   title to Payroll by State. Cut the chart and paste it in cell M1.

 

The 3-Finance worksheet contains   inputs for a loan.
In cell F2, insert the PV function to calculate the loan. Use cell references   in the Input Area and Basic Output Area.

 

You are ready to insert formulas   and functions in the loan amortization table.
In cell C7, enter a reference to the Payment that is stored in cell D2. Use   mixed reference so that the row number does not change. Copy the formula from   cell C7 to the range C8:C42.

 

The next column is designed to   display the interest portion of each payment.
In cell D7, insert the IPMT function to calculate the interest for the first   payment. Use mixed references for cells in the Basic Output Area and make   sure the result is a positive value. Copy the function from cell D7 to the   range D8:D42.

 

Next, you are ready to calculate   the portion of the monthly payment that goes toward the principal.
In cell E7, insert the PPMT function to calculate the principal portion of   the first payment. Use mixed references for cells in the Basic Output Area   and make sure the result is a positive value. Copy the function from cell E7   to the range E8:E42.

 

Now you are ready to calculate   the balance of the loan after each payment.
In cell F7, enter a formula to calculate the ending balance after subtracting   the Principal Repayment from the Beginning Balance. Copy the formula from   cell F7 to the range F8:F42.
In cell B8, enter a formula that refers to the ending balance for the   previous month in cell F7. Copy the relative formula to the range B9:B42.

 

Now you want to calculate the   cumulative interest after each payment.
In cell H7, insert the CUMIPMT function. Use mixed references for cells in   the Basic Output Area and the Start_period argument. Make sure the result is   a positive value. Copy the function from cell H7 to the range H8:H42.

 

Finally, you want to calculate   the cumulative principal after each payment.
In cell I7, insert the CUMPRINC function. Use mixed references for cells in   the Basic Output Area and the Start_period argument. Make sure the result is   a positive value. Copy the function from cell I7 to the range I8:I42.

 

Insert a footer on all three   sheets with your name on the left side, the sheet name code in the middle,   and the file name code on the right side.

 

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

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

Exp19_Excel_Ch06_Cap_High_West_Fashions

/in , /by

Exp19_Excel_Ch06_Cap_High_West_Fashions

Grader – Instructions Excel 2019 Project

Exp19_Excel_Ch06_Cap_High_West_Fashions

 

Project Description:

You are the digital marketing director for High West Fashions, a regional clothing company that specializes in custom t-shirts. Your company has decided to launch an online advertising campaign that gives customers the ability to purchase heavily discounted products. You have the task of determining the optimal amount of advertising to purchase in order to maximize profit and most effectively utilize resources.

 

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp19_Excel_Ch06_Cap_DirectMarketing.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 On the Direct Marketing worksheet, create appropriate range names for Design_Fee (cell B8), Cost_Per_Ad (cell B9), Total_Clicks (cell B10), Profit_Per_Click (B11), and Gross_Profit (cell B12). 5
3 Edit the existing name range Design_Fee to Design_Fee2021 to reflect the current year. 4
4 Use the newly created range names to create a formula to calculate Gross Profit (cell B12) and Net Profit (cell B13). 6
5 Create a new worksheet named Range Names, paste the newly created range name information in cell A1, and resize the columns as needed for proper display. Mac users, use the Insert menu to insert a new worksheet and paste the range names. 5
6 On the Direct Marketing worksheet, use Goal Seek to determine the optimal click rate in order to earn a $5,000 net profit. 6
7 Starting in cell E4. Complete the series of substitution values ranging from 2% to 6.5% at increments of .50% vertically down column E. 5
8 Enter references to the Gross Profit and Net Profit in the correct location for a one-variable data table. 3
9 Complete the one-variable data table, and then format the results with Accounting Number Format with two decimal places. 6
10 Apply custom number formats to display Gross Profit in cell F3 and Net Profit in cell G3. 4
11 Copy the response rate substitution values from the one-variable data table, and then paste the values starting in cell I4. 4
12 Type 10000 in cell J3. Complete the series of substitution values from 10000 to 40000 at 5000 increments. 3
13 Enter the reference to net profit formula in the correct location for a two-variable data table. 4
14 Complete the two-variable data table and format the results with Accounting Number Format with two decimal places. 7
15 Apply a custom number format to make the formula reference appear as Net Profit. 3
16 Make the Direct Marketing 2 worksheet active. Create a scenario named Best Case, using Number of Ads and Click Rate. Enter these values for the scenario: 40000, and 6.5%. 4
17 Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 10000, and 1%. 4
18 Create a third scenario named Most Likely, using the same changing cells. Enter these values for the scenario: 10000, and 6.83%. 4
19 Generate a scenario summary report using Gross Profit and Net Income. 4
20 Return to the Direct Marketing 2 worksheet. Load the Solver add-in if it is not already loaded. Launch Solver and set the objective to calculate a net profit of $20,000. 4
21 Use Number of Ads and Click Rate (B4:B5) as changing variable cells. 4
22 Set a constraint to ensure Number of Ads purchased is less than or equal to 40,000. 2
23 Set a constraint to ensure Click Rate is less than or equal to 7%. (Mac users should enter the value in decimal form. Example .07) 2
24 Solve the problem. Generate the Answer Report. 3
25 Create a footer on all worksheets with your name on the left side, the sheet name code in the center, and the file name code on the right side. 4
26 Save and close Exp19_Excel_Ch06_CAP_DirectMarketing.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

 

Created On: 04/09/2021 1 Exp19_Excel_Ch06_Cap – High West Fashions 1.6

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

Exp19_Excel_Ch06_ML1_Housing_Cost

/in , /by

Exp19_Excel_Ch06_ML1_Housing_Cost

Exp19_Excel_Ch06_ML1_Housing_Cost

 

Exp19_Excel_Ch06_ML1_Housing_Cost

Project Description:

Your friends, Elijah and Valerie Foglesong, want to build their dream house. They identified tentative costs, but they cannot afford the $414,717 estimated cost. You will use Goal Seek to determine an estimate of the total finished square footage they can afford. To help provide more flexibility in their decision-making, you will create a data table listing various finished square footages and their effects on the base house cost and total cost. Finally, you will create another data table showing combinations of square footages and lot prices to identify total costs.

 

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

 

Assign the range names B9 (Base), B15 (Interior), B21 (Exterior), B23 (Lot).

 

Enter a formula in cell B25 to   calculate the total estimated cost. The total estimated cost is the sum of   the base house price (B9), total interior upgrades (B15), total exterior   upgrades (B21), and lot fee (B23). Be sure to use range names in the formula.

 

Use Goal Seek to determine the   total finished square footage(B7) to meet the total cost goal of $350,000.

 

Enter a series of total square   footage ranging from 1,800 to 3,600 in increments of 200 in the range D6:D15. Apply Blue font and Comma Style with   zero decimal places to the series.

 

Enter a reference to the base   house price in cell E5 and a reference to the total estimated cost in cell   F5.

 

Complete the single variable   data table in the range D5:F15. Be sure to use total square footage as the   column input. Apply Custom number formats to give appropriate descriptions to   the second and third columns. Apply these formats to the headings: bold,   center, and Blue font color.

 

Identify the square footage,   base price, and total cost in the data table that comes closest to their   goal. Apply Yellow fill color to those cells.

 

Copy the square footage   substitution values, range D6:D15 to the range H6:H15 and remove the fill   color. Enter these lot price substitution values in the range I5:K5: 90000, 96000, and 102675. Format these values with Accounting Number Format with zero   decimal places and Blue font color.

 

Enter the reference to the total   estimated cost in the appropriate location for the second data table. Apply a   custom number format to display Total Sq Ft in that cell. Apply Blue font and bold to that   cell.

 

Complete the two-variable data   table using total square footage as the Column Input and lot price as the Row   Input. Apply a Custom number format to the reference to the formula cell.   Apply Yellow fill color to the total price in each column that comes closest   to their goal.

 

Format results in both tables   with Accounting Number Format with zero decimal places.

 

Create a scenario named Ideal Costs, using Base price per square   foot and Lot fee (use cell references). Enter these values for the scenario: 80, 102,675.

 

Create a scenario named Increased   Costs, using   Base price per square foot and Lot fee (use cell references). Enter these   values for the scenario: 100, 110,000.

 

Create a scenario named Low Costs, using Base price per square   foot and Lot fee (use cell references). Enter these values for the scenario: 60, 80,000.

 

Generate a scenario summary   report using the cell references for Price per square foot and Lot Fee. Add   the appropriate formatting to the newly created report

 

Save and close Exp19_Excel_Ch06_ML1_HW-Housing Cost.xlsx.   Exit Excel. Submit the file as directed.

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

Excel_Introductory_Capstone2_Annual_Report

/in /by

Excel_Introductory_Capstone2_Annual_Report

Grader – Instructions Excel 2019 Project

Excel_Introductory_Capstone2_Annual_Report

 

Project Description:

In this project, you will work with multiple worksheets and enter formulas and functions to calculate totals, averages, maximum values, and minimum values. Additionally, you will create a summary sheet, format cells, insert charts, insert sparklines, and create a table in a workbook.

 

Steps to Perform:

Step Instructions Points Possible
1 Open the Excel file named Student_Excel_Intro_Cap2_Annual_Report.xlsx downloaded with this project. 0
2 On the Net Sales worksheet, calculate totals in the ranges F4:F8 and B9:F9. Apply the Total cell style to the range B9:F9. 5
3 Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Texas Total is of Total Sales, and then apply Percent Style. Fill the formula down through the range G5:G8. 5
4 In the range H4:H8, insert Line sparklines to represent the trend of each state across the four quarters. Do not include the totals. Add Markers and then in the first row, apply the first Sparkline style. 4
5 Select the range A3:E8, and then use the Recommended Charts command to suggest an appropriate chart. Click the first Clustered Column chart that uses the state names as the category axis. Align the upper left corner of the chart inside the upper left corner of cell A11, and then size the chart so that its lower right corner is slightly inside cell H24. Apply chart Style 6. As the chart title, type Quarterly Net Sales by State 10
6 To show the percent that each state contributes to the total sales, select the nonadjacent ranges that represent the state names and state totals (in column F). Insert a 3-D Pie chart, and then move the chart to a New sheet. Name the sheet Net Sales Chart. 6
7 Change the Chart Title to Annual Net Sales by State and then change the chart title Font Size to 32. Remove the Legend from the chart, and then add Data Labels that display only the Category Name and Percentage positioned in the Center. Change the data labels Font Size to 14 and apply Bold and Italic. Change the Font color to White, Background 1. 6
8 Select the entire pie and display the Format Data Series pane. From the 3-D Format gallery, modify the 3-D options by changing the Top bevel and Bottom bevel to the first bevel option in the first row. Set all of the Width and Height boxes to 256 and then change the Material to the fourth Standard type—Metal. 2
9 Insert a Custom Footer with the File name in the left section and then save your workbook. 3
10 On the Portland Inventory worksheet, in cell B5, enter a function that will display the average retail price. In cell B6, enter a function that will display the median retail price. In cell B7, enter a function that will calculate the lowest retail price. In cell B8, enter a function that will calculate the highest retail price. Format the range B5:B8 with Accounting Number Format. 5
11 On the Portland Inventory worksheet, in cell G14, enter an IF function to determine the items to be ordered. If the Quantity in Stock is less than 50, then the cell should display Order. If not, then the cell should display OK. Copy the function down through cell G19. 8
12 Format the range A13:G19 as a table with headers and then apply Teal, Table Style Light 13. If the style is not available, choose another style. Filter the table on the Sport column to display only the Hiking types. Display a Total Row in the table, and then sum the Quantity in Stock for Hiking items. Type the result in cell B11. Remove the total row from the table and then clear the Sport filter. 4
13 In cell B10, insert a COUNTIF function to count the number of Hiking items in the Sport column. 1
14 Add Gradient Fill Blue Data Bars to the range A14:A19. Add conditional formatting to the range G14:G19 so that the cells with text that contain the word Order are formatted with Bold and Italic. Sort the table by Item # from Smallest to Largest. 3
15 Display the Inventory Summary sheet. In cell B4, enter a formula that references cell B4 in the Portland Inventory sheet so that the Portland Total Items in Stock displays in cell B4. In cell B5, enter a formula that references cell B5 in the Portland Inventory sheet so that the Portland Average Price displays in cell B5. In cells B6, B7, and B8, enter similar formulas to reference the Median Price, Lowest Price, and Highest price in the Portland Inventory sheet. 5
16 In cell C4, enter a formula that references cell B4 in the Austin Inventory sheet so that the Austin Total Items in Stock displays in cell C4. In cells C5, C6, C7, and C8, enter similar formulas to reference the Average Price, Median Price, Lowest Price, and Highest price in the Austin Inventory sheet. Be sure that the range B5:C8 is formatted with Accounting Number Format. 5
17 On the Annual Expenses sheet, construct formulas to calculate Totals by Quarter in the range B10:E10 and the Annual Totals in the range F5:F10. Apply the Total cell style to the Totals by Quarter (B10:F10), and then center the column headings (B4:G4) and apply the Heading 4 cell style. If necessary, format the range F6:F9 with Comma Style, zero decimal places. 5
18 Using absolute cell references as necessary, in cell G5, construct a formula to calculate the % of Total by dividing the Sales Expense Annual Total by the Annual Totals by Quarter. Fill the formula down through the range G6:G9. 2
19 Using the data in the nonadjacent ranges A4:E4 and A10:E10, insert a Line with Markers chart. Position the upper left corner of the chart slightly inside the upper left corner of cell A12. Change the height of the chart to 1.75 inches and the width of the chart to 8 inches. (Mac users, change the chart height to 1.5.) 6
20 Apply chart Style 3. Change the chart title to Direct Expenses. Edit the Vertical (Value) Axis so that the Minimum is 6000000. 4
21 Use Format Painter to copy the formatting from cell A2 to A20. In cell B23, enter a formula that references the value in cell F10. 2
22 Using absolute cell references as necessary, in cell C23, construct a formula to calculate the projected expenses for 2021 after the Forecasted increase in cell B21 is applied. Fill the formula through cell F23. 4
23 Display the Sales Reps sheet. By using Flash Fill and deleting columns as necessary, place the last names in column A and the first names in column B. Widen both columns to 100 pixels, and then merge and center the title Sales Reps across the two columns. Apply the Heading 1 cell style to the title. 4
24 With any sheet except the chart sheet active, group the worksheets. Change the Orientation to Landscape, center the worksheets Horizontally, and insert a footer in the left section with the file name. (Mac users, click the Net Sales sheet, hold down SHIFT, and then click the Sales Reps sheet. Then apply the Page Layout options.) 1
25 Save and close the file, and then submit for grading. 0
Total Points 100

 

Created On: 06/29/2021 1 GO19_XL_INTRO_GRADER_CAP2 – Annual Report 1.4

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

Microsoft Excel Assignment – “Revenue”

/in , /by

Microsoft Excel Assignment – “Revenue”

Do Project 3E Revenue that begins on page 527 of your textbook. Submit the document by clicking the Attachments button to attach the file. Do not paste the document in the Message area.

You will need to download and use the following data file to complete your assignment:

Revenue Report

In addition to the assignment file, answer the following question with your submission.  This can be answered directly in your Submission comment area, or as a separate Word attachment file:

In this Week’s reading and prep work, In this week’s chapter, you utilized the “What-If Analysis” feature. Explain what this is and how you feel you might be able to use it in a personal finance worksheet of your own?  Please look to answer this in at least 3 complete sentences in order to address it adequately.

The picture on the right is what it should look like afterwards!!!!  

 

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

Exp19_Excel_Ch05_Cap_Apartments

/in , /by

Exp19_Excel_Ch05_Cap_Apartments

#Exp19ExcelCh05CapApartments 

#Exp19_Excel_Ch05_Cap_Apartments

Project Description:

You manage several apartment complexes in Phoenix, Arizona. You  created a dataset that lists details for each apartment complex, such as  apartment number, including number of bedrooms, whether the unit is  rented or vacant, the last remodel date, rent, and deposits. You will  use the datasets to aggregate data to analyze the apartments at the  complexes.

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

Before subtotalling the data,   you need to sort the data.
Select the Summary sheet. Sort the data by Apartment Complex in  alphabetical   order and further sort it by # Bed (the number of  bedrooms) from smallest to   largest.

You want to use the Subtotal   feature to display the average total deposit by number of bedrooms for each   apartment complex.
Use the Subtotal feature to insert subtotal rows by Apartment  Complex to   calculate the average Total Deposit. Add a second subtotal  (without removing   the first subtotal) by # Bed to calculate the  average Total Deposit by the   number of bedrooms.

Use the outline symbols to   display only the subtotal rows. Create  an automatic outline and collapse the   outline above Total Deposit.

You want to create a PivotTable to determine the total monthly rental   revenue for occupied apartments.
Display the Rentals sheet and create a blank PivotTable on a new  worksheet to   the left of the Rentals sheet. Change the name of the  worksheet to Rental Revenue. Name   the PivotTable Rental Revenue.

Display the Apartment Complex and # Bed fields in Rows and the Rental   Price field as Values.

Format the Sum of Rental Price   for Accounting Number Format with zero decimal places and enter the custom   name Total   Rent Collected.

Select the Occupied field for   the filter and set the filter to Yes to display data for occupied apartments.

You want to calculate the total   monthly rental revenue if the rates increase by 5% for the occupied   apartments.
Insert a calculated field to multiply the Rental Price by 1.05. Change the name to New Rental   Revenue. Apply   Accounting Number Format with zero decimal places.

Select the range B3:C3 and apply   these formats: wrap text, Align Right horizontal alignment, and 30 row height. Select column B and   set 9.29 column width. Select column C   and set 14.43 column   width.

Apply Light Orange, Pivot Style   Medium 10 to the PivotTable and display banded rows.

Insert a slicer for # Bed so that you can filter the dataset by    number of bedrooms. Change the slicer caption to # of Bedrooms.

Change the slicer height to 1.4 inches and width to 1.75 inches. Apply Light Orange,   Slicer Style Light 2. Cut the slicer and paste it in cell E2.

Insert a timeline for the Last   Remodel field. Change the time  period to YEARS. Apply Light Orange, Timeline   Style Light 2. Change  the timeline height to 1.4 inches and with to 3.75 inches.

The Databases sheet contains two   tables. You will create a relationship between those tables.
Display the Databases sheet. Create a relationship between the  APARTMENTS   table using the Code field and the COMPLEX table using the  Code field.

You want to create a PivotTable   from the related tables.
Create a PivotTable using the data model on a new sheet. Change the sheet   name to Bedrooms.   Name the PivotTable BedroomData.

Select the Apartment Name field   from the COMPLEX table for Rows,  the # Bed field for Columns, and the # Bed   field as Values. This will  display the number of apartments with the   specified number of bedrooms  per apartment complex. Display the values as a percentage   of row  totals.

Create a Clustered Column   PivotChart. Cut the chart and paste it in cell A13 using the Destination   Theme.

Select the 3-bedroom data series   and apply the Black, Text 1,  Lighter 50% solid fill color. Apply Black, Text   1 font color to the  vertical axis and category axis. Change the chart height   to 3 inches  and the   width to 5 inches, if necessary. Hide the field buttons in the  PivotChart.

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

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

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

Excel_2E_Plants homework help

/in , /by

Excel_2E_Plants

Excel_2E_Plants

 

Open   the Excel workbook Student_Excel_2E_Plants.xlsx   downloded with this project.

 

To the right of column B, insert   two new columns to create new blank columns C and D. By using Flash Fill in   the two new columns, split the data in column B into a column for Item # in   column C and Category in column D. As necessary, type Item # as the column title in column C   and Category   as the column   title in column D. Delete column B.

 

By using the Cut and Paste   commands, cut column C—Category—and   paste it to column H, and then delete the empty column C. Apply AutoFit to   columns A:G.

 

In cell B4, insert a function to   calculate the Total Items in Stock by summing the Quantity in Stock data, and   then apply Comma style with zero decimal places to the result.

 

In each cell in the range B5:B8,   insert functions to calculate the Average, Median, Lowest, and Highest retail   prices, and then apply the Accounting Number Format to each result.

 

Move the range A4:B8 to the   range D4:E8, apply the 40% – Accent4 cell style to the range, and then select   columns D:E and AutoFit.

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