# e00_cumexam (solution)

Mark’s Construction and Realty

Mark’s Construction and Realty buys and flips houses. They have hired you to look at their sales numbers from last year to analyze the data. They have instructed you to see if there are any trends in the types of houses they typically purchase, if there is a correlation between the number of houses sold and sale date, and which sales should be included in the project managers’ year-end bonus.

Perform Preliminary Work

You need to open the existing workbook they provided, save it with a new name, and make a few preliminary modifications.

a. Open the e00_cumexam_data workbook and save the workbook as e00_cumexam_LastFirst.

b. Change the width of column E to 15.00.

c. Insert a new row above row 1.

d. Enter the text 2017 Sales Data in cell A1, change the font size to 14 and apply bold.

e. Add a fill color of Blue, Accent 1 for cell A1.

f. Merge and center the range A1:I1.

g. Freeze the headers so when you scroll down the headers are always visible.

Calculate Profit and Determine if Project is Eligible for Bonus

You are ready to calculate the total costs.

h. Insert two columns after Sale Price named Total Cost and Profit respectively.

i. Insert a function in cell H3 to calculated the total cost of each project.

j. Insert a function in cell I3 to calculated the profit of each project.

k. Create an IF statement in cell K3 where if the profit is greater than \$50,000 it will display the text Yes, otherwise it will display the text No.

l. Copy the three formulas down their respective columns.

Organize Monthly Data

The company has already collected data about how many of each type of house they sold each month. You will convert the data into a table, apply a table style to make the data more visually appealing, and add total rows for use in the analysis grids later.

m. Click the Sheet2 worksheet and rename it Analysis.

n. Convert the entire dataset into a new table named Date with a table style of Table Style Medium 6.

o. Calculate a total for each house type in column N.

House Type Analysis

You will analyze each house type to determine if the company has a tendency to purchase a certain type of a house.

p. Create a 2D-Pie chart that displays the total number of houses by house type and apply chart type Style 7.

q. Move the chart to the left side of the screen directly under the table data.

r. Change the chart title to House Type.

s. Move the legend to the top-left corner.

t. Explode the largest piece of the pie to a Point Explosion of 20%

u. Add Data Callout data labels to the chart.

Sales Analysis

You will analyze the sales numbers across each month to determine in which months buyers tend to purchase houses.

v. Calculate a new total row for each month.

w. Create a 2D-Line chart that displays total house sales by Month and apply chart style Style 6 and move it to the right of the pie chart.

x. Change the chart title to Total Sales

y. Change the plotted line color to White, Background 1.

z. Add a column to the Date table after House Type called Sparkline.

aa. Add a sparkline for each house type over the 12-month period in the new Sparkline column.

Finalize the Workbook

You will make some small formatting changes.

bb. Set the page orientation to Landscape.

cc. Insert a footer with your instructor’s name on the left side, the date in the center, and your Last,First name on the right side.

dd. Save and close the workbook. Based on your instructor’s directions, submit e00_cumexam_LastFirst.

Mark’s Construction and Realty

Mark’s Construction and Realty buys and flips houses. They have hired you to look at their sales numbers from last year to analyze the data. They have instructed you to see if there are any trends in the types of houses they typically purchase, if there is a correlation between the number of houses sold and sale date, and which sales should be included in the project managers’ year-end bonus.

Perform Preliminary Work

You need to open the existing workbook they provided, save it with a new name, and make a few preliminary modifications.

a. Open the e00_cumexam_data workbook and save the workbook as e00_cumexam_LastFirst.

b. Change the width of column E to 15.00.

c. Insert a new row above row 1.

d. Enter the text 2017 Sales Data in cell A1, change the font size to 14 and apply bold.

e. Add a fill color of Blue, Accent 1 for cell A1.

f. Merge and center the range A1:I1.

g. Freeze the headers so when you scroll down the headers are always visible.

Calculate Profit and Determine if Project is Eligible for Bonus

You are ready to calculate the total costs.

h. Insert two columns after Sale Price named Total Cost and Profit respectively.

i. Insert a function in cell H3 to calculated the total cost of each project.

j. Insert a function in cell I3 to calculated the profit of each project.

k. Create an IF statement in cell K3 where if the profit is greater than \$50,000 it will display the text Yes, otherwise it will display the text No.

l. Copy the three formulas down their respective columns.

Organize Monthly Data

The company has already collected data about how many of each type of house they sold each month. You will convert the data into a table, apply a table style to make the data more visually appealing, and add total rows for use in the analysis grids later.

m. Click the Sheet2 worksheet and rename it Analysis.

n. Convert the entire dataset into a new table named Date with a table style of Table Style Medium 6.

o. Calculate a total for each house type in column N.

House Type Analysis

You will analyze each house type to determine if the company has a tendency to purchase a certain type of a house.

