New Perspectives Excel 2016 | Modules 9-12: SAM Capstone Project 1a Vitality Exercise Equipment ANALYZING DATA AND WORKING WITH FINANCIAL TOOLS GETTING STARTED ? Open the file NP_EX16_CS9-12a_ FirstLastName _1

Subject:MS ExcelPrice:20.87 Bought26

Share With

New Perspectives Excel 2016 | Modules 9-12: SAM Capstone Project 1a

Vitality Exercise Equipment

ANALYZING DATA AND WORKING WITH FINANCIAL TOOLS

GETTING STARTED

?

Open the file

NP_EX16_CS9-12a_

FirstLastName

_1.xlsx

, available for

download from the SAM website.

?

Save the file as

NP_EX16_CS9-12a_

FirstLastName

_2.xlsx

by changing the

“1” to a “2”.

o

If you do not see the

.xlsx

file extension

in the Save As dialog box, do not

type it. The program will add the file extension for you automatically.

?

To complete this SAM Project, you will also need to download and save the

following data files from the SAM website onto your computer:

o

Support_NP_EX16_CS9-12a_Mora.xlsx

o

Support_NP_EX16_CS9-12a_History.csv

o

Support_NP_EX16_CS9-12a_Orders.csv

o

Support_NP_EX16_CS9-12a_Sales.csv

NOTE: Do not open the support files as you may not be able to merge them.

?

With the file

NP_EX16_CS9-12a_

FirstLastName

_2.xlsx

still open, ensure that

your first and last name is displayed in cell B6 of the Documentation sheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

?

To complete this project, you need to add the Compare and Merge Workbooks

button to the Quick Access Toolbar

as follows:

o

On the

Quick

Access Toolbar, click the

Customize Quick Access Toolbar

button, and then click

More Commands

.

o

In the

Excel

Options dialog box, click the

Choose commands from

arrow, and then click

Commands Not in the Ribbon

.

o

In the list of commands, click

Compare and Merge Workbooks

, and

then click the

Add

button.

o

Click the

OK

button to close the Excel Options dialog box.

?

To complete this project, you also need to use the Solver

add-in

.

If this add-in is

not available on the Data tab in the Analyze

group (or if the Analyze

group is

not available), install

Solver

as follows:

o

In Excel, click the

File

tab, and then click the

Options

button in the left

navigation bar.

o

Click the

Add-Ins

option in the left pane of the Excel Options dialog box.

 

Click the

Manage

arrow, click the

Excel Add-Ins

option, and then click

the

Go

button.

o

In the Add-Ins dialog box, click the

Solver Add-In

check box and then

click the

OK

button.

o

Follow any remaining prompts to install Solver

.

?

You also need to add the Power Pivot tab to the Ribbon

as follows:

o

On the File tab, click the

Options

button. In the Data section of the

Advanced tab, click the check box next to Enable Data Analysis add-ins:

Power Pivot

,

Power View

, and

3D Maps

, and click

OK

.

*Power Pivot, Power View, and 3D Maps are not available on Microsoft

Office Home and Business 2016 and Microsoft Office 365 Business.

PROJECT STEPS

1.

You are an assistant at Vitality Exercise Equipment, a company that

manufactures a variety of exercise equipment, including treadmills and elliptical

machines. You work with Sarah McCoy, a financial analyst, who wants to

examine the financial details of the company’s treadmill models. You also need

to incorporate revisions and comments from Alex Mora, the company

accountant, calculate loan information, forecast sales, and analyze sales data.

Use the

Compare and Merge Workbooks

feature to include comments and

changes from Alex Mora

by merging

the

Support_NP_EX16_CS9-

12a_Mora.xlsx

workbook, available from the SAM website, into the

NP_EX16_CS9-12a_

FirstLastName

_2.xlsx

workbook.

2.

Switch to the

Home

worksheet. Review and respond to the comments in the

workbook as follows:

a.

Respond to the comment from Alex Mora in cell D6 by changing the value

in cell D6 as Alex requests.

b.

Delete the comment in cell D6.

c.

Review the next comment in the workbook, which is from Alex Mora in cell

D12. Edit the comment by including

Yes

as the response.

3.

Switch to the

Ellipticals

worksheet. Using the

Accept/Reject Changes

command, specify that you want to review the changes made in the range

A4:D13