p. Create a 2D-Pie chart that displays the total number of houses by house type and apply chart type Style 7.

q. Move the chart to the left side of the screen directly under the table data.

r. Change the chart title to House Type.

s. Move the legend to the top-left corner.

t. Explode the largest piece of the pie to a Point Explosion of 20%

u. Add Data Callout data labels to the chart.

Sales Analysis

You will analyze the sales numbers across each month to determine in which months buyers tend to purchase houses.

v. Calculate a new total row for each month.

w. Create a 2D-Line chart that displays total house sales by Month and apply chart style Style 6 and move it to the right of the pie chart.

x. Change the chart title to Total Sales

y. Change the plotted line color to White, Background 1.

z. Add a column to the Date table after House Type called Sparkline.

aa. Add a sparkline for each house type over the 12-month period in the new Sparkline column.

Finalize the Workbook

You will make some small formatting changes.

bb. Set the page orientation to Landscape.

cc. Insert a footer with your instructor’s name on the left side, the date in the center, and your Last,First name on the right side.

dd. Save and close the workbook. Based on your instructor’s directions, submit e00_cumexam_LastFirst.

Mark’s Construction and Realty

Mark’s Construction and Realty buys and flips houses. They have hired you to look at their sales numbers from last year to analyze the data. They have instructed you to see if there are any trends in the types of houses they typically purchase, if there is a correlation between the number of houses sold and sale date, and which sales should be included in the project managers’ year-end bonus.

Perform Preliminary Work

You need to open the existing workbook they provided, save it with a new name, and make a few preliminary modifications.

a. Open the e00_cumexam_data workbook and save the workbook as e00_cumexam_LastFirst.

b. Change the width of column E to 15.00.

c. Insert a new row above row 1.

d. Enter the text 2017 Sales Data in cell A1, change the font size to 14 and apply bold.

e. Add a fill color of Blue, Accent 1 for cell A1.

f. Merge and center the range A1:I1.

g. Freeze the headers so when you scroll down the headers are always visible.

Calculate Profit and Determine if Project is Eligible for Bonus

You are ready to calculate the total costs.

h. Insert two columns after Sale Price named Total Cost and Profit respectively.

i. Insert a function in cell H3 to calculated the total cost of each project.

j. Insert a function in cell I3 to calculated the profit of each project.

k. Create an IF statement in cell K3 where if the profit is greater than \$50,000 it will display the text Yes, otherwise it will display the text No.

l. Copy the three formulas down their respective columns.

Organize Monthly Data

The company has already collected data about how many of each type of house they sold each month. You will convert the data into a table, apply a table style to make the data more visually appealing, and add total rows for use in the analysis grids later.

m. Click the Sheet2 worksheet and rename it Analysis.

n. Convert the entire dataset into a new table named Date with a table style of Table Style Medium 6.

o. Calculate a total for each house type in column N.

House Type Analysis

You will analyze each house type to determine if the company has a tendency to purchase a certain type of a house.

p. Create a 2D-Pie chart that displays the total number of houses by house type and apply chart type Style 7.

q. Move the chart to the left side of the screen directly under the table data.

r. Change the chart title to House Type.

s. Move the legend to the top-left corner.

t. Explode the largest piece of the pie to a Point Explosion of 20%

u. Add Data Callout data labels to the chart.

Sales Analysis

You will analyze the sales numbers across each month to determine in which months buyers tend to purchase houses.

v. Calculate a new total row for each month.

w. Create a 2D-Line chart that displays total house sales by Month and apply chart style Style 6 and move it to the right of the pie chart.

x. Change the chart title to Total Sales

y. Change the plotted line color to White, Background 1.

z. Add a column to the Date table after House Type called Sparkline.

aa. Add a sparkline for each house type over the 12-month period in the new Sparkline column.

Finalize the Workbook

You will make some small formatting changes.

bb. Set the page orientation to Landscape.

cc. Insert a footer with your instructor’s name on the left side, the date in the center, and your Last,First name on the right side.

dd. Save and close the workbook. Based on your instructor’s directions, submit e00_cumexam_LastFirst.

Mark’s Construction and Realty Mark’s Construction and Realty

Mark’s Construction and Realty buys and flips houses. They have hired you to look at their sales numbers from last year to analyze the data. They have instructed you to see if there are any trends in the types of houses they typically purchase, if there is a correlation between the number of houses sold and sale date, and which sales should be included in the project managers’ year-end bonus.
Mark’s Construction and Realty buys and flips houses. They have hired you to look at their sales numbers from last year to analyze the data. They have instructed you to see if there are any trends in the types of houses they typically purchase, if there is a correlation between the number of houses sold and sale date, and which sales should be included in the project managers’ year-end bonus.