.

Do not specify when or who made the changes.

a.

Reset cell A13 to its original value.

b.

Accept the change made by Alex Mora in cell C6.

c.

Reject the change in cell D9.

Turn off workbook sharing. (

Hint

: Click Yes when prompted by the warning

message.)

4.

Return to the

Home

worksheet. Perform a break-even analysis for home

treadmills as follows:

a.

In cell B27, use Goal Seek to set cell

B27

to a value of

0

 

b.

Change the number of units sold in cell

B26

to break even, or reach a

Gross Profit of $0. (

Hint

: The number format applied to cell B27 will display

the value of $0 as $ -.)

5.

Create a one-variable data table to calculate sales, expenses, and profits based

on the number of home treadmills sold as follows:

a.

In cell D5, enter a formula that references cell

B4

, which is the expected

units sold for this product.

b.

In cell E5, enter a formula that references cell

B19

, which is the expected

total sales for this product.

c.

In cell F5, enter a formula that references cell

B20

, which is the expected

total expenses for this product.

d.

In cell G5, enter a formula that references cell

B21

, which is the expected

gross profit for this product.

e.

Select the range D5:G10 and then complete the one-variable data table,

using cell

B4

as the Column input cell for your data table.

6.

Create a two-variable data table to calculate the gross profit based on the

number of home treadmills sold and the price per unit:

a.

For the range D14:K19, create a two-variable data table using the price

per unit (cell

B5

) as the Row input cell.

b.

Use the units sold (cell

B4

) as the Column input cell.

7.

Apply a custom format to cell

D14

to display the text

Units Sold

in place of the

cell value.

8.

Switch to the

Pro

worksheet. Create a

Scatter with Straight Lines and

Markers

chart based on range D5:F14 in the data table Pro Treadmills – Break-

Even Analysis.

9.

Modify the new chart as follows:

a.

Resize and reposition the chart so that it covers the range D15:I30.

b.

Remove the chart title from the chart.

c.

Add

Revenue and Expenses

as the vertical axis title and

Units Sold

as

the horizontal axis title.

10.

Edit the chart series names as follows so that the legend identifies the series

more clearly:

a.

For Series 1, set the series name to cell

E4

. (

Hint

: The series name should

automatically update to “=Pro!$E$4”.)

b.

For Series 2, set the series name to cell

F4

.

11.

Sarah wants to compare the costs for standard and high-end components in the

pro treadmills. She has already created a Standard scenario for the variable and

fixed costs of standard components. Create a scenario for high-end components

as follows:

a.

In the Scenario Manager, add a new scenario using the data shown in bold

in Table 1 below.

b.

The changing cells are the nonadjacent cells

B11

and

B14

.

c.

Close the Scenario Manager without showing any of the scenarios.

Table 1: Pro Treadmill Scenario Values

Values

New Scenario

Scenario Name

High-End

Pro_Variable_Cost (B11)

810.00

Pro_Fixed_Cost (B14)

285000

12.

Switch to the

Commercial

worksheet. Modify the Scatter with Straight Lines and

Markers chart in the range D15:I32 as follows:

a.

Reposition the chart legend to the

Right

of the chart.

b.

Change the colors of the chart to

Monochromatic Palette 1

(1

st

row in

the Monochromatic palette). (

Hint

: Depending on your version of Office,

the name of the color may be different.)

c.

Change the Minimum Bounds of the vertical axis to

-100000

and the

Maximum Bounds to

750000

.

d.

Change the Minimum Bounds of the horizontal axis to

850

and the

Maximum Bounds to

1650

.

13.

Vitality Exercise Equipment wants to determine whether subcontracting the

manufacture of their elliptical machines to other manufacturing companies

would reduce the costs of the equipment. Switch to the

Ellipticals

worksheet,

and then run Solver to solve this problem as follows:

a.

Set the objective as minimizing the value in cell

E10

(Total Costs).

b.

Use the range

B4:D4

as the changing variable cells.

c.

Adjust the number of units produced by each company using the following

constraints:

o

E4=12000

, the total number of ellipticals produced

o

E10<=2500000

, the maximum total cost

o

B4:D4<=4600

, the maximum number of ellipticals produced by a

single manufacturer

o

B4:D4

should be an

Integer

d.

Run Solver using the

GRG Nonlinear