Perform Preliminary Work
Perform Preliminary Work

You need to open the existing workbook they provided, save it with a new name, and make a few preliminary modifications.
You need to open the existing workbook they provided, save it with a new name, and make a few preliminary modifications.

a. Open the e00_cumexam_data workbook and save the workbook as e00_cumexam_LastFirst.
a. Open the e00_cumexam_data workbook and save the workbook as e00_cumexam_LastFirst.

b. Change the width of column E to 15.00.
b. Change the width of column E to 15.00.

c. Insert a new row above row 1.
c. Insert a new row above row 1.

d. Enter the text 2017 Sales Data in cell A1, change the font size to 14 and apply bold.
d. Enter the text 2017 Sales Data in cell A1, change the font size to 14 and apply bold.

e. Add a fill color of Blue, Accent 1 for cell A1.
e. Add a fill color of Blue, Accent 1 for cell A1.

f. Merge and center the range A1:I1.
f. Merge and center the range A1:I1.

g. Freeze the headers so when you scroll down the headers are always visible.
g. Freeze the headers so when you scroll down the headers are always visible.

Calculate Profit and Determine if Project is Eligible for Bonus
Calculate Profit and Determine if Project is Eligible for Bonus

You are ready to calculate the total costs.
You are ready to calculate the total costs.

h. Insert two columns after Sale Price named Total Cost and Profit respectively.
h. Insert two columns after Sale Price named Total Cost and Profit respectively.

i. Insert a function in cell H3 to calculated the total cost of each project.
i. Insert a function in cell H3 to calculated the total cost of each project.

j. Insert a function in cell I3 to calculated the profit of each project.
j. Insert a function in cell I3 to calculated the profit of each project.

k. Create an IF statement in cell K3 where if the profit is greater than \$50,000 it will display the text Yes, otherwise it will display the text No.
k. Create an IF statement in cell K3 where if the profit is greater than \$50,000 it will display the text Yes, otherwise it will display the text No.

l. Copy the three formulas down their respective columns.
l. Copy the three formulas down their respective columns.

Organize Monthly Data
Organize Monthly Data

The company has already collected data about how many of each type of house they sold each month. You will convert the data into a table, apply a table style to make the data more visually appealing, and add total rows for use in the analysis grids later.
The company has already collected data about how many of each type of house they sold each month. You will convert the data into a table, apply a table style to make the data more visually appealing, and add total rows for use in the analysis grids later.

m. Click the Sheet2 worksheet and rename it Analysis.
m. Click the Sheet2 worksheet and rename it Analysis.

n. Convert the entire dataset into a new table named Date with a table style of Table Style Medium 6.
n. Convert the entire dataset into a new table named Date with a table style of Table Style Medium 6.

o. Calculate a total for each house type in column N.
o. Calculate a total for each house type in column N.

House Type Analysis
House Type Analysis

You will analyze each house type to determine if the company has a tendency to purchase a certain type of a house.
You will analyze each house type to determine if the company has a tendency to purchase a certain type of a house.

p. Create a 2D-Pie chart that displays the total number of houses by house type and apply chart type Style 7.
p. Create a 2D-Pie chart that displays the total number of houses by house type and apply chart type Style 7.

q. Move the chart to the left side of the screen directly under the table data.
q. Move the chart to the left side of the screen directly under the table data.

r. Change the chart title to House Type.
r. Change the chart title to House Type.

s. Move the legend to the top-left corner.
s. Move the legend to the top-left corner.

t. Explode the largest piece of the pie to a Point Explosion of 20%
t. Explode the largest piece of the pie to a Point Explosion of 20%

u. Add Data Callout data labels to the chart.
u. Add Data Callout data labels to the chart.

Sales Analysis
Sales Analysis

You will analyze the sales numbers across each month to determine in which months buyers tend to purchase houses.
You will analyze the sales numbers across each month to determine in which months buyers tend to purchase houses.

v. Calculate a new total row for each month.
v. Calculate a new total row for each month.

w. Create a 2D-Line chart that displays total house sales by Month and apply chart style Style 6 and move it to the right of the pie chart.
w. Create a 2D-Line chart that displays total house sales by Month and apply chart style Style 6 and move it to the right of the pie chart.

x. Change the chart title to Total Sales
x. Change the chart title to Total Sales

y. Change the plotted line color to White, Background 1.
y. Change the plotted line color to White, Background 1.

z. Add a column to the Date table after House Type called Sparkline.
z. Add a column to the Date table after House Type called Sparkline.

aa. Add a sparkline for each house type over the 12-month period in the new Sparkline column.
aa. Add a sparkline for each house type over the 12-month period in the new Sparkline column.

Finalize the Workbook
Finalize the Workbook

You will make some small formatting changes.
You will make some small formatting changes.