solving method and setting

unconstrained variables to non-negative values. Keep the solution, and

then return to the Solver Parameters dialog box. Save the model to the

range

A14:A21

, and then close the Solver Parameters dialog box.

14.

Switch to the

All Treadmills

worksheet. Use the Scenario Manager to create a

Scenario Summary report that summarizes the effect of the Current Prices,

 

Subcontract, and Raise Prices $100 scenarios. Use cells

B17:D17

as the result

cells.

15.

Switch back to the

All Treadmills

worksheet. Use the Scenario Manager as

follows to compare the profit per unit in each scenario:

a.

Create a Scenario PivotTable report for result cells

B17:D17

.

b.

Remove the Filter field from the PivotTable.

c.

Change the number format of the

Profit_per_Unit_Sold_Home

,

Profit_per_Unit_Sold_Pro

, and

Profit_per_Unit_Sold_Commercial

fields (located in the Values box of the PivotTable Field List) to

Currency

with

2

decimal places and

$

as the symbol.

d.

Use

Home

as the row label value in cell B3,

Pro

as the value in cell C3,

and

Commercial

as the value in cell D3.

e.

In cell A1, use

Profit per Unit Sold

as the report title.

f.

Format the report title using the

Title

cell style.

g.

Resize column A using AutoFit. Resize columns B-D to

14.00

.

16.

Add a PivotChart to the

Scenario PivotTable

worksheet as follows:

a.

Create a

Clustered Column

PivotChart based on the PivotTable.

b.

Resize and reposition the chart so that it covers the range A8:D20.

c.

Hide all the field buttons in the chart.

17.

Vitality Exercise Equipment wants to expand by adding one or more industrial

robots to their assembly line. The company is also considering whether to build

an entire new assembly line. In any case, the company needs a loan to cover

the cost of expansion.

Switch to the

Loan Options

worksheet, and then calculate the monthly payment

for the Add 1 Robot option as follows:

a.

In cell D11, enter a formula using the

PMT

function to calculate the

monthly payment for a loan.

b.

Use the inputs listed under the Add 1 Robot loan option in cells

D7

,

D9

,

and

D5

. (

Hint

: The result will be displayed as a negative number to reflect

the negative cash flow of a loan payment.)

18.

Calculate the monthly interest rate for the Add 2 Robots option as follows:

a.

In cell E7, enter a formula using the

RATE

function to calculate the

monthly interest rate for a loan.

b.

Use the inputs listed under the Add 2 Robots

loan option in cells

E9

,

E11

,

and

E5

. (

Hint

: Assume the present value of the loan is the loan amount

shown in cell E5.)

19.

Calculate the loan amount for the Add 3 Robots option as follows:

a.

In cell F5, enter a formula using the

PV

function to calculate the loan

amount.

b.

Use the inputs listed under the Add 3 Robots loan option in cells

F7

,F9 and F11. 

20.

Calculate the number of months Sarah needs to pay back a loan for a new

assembly line as follows:

a.

In cell G9, enter a formula using the

NPER

function to calculate how many

months it would take to pay back a $450,000 loan.

b.

Use the inputs listed under the New Line loan scenario in cells

G7

,

G11

,

and

G5

.

21.

Switch to the

Amortization

worksheet. Calculate the cumulative interest for a

loan for one robot as follows:

a.

In cell C17, enter a formula using the

CUMIPMT

function to calculate the

cumulative interest paid on the loan after the first year (payment 1 in cell

C15

through payment 12 in cell

C16

) when the payments are made at the

end of the period. Use

0

as the type argument in your formula.

b.

Use absolute references for the rate, nper, and pv arguments.

c.

Use relative references for the start and end arguments.

d.

Copy the formula from cell C17 to the range D17:G17 to calculate the

interest paid in Years 2–5.

22.

Calculate the cumulative principal for a loan for one robot as follows:

a.

In cell C18, enter a formula using the

CUMPRINC

function to calculate the

cumulative principal paid in the first year (payment 1 in cell

C15

through

payment 12 in cell

C16

) when the payments are made at the end of the

period. Use

0

as the type argument in your formula.

b.

Use absolute references for the rate, nper, and pv arguments.

c.

Use relative references for the start and end arguments.

d.

Copy the formula from cell C18 to the range D18:G18 to calculate the

principal paid in Years 2–5.