bb. Set the page orientation to Landscape.
bb. Set the page orientation to Landscape.

cc. Insert a footer with your instructor’s name on the left side, the date in the center, and your Last,First name on the right side.
cc. Insert a footer with your instructor’s name on the left side, the date in the center, and your Last,First name on the right side.

dd. Save and close the workbook. Based on your instructor’s directions, submit e00_cumexam_LastFirst.
dd. Save and close the workbook. Based on your instructor’s directions, submit e00_cumexam_LastFirst.

### Related Questions

##### Have to answer the questions from the textboo...

Have to answer the questions from the textbook using the excel data file.I’m attaching text book and excel data file(Data for – Lab #6.xlsx).have...

##### Choose one of the attached documents below an...

Choose one of the attached documents below and write a one paragraph response to the argument.  You may agree or disagree or both.  In your response...

##### I want you to chose one of the following topi...

I want you to chose one of the following topics to write on. Please write 1000 to 1500, and be sure to cite your sources. Write on one of the two of t...

##### Go to this website and read the short essay:h...

Go to this website and read the short essay:http://www.forbes.com/sites/chriscancialosi/2016/04/04/how-great-leaders-manage-underperforming-teams/#a5e...

##### Do I Have a Core Essence?The purpose of this d...

Do I Have a Core Essence?The purpose of this discussion assignment is to reflect upon your understanding of different historical and philosophical app...

##### Intelligence against the War on TerrorismYou serve...

Intelligence against the War on TerrorismYou serve on the staff of a US senator, who has just been appointed to the Senate Intelligence Committee. She...

##### 1) Select a societal problem (social phenomena) th...

1) Select a societal problem (social phenomena) that you are interested in. Be sure that there is enough information on your topic for you to be able...

##### Must include (Company Name, Course Title and Semes...

Must include (Company Name, Course Title and Semester, Date, and Your Name) Introduction on the company (overview), what does the company do, what pr...

##### In a paper, discuss various governmental concepts...

In a paper, discuss various governmental concepts associated with concepts of information technology. Be certain to address the following: Examine th...

##### The Department of Homeland Security vs. Terrorists...

The Department of Homeland Security vs. TerroristsAfter the September 11, 2001 attacks, the US government established the Department of Homeland Secur...

##### Students are asked to review the developments and...

Students are asked to review the developments and introduction of both garment types and new fabrications in the specified time frame and write a syno...

##### Create the “shell” of your synthesis paper in...

Create the “shell” of your synthesis paper in APA format. Create your title page with proper running head, title/author section and page numbers,...

##### Analyze the adequacy of the C-I-A triad in selecti...

Analyze the adequacy of the C-I-A triad in selecting controls for Windows systems.Assignment RequirementsNonrepudiation is the ability to have proof t...

##### Primary Discussion Response is due by Friday (11:5...

Primary Discussion Response is due by Friday (11:59:59pm Central), Peer Responses are due by Tuesday (11:59:59pm Central).Primary Task Response:Within...

##### The CEO of the company you work for just announced...

The CEO of the company you work for just announced that 20 people were terminated due to budget cuts. You find out that one of those people is your co...

##### Assignment 1 InstructionsAssignment 1 is due in We...

Assignment 1 InstructionsAssignment 1 is due in Week 4. The link below to submit your assignment will be available in Week 2. Review the assignment i...

##### What makes a life worth living?In 1 page, expl...

What makes a life worth living?In 1 page, explain the idea that the unexamined life is not worth living and discuss your own ideas about what makes li...

##### Discrimination (for the purposes of our course) is...

Discrimination (for the purposes of our course) is NOT an ethical theory, it is an ethical issue. The goal of this assignment is to test your ability...

##### Madrid 2004 BombingIn March, 2004, commuter trains...

Madrid 2004 BombingIn March, 2004, commuter trains in Madrid, Spain were attacked, and terrorism was suspected. Research this incident and prepare a r...

##### A strategic purpose for a well-blended compensatio...

A strategic purpose for a well-blended compensation program, one that includes various types of direct compensation, is gaining employee commitment an...

##### DQ5-1...

Select two of the following discussion questions for your discussion response. Indicate which questions you have chosen using the format displayed in...

##### Responsibility Charting Project...

Responsibility ChartingWrite one or two paragraphs introducing this phase of the action research process. A responsibility chart is a helpful tool...

##### Assignment 4...

Modifications:As for your M4A2 assignment, here’s the modification (same details below): You will write a 3-5-page paper, rather than 8-10-pages as...

##### Continuation problem Part 6...

The existing company I work with previously is Apple, Inc. Part 6: Critical Milestones (Topic 7)Gantt ChartRefer to Gantt chart resources in the topi...

## If you didn't find the right answer

Ask Your Questions, We'll notify you once someone answers it