23.

In cell H18, use the Error Checking

command to identify the error in the cell,

and then correct the error. (

Hint

: The formula in the cell should calculate the

total the values in C18:G18 using the SUM function.)

24.

Calculate the principal amounts in the loan amortization schedule as follows:

a.

In cell E23, enter a formula using the

PPMT

function to determine the

amount of the first loan payment devoted to principal.

b.

Use absolute references only for the rate, nper, and pv arguments.

c.

Use cell

A23

as the current period. (

Hint

: The period is based on a

monthly payment schedule.)

d.

Copy the formula from cell E23 to the range E24:E82 to calculate the

principal paid in Periods 2–60.

25.

Calculate the interest amounts in the loan amortization schedule as follows:

a.

In cell F23, enter a formula using the

IPMT

function to determine the

amount of the first loan payment devoted to interest.

b.

Use absolute references only for the rate, nper, and pv arguments.

c.

Use cell

A23

as the current period. (

Hint

: The period is based on a

monthly payment schedule.

d.

Copy the formula from cell F23 to range F24:F82 to calculate the principal

paid in Periods 2–60.

26.

Switch to the

Depreciation

worksheet. Calculate the annual straight-line

depreciation for one new robot (the most likely scenario) as follows:

a.

In cell C11, enter a formula using the

SLN

function to calculate the

straight-line depreciation for the new robot during its first year of service.

b.

Use the values in cells

D5

,

D6

, and

D7

for the arguments.

c.

Use absolute references for the cost, salvage, and life arguments in the

SLN formula.

d.

Copy the formula from cell C11 to the range D11:I11 to calculate the

yearly straight-line depreciation in Years 2

–7.

27.

Calculate the annual declining balance depreciation for one new robot as

follows:

a.

In cell C18, enter a formula using the

DB

function to calculate the

declining balance depreciation for the new robot during its first year of

service.

b.

Use the values in cells

D5

,

D6

, and

D7

for the arguments.

c.

Use the value in cell

C17

as the current period.

d.

Use absolute references only for the cost, salvage, and life arguments in

the DB formula.

e.

Copy the formula from cell C18 to the range D18:I18 to calculate the

yearly declining balance depreciation in Years 2

–7.

28.

Correct the errors on the

Depreciation

worksheet as follows:

a.

Determine the error in cell D19 by using the Trace Precedent and Trace

Dependent arrows. Correct the error so that the formula in cell D19

calculates the cumulative depreciation of the vehicle fleet by adding the

Cumulative Depreciation value in Year 1 to the Yearly Depreciation value

in Year 2.

b.

Copy the corrected formula in cell D19 to the range E19:I19, and then

remove any arrows from the worksheet, if necessary.

29.

Switch to the

Income Statement

worksheet. For the Add 1 Robot option, project

income and expenses as follows:

a.

Project the income from Home treadmills

for 2020-2022 (cells D5:F5)

using a Growth Trend interpolation. (

Hint

: Select the range C5:G5 before

filling this series with values.)

b.

Project the income from Commercial treadmills for 2020-2022 (cells

D7:F7) using a Linear Trend interpolation. (

Hint

: Select the range C7:G7

before filling this series with values.)

c.

Project the expenses for Insurance for 2020-2023 (cells D13:G13) using a

Growth Trend extrapolation and a step value of

1.06

. (

Hint

: When

extrapolating values, the Trend check box in the Series Dialog Box

should

not be checked.) Do not set a stop value for the series. (

Hint

: Select the

range C13:G13 before filling this series with values.)

30.

Sarah asks you to produce a sales report for all products sold in five regions

since the company was founded in 1979, and then to project future sales. To

create the report, you need to import data from various sources and use the

Excel Business Intelligence (BI) tools.

Switch to the

Sales History

worksheet.

Use Power Query to create a query and

load data from a CSV file into a new table as follows:

a.

Create a new query that imports data from the

Support_NP_EX16_CS9-

12a_History.csv

file, available for download from the SAM website.

b.

Edit the query to remove the Units Sold column, then close and load the

query data to a table in cell A3 of the existing worksheet. (

Hint

: Use the

Close & Load to... button.)

31.

Insert a chart to identify trends in the sales history data as follows:

a.

Insert a

Scatter

chart based on the values in the range B3:C24.

b.

Move and resize the chart so that it covers the range D3:J20.

c.

Add the axis title

Sales ($mil)

to the vertical axis.

d.

Use

Sales Trend

as the chart title.

e.

Add a

Logarithmic

trendline to the chart.

32.

Switch to the

Monthly Sales

worksheet.

Use Power Query as follows to create a

query that loads sales data from a CSV file containing daily orders from the past

two years and transforms the data to display only monthly totals:

a.

Beginning in cell A3, create a new query that imports data from the

Support_NP_EX16_CS9-11a_Orders.csv

file, available for download

from the SAM website.

b.

Edit the query to remove all columns except the OrderDate and Sales

columns.

c.

Based on the OrderDate column, add a new

Date

column to the query

using the

End of Month

option. (

Hint

: Select the OrderDate column

before selecting options on the Add Column tab.)

d.

Use

Month

as the name of the new column.

To display the total sales for each month, group the dates in the Month column

and create a new column displaying the sum of the Sales values as follows:

e.

Transform the data in the Month column by grouping the data by month.

f.

Use

Monthly Sales

as the name of the new column.

g.

Use

Sum

as the operation.

h.

Use

Sales

as the column to sum within the Month group.

i.

Load the query data to a table in the existing worksheet beginning in cell

A3.

j.

Format the data in the range B4:B27 using the

Currency

number format

with

2

decimal places and the

$

symbol.

33.

Create a forecast sheet to track the changes in monthly sales and project next

year’s monthly sales as follows:

a.

Based on the data in the range A3:B27, create a forecast sheet.

b.

Using the Options menu in the Create Forecast Worksheet dialog box,

manually set the seasonality to

12

.

c.

Use

12/31/2020

as the Forecast End date. (

Hint

: After clicking the Create

button, click the Got it! button if the FORECAST SHEET dialog box opens.)

d.

Use

Monthly Sales Forecasts

as the name of the new sheet.

e.

Resize and move the forecast chart to cover the range C2:E25.

34.

Switch to the

Products Sold by Location

worksheet. Create another query and

load data from a CSV file to display sales information by month and region as

follows:

a.

Create a new query that imports data from the

Support_NP_EX16_CS9-

11a_Sales.csv

file, available for download from the SAM website.

b.

Preview the Orders worksheet, and then only create a connection to the

data. (

Hint

: Use the Load To button arrow to open the Load To dialog box.)

c.

Add the data to the Data Model.

Create a PivotTable that shows products sold in each of five regions during 2018

and 2019 as follows:

d.

In cell A3, insert a PivotTable using the workbook’s Data Model.

e.

Use the following fields from the

Support_NP_EX16_CS9-11a_Sales

table in

the PivotTable areas:

o

Units field: Values box

o

Region field: Columns box

o

OrderDate field: Rows box

35.

Format the PivotTable to make the data easier to interpret as follows:

a.

Use

Units Sold

as the custom name of the Sum of Units field.

b.

Format the Sum of Units values using the

Number

format with

0

decimal

places and a

1000 separator

.

36.

Insert a Timeline Slicer to show items sold in March, 2019 as follows:

a.

Insert a Timeline Slicer that uses the OrderDate field from the active table.

b.

Move and resize the Timeline Slicer to cover the range A9:G15.

c.

Use the Timeline Slicer to display items sold only in March, 2019.

Your workbook should look like the Final Figures below. (The value in cell B26 of the

Home

worksheet generated by the Goal Seek analysis has intentionally been blurred

out in the Final Figure.) The

Ellipticals

worksheet is not provided to prevent manual

entry of the Solver results.

Save your changes, close the workbook, and then exit

Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Home Worksheet

Final Figure 2: Pro Worksheet

Final Figure 3: Commercial Worksheet

Final Figure 4: Scenario Summary Worksheet

Final Figure 5: Scenario PivotTable Worksheet

Final Figure 6: All Treadmills Worksheet

Final Figure 7: Loan Options Worksheet

Final Figure 8: Amortization Worksheet

Final Figure 9: Depreciation Worksheet

Final Figure 10: Income Statement Worksheet

Final Figure 11: Sales History Worksheet

Final Figure 12: Monthly Sales Forecasts Worksheet

Final Figure 13: Monthly Sales Worksheet

Final Figure 14: Products Sold by Location Worksheet