devry bis155 full couse [ all discussions all ilabs and all quizes ]

| June 6, 2016

Question

discussions

week 1

Getting Familiar with Excel (graded)
Excel was introduced in 1985. Since then, it has become a standard business tool. In this thread, discuss the different features of Excel that make it a valuable business tool. In addition, discuss why Excel skills are so important in today’s job market.

week 2
Formulas and Functions (graded)
One of the benefits of Excel is the ability to use formulas and functions. Discuss the differences between formulas and functions. Pick a function in Excel and discuss how that function is used to calculate results in your worksheets. Using the following scenarios, discuss how you would apply the Excel functions or create a formula to solve the scenario.

You wish to calculate the commission on sales. The commission is 6% on all sales that are at least 20% above cost.
You wish to calculate the total pay for an employee who receives regular time for 40 hours, time and a half for 40-50 hours, and double time for hours over 50.
You have a list of contracts and due dates for annual maintenance fees. You wish to determine when you have a contract due in the next 45 days and provide a note that warns you that the payment is due.
Please feel free to add other, unique mathematical challenge questions to the discussion, and let us work together to figure out how you might calculate it.

week 3

Data Analysis (graded)
Excel provides many different ways in which to analyze data. Discuss the different methods by which you can analyze data in your spreadsheet. Assume the role of a Regional Manager for a retail organization looking at ways to analyze the large amounts of sales data you have. What are some of the ways Excel can be used to analyze this data? What are some examples of reports you might want to review? Using the concepts from this week, how will you determine where to start and what tools can you use within Excel to help you organize everything and make decisions?

week 4
Excel Data Exchange (graded)
Excel 2010 provides the capability to exchange data with other applications. Discuss the different types of ways to exchange data between Excel and other Microsoft applications. Are there limitations to the exchange process? How can you exchange data with other applications that are not part of the Microsoft Office Suite? Discuss how you would exchange data with external data sources such as websites.

week 5
Data Consolidation and What-if Analysis (graded)
Discuss and compare the data consolidation and What-If analysis Excel tools. Under what circumstances would you use each of these tools? Do they both serve the same purpose, or is each one used under different scenarios? Give examples of how each can be used.

week 6
Bringing It All Together (graded)
This week will be an opportunity to share ideas and approaches to the Excel course project. What are some spreadsheet features you will use to make your spreadsheet professional, readable, reliable, and repeatable? Which spreadsheet functions and data analysis types do you think will be the most challenging to implement in this project? What questions do you have about those functions and types of data analysis?

This section lists options that can be used to view responses.

week 7
Database Decisions (graded)
Discuss the differences between storing data in Access and Excel. Why would you use a spreadsheet over a database? At what point does it become more efficient to store your data in a database rather than a spreadsheet? If you are deciding whether to use a spreadsheet or database, what questions should you consider?

quizes
week 1

Question 1. Question :

(TCO 1) Which of the following describes the function of the Formula Bar (as it applies to Excel 2010)?

Displays the name of a worksheet within a workbook

Is at the intersection of a column and a row

Identifies the address of the current cell

Displays the content of the active cell

: Textbook Chapter 1

2 of 2

Question 2. Question :

(TCO 1) In Excel 2010, a sheet tab ________.

displays the content of the active cell

conceals the applicable formulas for the workbook

identifies the address of the current cell

displays the name of a worksheet within a workbook

: Textbook Chapter 1

2 of 2

Question 3. Question :

(TCO 1) Which of the following best describes the AutoComplete function?

Adds all the numbers in the row automatically

Searches for and displays any other similar label in the current column as you begin to type

Adds all the numbers in the columns automatically

Is a number that represents a quantity and can be the basis of calculations

: Textbook Chapter 1

2 of 2

Question 4. Question :

(TCO 1) Why would you use cell references in formulas instead of constant values?

You can easily include letters, numbers, and spaces

It is easier to debug the errors

You can change the input values without changing the formulas

You can control the sequence in which Excel performs arithmetic operations

: Textbook Chapter 1

2 of 2

Question 5. Question :

(TCO 1) Which of the following best describes the result of using the fill handle on a cell containing a formula?

Cannot complete a sequence of dates in a column

Changes the background color of the selected cells to yellow

Copies the formula in the active cell to other cells and adapts it based upon the type of cell references in the original formula

Has two or more sub-commands related to the command

: Textbook Chapter 1

2 of 2

Question 6. Question :

(TCO 1) Which of the following would allow you to adjust the column width and/or row height of many cells at once?

Just type longer or taller formulas in some of them

Click and drag across the cells to select them and use any sizing method you choose

Drag the fill handle across the cells

Type A1 in the “Go To” dialog box

: Textbook Chapter 1

2 of 2

Question 7. Question :

(TCO 1) If you had worksheets that contained the inventory of each room in your house, with a separate sheet for each room, all the sheets together would be a:

workbook.

ledger.

document.

journal.

: Textbook Chapter 1

2 of 2

Question 8. Question :

(TCO 1) Which of the following describes the horizontal Alignment in Excel 2010?

Refers to the up-down position of contents in a cell

Removes the vertical lines running through the data

Puts a line horizontally through the data

Refers to the left-right position of contents in a cell

: Textbook Chapter 1

2 of 2

Question 9. Question :

(TCO 3) A chart can be defined as ________.

a group of related data points

a visual representation of numerical data

a numeric value that describes a single value on a chart

text that describes a collection of data points in a chart

: Textbook Chapter 3

2 of 2

Question 10. Question :

(TCO 3) A __________ is usually the most effective way to display proportional relationships, such as market share data, where the individual data values represent parts of a whole.

column chart

pie chart

bar chart

line chart

: Textbook Chapter 3

2 of 2

Question 11. Question :

(TCO 3) Which of the following best describes the chart area?

Contains graphical representation of values in data series

Contains the entire chart and all of its elements

A vertical line that borders the plot area to provide a frame of reference for measurement

A horizontal line that borders the plot area to provide a frame of reference for measurement

: Textbook Chapter 3

2 of 2

Question 12. Question :

(TCO 3) To display similar data in a single column, with each series of data distinguished by a different color, use a:

stacked column chart.

pie chart.

3-D column chart.

box diagram.

: Textbook Chapter 3

2 of 2

Question 13. Question :

(TCO 3) To provide your viewers with an explanation of the representations of the colors utilized in the data series in your chart, provide a:

diagram.

pop-up comment box.

legend.

note.

: Textbook Chapter 3

2 of 2

Question 14. Question :

(TCO 3) In Excel, an area chart _______.

shows the high, low, and close prices for individual stocks over time

trends using two dimensions on a continuous curve

emphasizes the magnitude of changes over time by filling in the space between lines with a color

shows a relationship between two variables

: Textbook Chapter 3

2 of 2

Question 15. Question :

(TCO 3) Which of the following best describes a sparkline?

A label that describes either the category axis or the value axis

A miniature chart contained in a single cell

The value or name of a data point

A label that describes the chart

: Textbook Chapter 3

2 of 2

* Times are displayed in (GMT-07:00) Mountain Time (US & Canada)

week 2

(TCO 2) An absolute cell reference:

causes a potential error.

indicates a cell’s specific location and the reference does not change when you copy the formula.

occurs when a formula directly or indirectly refers to the cell containing the formula.

contains both an absolute and a relative cell reference.

: Textbook Chapter 2

Comments:

Question 2. Question :

(TCO 2) Which of the following best describes a mixed cell reference?

Occurs when a formula directly or indirectly refers to the cell containing the formula.

Causes a potential error.

Contains absolute or relative cell references, but not both.

Contains both an absolute and a relative cell reference.

: Textbook Chapter 2

Comments:

Question 3. Question :

(TCO 2) The rules for constructing a function can be referred to as the function’s _____.

synthesis

synergy

synchronization

syntax

: Textbook Chapter 2

Comments:

Question 4. Question :

(TCO 2) To display the current date on each page of an Excel worksheet, use the _____ function.

PRESENT

TODAY

CURRENT

SET

: Textbook Chapter 2

Comments:

Question 5. Question :

(TCO 2) The _____ function returns a result based on a condition.

MAX

TODAY

FIND

IF

: Textbook Chapter 2

Comments:

Question 6. Question :

(TCO 2) The MAX function identifies the _____.

highest value in a range.

number of blank cells in a range.

lowest value in a range.

midpoint value in a range.

: Textbook Chapter 2

Comments:

Question 7. Question :

(TCO 2) The NOW function would perform which of the following?

Contains another function embedded inside one or more of its arguments.

Evaluates a condition and returns one value if the condition is true and a different value if the condition is false.

Evaluates true or false.

Displays the current date and time.

: Textbook Chapter 2

Comments:

Question 8. Question :

(TCO 2) The _____ number is the number of the column in the lookup table that contains the return values.

lookup value

column index

lookup table

random

: Textbook Chapter 2

Comments:

Question 9. Question :

(TCO 2) To copy a formula, you may use the _____, which is found in the bottom right-hand corner of an active cell.

sizing handle

pointer

fill handle

insertion point

: Textbook Chapter 2

Comments:

Question 10. Question :

(TCO 2) To simplify entering ranges in formulas, you can use _____.

range names

a cell’s relative location

both an absolute and a relative cell reference

a cell’s specific location

: Textbook Chapter 2

Comments:

Question 11. Question :

(TCO 2) When calculating a PMT function, the RATE would be described best by which of the following?

It is the periodic interest rate, such as a monthly interest rate.

It is the total number of payment periods.

It is a word or a string of characters that represent one or more cells.

It is the present value of the loan.

: Textbook Chapter 2

Comments:

Question 12. Question :

(TCO 7) A _____ is a partially completed workbook that you can use to create other workbooks that have the same structure and purpose.

theme

style

template

macro

: Textbook Chapter 11

Comments:

Question 13. Question :

(TCO 7) The process of protecting a worksheet requires you to enter a(n) _____.

user name

e-mail address

IN user key

password

: Textbook Chapter 11

Points Received: 0 of 2

Comments:

Question 14. Question :

(TCO 7) You must _____ the recording of the macro when you have completed the tasks it was designed to do.

begin

stop

copy

paste

: Textbook Chapter 11

Comments:

Question 15. Question :

(TCO 7) To create a macro button, click the _____ tab, click Insert, then click Button in the Form Controls section of the Insert palette.

View

Data

Insert

Developer

: see p. 608 in textbook, first point under “Creating Macro Buttons.”

Comments:

week 3

(TCO 4) In Excel, a dataset would be which of the following?

Indicates where data starts on a new printed page

CORRECT Collection of structured, related data in columns and rows

Freezing rows to keep them visible

Freezing panes to keep them visible

: Textbook Chapter 4

2 of 2

Question 2. Question :

(TCO 4) Which of the following best describes a page break?

Freezes only the top row

The sequence in which pages print

CORRECT Indicates where data starts on a new printed page

Stops the printer from printing

: Textbook Chapter 4

2 of 2

Question 3. Question :

(TCO 4) To create a table from an existing range of data, _____.

click within the range, click the Table Tools Design tab, and then select Convert to Table

select any cell in the worksheet and then click Existing Range in the Tools group

click in a cell and on the Home tab, and click the Insert arrow in the Cells group

CORRECT click the Insert tab and then click Table in the Tables group

: Textbook Chapter 4

2 of 2

Question 4. Question :

(TCO 4) Filtering the Cost column (field) to show only records greater than $10,000 is an application of _____.

a Date Filter

a Text Filter

INCORRECT the use of a table element as a formula

CORRECT a Number Filter

: Textbook Chapter 4

0 of 2

Question 5. Question :

(TCO 4) Which of the following best describes conditional formatting?

CORRECT Highlights or emphasizes cells that meet certain conditions

Tags a table element as a reference in a formula

Formats the condition of the worksheet in preparation for printing

Calculates an aggregate for values in a range or database

: Textbook Chapter 4

2 of 2

Question 6. Question :

(TCO 4) In addition to sorting and filtering cells by content, you can sort and/or filter by _____.

top/bottom rule

conditional formatting

icon sets

the data bar

: Textbook Chapter 4

0 of 2

Question 7. Question :

(TCO 4) When data is grouped, the margin area displays the _____.

aggregate columns

group headings

collapse and expand buttons

group totals

: Textbook Chapter 5

0 of 2

Question 8. Question :

(TCO 4) To add a PivotTable, choose the Insert PivotTable command from the _____ group.

tables

format

layout

chart

: Textbook Chapter 5

0 of 2

Question 9. Question :

(TCO 4) To begin a filter based on a particular field, you drag the desired field to which area under Drag fields between areas below?

Column Labels

Report Filter

Row Labels

Values

: Textbook Chapter 5

0 of 2

Question 10. Question :

(TCO 4) To create a calculated field, select _____ located on the PivotTable Tools Options tab.

Calculations

Insert Calculated Field dialog launcher

Tools

PivotTable Calculation

: Textbook Chapter 5

0 of 2

Question 11. Question :

(TCO 4) Excel applies basic formatting to PivotTables such as _____.

subtotals in italics

primary row labels in bold

primary row labels in italics

subtotals in red

: Textbook Chapter 5

2 of 2

Question 12. Question :

(TCO 4) What are the areas of a PivotTable Report where fields can be placed?

Criteria Range and Extract Range

Values, Axis Fields, Legend Fields, and Report Filter

CORRECT Values, Row Labels, Column Labels, and Report Filter

Database, Field, and Criteria

: Textbook Chapter 5

2 of 2

Question 13. Question :

(TCO 4) After a PivotChart has been created, the PivotTable Settings can still be changed by using the _____.

PivotTable Field List

Chart Layout command

Format Tab

Data Tab

: Textbook Chapter 5

2 of 2

Question 14. Question :

(TCO 4) Click the _____ to sort or filter the chart representation based on the values.

Axis Field arrows

Report Filter arrows

Values arrows

Legend Field arrows

: Textbook Chapter 5

0 of 2

Question 15. Question :

(TCO 4) Which of the following will NOT delete a PivotChart?

Go to the Home tab and click Cut

Right-click the chart and click Cut

I Select the chart and then press Delete

Click and drag the PivotChart off the worksheet

: Textbook Chapter 5

0 of 2

week 4

(TCO 8) To

import a text file into Excel, click ________ on the File tab.
Open

New

Info

Options

Question 2. Question :

(TCO 8) Which of the following is NOT a text file?

Tab-delimited

Comma-separated value

XML

Fixed-width

Question 3. Question :

(TCO 8) Which of the following is NOT a method for refreshing data?

Click the Refresh All command.

Save the Excel workbook.

Click the Refresh All arrow, then click Refresh.

Right-click in a range of data and then select Refresh.

Question 4. Question :

(TCO 8) The Convert Text to Columns Wizard allows you to choose the file type, such as Delimited or ________ width.

Fixed

Mixed

Divided

Text

Question 5. Question :

(TCO 8) To change the text string jOHn dOE to John Doe, use the ________ function.

CONCATENATE

PROPER

UPPER

LOWER

Question 6. Question :

(TCO 8) To change the text string Jack Doe to JACK DOE, use the ________ function.

UPPER

LOWER

PROPER

CONCATENATE

Question 7. Question :

(TCO 8) Which of the following functions does not change the case or capitalization of text?

PROPER

CAPS

UPPER

LOWER

:

Question 8. Question :

(TCO 8) When the data source you select for a mail merge is an Excel workbook that has more than one worksheet, what does Word do?

Automatically uses the data from the first worksheet in the workbook.

Automatically uses the data from the last worksheet in the workbook.

Displays a list of the worksheet names and lets you pick which one to use.

Displays an error message and is unable to continue.

:

Question 9. Question :

(TCO 8) The New Comment command is located on the ________ tab.

View

Review

Data

IN File

Question 10. Question :

(TCO 8) If you have a table with city, state, and zip code merged together in column C and use the Convert Text to Columns Wizard to separate the data into three fields, you need:

empty columns to the right of column C.

empty rows at the bottom of the worksheet.

empty columns to the left of column C.

empty rows at the top of the worksheet.

Instructor Explanation: Textbook Chapter 9

Points Received: 2 of 2

Comments:

Question 11. Question :

(TCO 8) The command to track changes is located on the ________ tab.

File

Data

Review

View

Question 12. Question :

(TCO 8) If you have ________ configured as your e-mail client, you can e-mail an Excel file directly from Excel.

Gmail

Hotmail

Yahoo

Outlook

Question 13. Question :

(TCO 8) The ________ command displays additional document properties such as Company.

Options

Permissions

Show Fewer Properties

Show All Properties

Instructor Explanation: Textbook Chapter 10

Points Received: 2 of 2

Comments:

Question 14. Question :

(TCO 8) All EXCEPT ___________ are Excel functions that help you manipulate text.

PROPER

UPPER

SUBSTITUTE

RESPOND

Instructor Explanation: Textbook Chapter 10

Points Received: 2 of 2

Comments:

Question 15. Question :

(TCO 8) To encrypt a workbook with a password, click Info on the File tab, click ________, and then select Encrypt with Password.

Check for Issues

Protect Workbook

Manage Versions

Options

week 5

(TCO 5) Excel’s multiple worksheet abilities can do all of the following EXCEPT:

create Pivot Charts.

group worksheets together to enter data.

display worksheets side by side.

IN create links among the worksheets.

Comments:

Question 2. Question :

(TCO 5) In Excel, which of the following would you perform to group adjacent worksheets?

Click the first tab, hold Shift, and then click the last tab.

Click the first tab, hold Ctrl, and then click the last tab.

Right-click a worksheet tab, then select Select All Sheets.

Right-click a worksheet tab, then select Group.

Comments:

Question 3. Question :

(TCO 5) If the active cell is in column A, but not in row 1, when you click the Split command, the worksheet will appear in two ________ panes.

horizontal

IN vertical

diagonal

square

Comments:

Question 4. Question :

(TCO 5) In Excel, which of the following is NOT a method for splitting a worksheet?

Drag a split box.

IN Double-click a split box.

Right-click in the active cell and click Split.

Click Split in the Window group on the View tab.

Comments:

Question 5. Question :

(TCO 5) Which of the following external references is for a workbook and worksheet that have no spaces?

“[New.Jersey.xlsx]Qtr4!”B6

‘[New.Jersey.xlsx]Qtr4!’B6

IN [New.Jersey.xlsx]Qtr4!,B6

[NewJersey.xlsx]Qtr4!B6

Comments:

Question 6. Question :

(TCO 5) Excel’s ability to manage worksheets allows you to do all EXCEPT:

validate data in worksheets.

hide worksheets.

arrange windows to view multiple sheets at once.

split a worksheet window to see separate sections at once.

Comments:

Question 7. Question :

(TCO 5) Which of the following is the proper use of a 3D formula?

=SUM(Yr1:Yr5!C7)

IN =SUM(Yr1-Yr5!C7)

=SUM(‘Yr1:Yr5′!C7)

=SUM(“Yr1:Yr5”!C7)

Comments:

Question 8. Question :

(TCO 6) A one-variable data table must have at least ________ blank row(s) and ________ blank column(s) between the dataset and the data table.

two, two

two, one

one, two

one, one

Comments:

Question 9. Question :

(TCO 6) Which What-If Analysis tool would be best at comparing the combined effects of various interest rates and down payments?

IN Scenario Manager

Goal Seek

One-variable data table

Two-variable data table

Comments:

Question 10. Question :

(TCO 6) What is the main difference between Goal Seek and variable data tables?

Goal Seek produces a list of result values.

Goal Seek uses the original worksheet data to change an input.

Goal Seek manipulates multiple variables to produce multiple results.

Goal Seek shows results in a separate table.

Comments:

Question 11. Question :

(TCO 6) The Scenario Manager dialog box enables you to do all the following EXCEPT:

format scenarios.

add scenarios.

IN delete scenarios.

edit scenarios.

Comments:

Question 12. Question :

(TCO 6) Which of the following is NOT in the What-If Analysis command in the Data Tools group on the Data tab?

Scenario Manager

Goal Seek

Data Table

Solver

Comments:

Question 13. Question :

(TCO 6) Which of the following is NOT one of the three parameters in Solver?

IN Constraints

Objective cell

Changing variable cells

Input value

Comments:

Question 14. Question :

(TCO 6) Which of the following is NOT required for Solver to generate a solution?

Constraints

A formula that produces a value to be optimized

Precedent values

IN Variables that change within the specified limitations

Comments:

Question 15. Question :

(TCO 6) The initial results of a Goal Seek are:

automatically entered into the worksheet.

inserted into a new worksheet.

presented visually in a chart.

shown in the Goal Seek Status dialog box.

Comments:

Lab 1 of 7: Saddle River Marching Band (50 Points)

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

You are the assistant to the band director for the Upper Saddle River Marching Band, and you must prepare a report showing the status of the marching band fundraising event for presentation to the board of trustees. The report will summarize all sales of all items and include the total profit-to-date with the amount remaining to reach the profit goal. You will open the partially completed workbook, create formulas, format for presentation to the board, and add charts to graphically depict the sales over time and by product.

Deliverables

After completing the steps below, turn in one Excel 2010 workbook. The workbook should be named Lab1_yourLastName.xlsx. When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity

iLAB STEPS

STEP 1: Open the workbook and add a documentation sheet (5 points)

Back to Top

Begin the lab by locating the spreadsheet, chap1_cap_fundraising.xslx, in Doc Sharing (Lab Materials). Open the workbook in Excel 2010, and rename it Lab1_your lastname.xlsx (Mary Jones would save the file as Lab1_jones.xlsx).

Step-by-Step Guide:Opening a File in the iLab Environment

Insert a new spreadsheet into the workbook. Create a Documentation Sheet. Include your name, the date, and the purpose of the spreadsheet. Your documentation sheet should look something like the one below:

Upper Saddle River Marching Band – Fundraising Event

Creator Your Name

Date Date

Purpose Board of Directors Presentation on Fundraising Sales to date

Need help? Click here for a Step-by-Step Guide: Creating the Documentation Sheet.

How to Use The Lab Videos

Please do not rely solely on the videos to complete this week’s lab. The videos may not exactly match the official lab instructions, and may not show all the steps required.

For full credit, follow the written instructions on the main iLab page and the linked Step-By-Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you, by showing operations that are similar to, but not exactly the same as, those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.

STEP 2: Calculate Values (10 points)

Back to Top

You are to create the formulas needed to calculate the total sales for each item, the profit per item, and the profit based on the number of items sold to date. You will also create a formula to calculate total profit-to-date and the remaining profit needed to make the Marching Band goal for the fundraising campaign.

NOTE: In the data provided, there is no column for sales on 7/6/2006. The band did not do fundraising on that date due to bad weather. You do not need to adjust the dates or add a column for the skipped date; just use the sales data as given. The skipped date will not cause any problems.

In cell C5, enter a formula to calculate profit per item. Profit per item is 50%. Format the cells that hold profit as Currency with two decimal places.

Transcript

This video walks through the current step(s) in this week’s lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2A Instructions.

In column N, enter a formula to calculate the total sales for each item.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2B Instructions.

In column O, calculate the profit per item, which will be the total sales for each item multiplied by the profit per item.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2C Instructions.

In cell D15, calculate Total Profit-to-Date. In cell D16, calculate the remaining profit needed to reach the goal.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2D Instructions.

Create a formula to calculate the percentage of profit contributed by the sale of each item. All of the other calculations were based on “relative references.” For example, in cell O5, you created a formula N5 * C5. When you copied the formula to the next row, Excel automatically changed the reference to cell N6 and C6. In this instance, we want to divide the total profit from each item (O5 through O14) by the total profit overall (cell D15). The reference to the item will change, but the reference to the total profit to date will remain the same. We can use an absolute reference to do this. In cell P5, we will enter =O5/$D$15. The dollar signs tell Excel to always reference column D and row 15. Next week, we will learn more about relative and absolute cell referencing.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 2E Instructions.

STEP 3: Format the Worksheet (15 points)

Back to Top

Now that you have finished the formulas, you must format the worksheet in a professional manner so that it is suitable for presentation to the board of trustees for the college. Remember to focus on readability and reusability.

Insert a comment in cell D16 to explain the formula underlying this cell.

Download the Lab 1, Step 3A Instructions.

Format the spreadsheet as depicted below:

Download the Lab 1, Step 3B Instructions.

STEP 4: Layout the Worksheet (5 points)

Back to Top

Now that you have finished the major formatting, you must lay out the worksheet to further define and separate the areas of the worksheet. This step makes the worksheet easier to read and prepares for printing.

Change the color of the first tab to a shade of brown.

Rename the second tab Fundraising, and change the color to orange.

Create a custom header with your name on the left and your instructor’s name on the right.

Change the page orientation to landscape.

Download the Lab 1, Step 4 Instructions.

STEP 5: Create Charts (15 points)

Back to Top

Create charts based on the data in your worksheet. The charts provide information visually and help you analyze the information.

Create a 3-D pie chart on a separate sheet that displays the percentage of profits for each item. Include descriptive titles and labels and a graphic pointer to the highest profit item. Your chart should look something like this:

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 5A Instructions.

For the next chart, you will need to calculate the total profit per day. Here are the steps:

Copy the daily profit data to a new sheet, and name the sheet Total Sales Data.

In a new section of the worksheet, copy the Items in Column A and the Days.

Then calculate the profit per day for each item

Calculate the total profit per day.

Your data will look something like this:

Create a trend line chart on a separate sheet that shows the total profit by day. Include a descriptive title and labels. Include a callout that indicates the top profit day and label it “Kick Off Event.” Your chart will look something like this:

Make certain that all worksheets in the workbook have meaningful names. Upload the file to the Dropbox. Make sure you post a comment about what you learned when submitting the file.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 1, Step 5 Data Instructions (constructing the data set)

and Lab 1, Step 5B Instructions (creating the line chart).

*Note: The video incorrectly refers to “Sales Per Day”, when it

week 2

La
b 2 of 7: First National Bank New Loan Report (50 Points)
Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

You are an intern at First National Bank working in the loan department, and your boss has asked you to prepare the monthly “New Loan Report” for the Board of Directors. This analysis report will clearly list and summarize all new loans for residential housing in the past month. The summary area includes the loan statistics as labeled data in the data file. The format of the report is appropriate for the Board of Directors for the First National Bank.

Deliverables

Submit one workbook title Lab2_YourName.xlsm to the Dropbox. (Note that files containing macros have the extension, .xlsm rather than .xlsx.) When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

iLAB STEPS

STEP 1: Open, Save, and Document the Workbook (3 points)

Back to Top

Locate the file chap2_cap_housing.xlsx in Lab Materials in Doc Sharing. Open the file in Excel, and save it as Lab2_yourlastname.

Add a documentation sheet that provides a spreadsheet title, author, date, and purpose. Format the documentation sheet. The Bank’s logo is dark blue, so use a color scheme that reflects that logo. Select complementary Font and Fill colors for the documentation sheet. Ensure that the sheet tab is named Documentation and that the documentation sheet is the first sheet in the workbook.

(To get help on creating a documentation sheet, refer to Lab 1, Step 1.)

How to use the lab videos

Please do not rely solely on the videos to complete this week’s lab. The videos may not exactly match the official lab instructions and may not show all the steps required.

For full credit, follow the written instructions on the main iLab page and the linked Step-By-Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you by showing operations that are similar to, but not exactly the same as, those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.

STEP 2: Create Calculations (10 points)

Back to Top

Functions are used to calculate the interest rate, down payment, monthly payment, and average selling price for each residential home in the worksheet. You need to create a formula to determine the down payment. Finish the calculations by using the appropriate functions to complete the Loan Statistics summary area of the worksheet.

Use a VLOOKUP function to determine the interest rates in column D.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2A Instructions.

Calculate the down payment by multiplying the results of a VLOOKUP function by the selling price. Enter the formula in column E.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2B Instructions.

Calculate the amount financed by subtracting the down payment from the selling price. Enter the formula in column F.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

No detailed written instructions are necessary for the Lab 2, Step 2C. The only written instruction needed is already listed after the outline letter C, above the Step 2C box.

Use a PMT function to determine the monthly payments in column G.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2D Instructions.

Use appropriate formulas and functions to calculate over loan statistics for the month:

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Download the Lab 2, Step 2E Instructions.

Number of Loans, Highest Amount Financed, Lowest Amount Financed, Total Amount Financed.

STEP 3: Format the Worksheet (9 points)

Back to Top

Now that you have finished the calculations, you must format the worksheet in a professional manner suitable for delivering to the Board of Directors. Your final worksheet will look something like the worksheet below:

Format all money figures as currency with two decimals and all percentages as percents.

Adjust columns sizes to fit the data.

Merge and Center titles and size appropriately.

Change Font color to dark blue.

Add a small graphic appropriate for the purpose of the worksheet.

Separate sections of the worksheet and provide borders as appropriate.

STEP 4: Create an Excel Application with Macro and Macro Button (28 points)

Back to Top

NOTE: At the end of this step, you will save the file as an xlsm file rather than an xlsx file. This is the filetype that allows you to save a file that includes a Macro.

Add a new worksheet to the workbook, and name it Loan calculator.

Download the Lab 2, Step 4 Instructions.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Create a user input section that asks users to input the following:

What is the price of the home?

Do you wish to put any additional money down?

Pay at the beginning of the month?

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Create an Output section that uses the user-provided data and calculates the Mortgage payments for a 15-year loan, a 20-year loan, and a 30-year loan.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Add user input validation to ensure that users input at least $50,000 for the cost of the home and answer Yes/No to the payment question.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

Unlock the cells in which users will input data so that you may protect the sheet to keep users from changing other areas.

Create a Macro.

Use Insert Shapes to add a Macro button that will allow users to clear values and start over. Name the button “Enter New Values.”

Protect the worksheet, but do NOT require a password.

Format the worksheet so that it is easy to understand and use. The following is a sample of what your final worksheet might look like:

Save your file as an xlsm file and submit to the

week 4

Lab 4 of 7: Fundraiser Letter (50 Points)

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

You work with the XYZ Corporation Charitable Trust allows you to demonstrate your expertise with Excel. The trust is sponsoring an auction, and you have received a list of all donors who have contributed to this auction. The list was pulled from the Corporation’s database as a comma separated text file. You have been asked to create a letter that will go out to each of the contributors that will accept their donation. Tickets to the event will be enclosed. The letter requires that you provide the following pieces of information:

Full Name and Address

First Name

Donated Item

Value

Number of tickets requested

Deliverables

Your will turn in one Excel spreadsheet and two Word documents. You will locate the following files in DocSharing (Lab Materials folder): wk4_trust.txt and wk4_trust_letter.docx. You will submit the following three items: one Excel spreadsheet (Lab4_yourlastname.xlsx), one Word document showing 25 letters (Lab4_yourlastname.docx), and one Word document showing 25 labels (Lab4_yourlastname_labels.docx). When submitting the Excel workbook and the Word documents, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

iLAB STEPS

STEP 1: Import text file into a spreadsheet and clean up data (20 points)

Back to Top

Download the Lab 4, Step 1 Instructions.

Import the text file into Excel and save as lab4_yourlastname.xlsx. Import only the data you will need for your letter.

Separate the first and last names into two columns so that you may use the first name in a Mail Merge. Make sure the fields are named LastName and FirstName.

Check the accuracy of all data by using the Spelling tool in the Proofing group.

Add a documentation sheet that provides a spreadsheet title, author, date, and purpose. Format the documentation sheet by applying a professional color scheme to the text. Ensure that the sheet tab is named Documentation and that the documentation sheet is the first sheet in the workbook.

(To get help on creating a documentation sheet, refer to Lab 1, Step 1.)

How to use the lab videos

Please do not rely solely on the videos to complete this week’s lab. The videos may not exactly match the official lab instructions, and may not show all the steps required.

For full credit, follow the written instructions on the main iLab page and the linked Step-By-Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you, by showing operations that are similar to, but not exactly the same as, those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

STEP 2: Create Merge Document (15 points)

Back to Top

Download the Lab 4, Step 2 Instructions.

Open the wk4_trust_letter document in Word. Make sure that the document is in the same folder as the Excel spreadsheet. This will make it easier to work with the two documents during the Merge process.

Merge information from your Excel spreadsheet into the Word document. Your completed Merge will look like the following when complete:

9/27/2007

XYZ Corporation Charitable Trust

123 Adams Street

Pittsburgh, PA 15697

Shelly Martin

123 North Street

Pittsburgh, PA 15697

Dear Shelly,

Thank you for your generous donation to the XYZ Corporation Charitable Trust Fundraiser. We are delighted to accep the following donation:

Item: Massage

Item Value: $50.00

Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2007. We will be looking forward to seeing you and giving your personal thanks.

Thank you again for you support of the fund. The proceeds will support international annual rescue efforts.

With Special thanks,

Jeoffrey McMillan

Charitable Trust President

XYZ Corporation

Accept all spelling suggestions in the document, and save document as Lab4_yourlastname.docx.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

STEP 3: Create Address Labels (15 points)

Back to Top

Create labels for the letters. The XYZ Corporation wishes to use mailing labels to address the envelopes. They own 8 1/2 x 11″ sheets of label paper with 1″ by 4″ labels, which they purchased from Office Depot.

Download the Lab 4, Step 3 Instructions.

Create a new file for printing labels.

Merge data from your Excel spreadsheet into the new label document.

Submit your completed workbook and the two Word documents (merged letters and merged labels) to the Dropbox. Make sure you post a comment about what you learned when submitting the files.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

week 3

ss Lab 3 of 7: Alice Barr Realty Analysis (50 Points)

Note!

Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Remember This!

Connect to the iLab here.

iLAB OVERVIEW

Scenario/Summary

You are an intern with the Regional Realty Association and are analyzing the claim made by Alice Barr Realty that “we get your price.” You have prepared a spreadsheet that shows data relating to 3 months’ sales by Alice Barr Realty. You are going to determine the percent of asking price for each home sold and analyze the sales data to determine the true track record of the company in selling homes for the asking price.

Deliverables

Turn in one workbook titled lab3_yourlastname to the Dropbox. When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

iLAB STEPS

STEP 1: Open, Save, and Document the Workbook (5 points)

Back to Top

You must open a workbook that lists home sales for 3 months.

Download the file chap4_cap_barr from DocSharing, Lab Materials.

Open the workbook in Excel, and save it as Lab3_Yourlastname.xlsx.

Insert a documentation sheet that includes title, author, date, and purpose. Format it appropriately, including changing the Sheet Name to Documentation. At the end of this activity, you will be asked to provide a conclusion as to whether this realty company “really gets your price.”

How to use the lab videos

Please do not rely solely on the videos to complete this week’s lab. The videos may not exactly match the official lab instructions, and may not show all the steps required.

For full credit, follow the written instructions on the main iLab page and the linked Step by Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you, by showing operations that are similar to but not exactly the same as those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions.

If you have any questions, please post in the Q & A Forum or contact your instructor.

STEP 2: Add Calculations and Format Sales Data Worksheet (10 points)

Back to Top

A formula is used to calculate the percent of asking price, and this formula is applied to all listed sales. You will format the list in an attractive and useful manner.

Calculate the percent of asking price for each sale (selling price divided by asking price). Enter the formula in Column F. Format Column F as a percentage with one decimal.

Format Columns D and E as currency, with no decimals. Format Columns G and H as dates so just the day and month (for example, 5-May) are visible. You will need to use the Number Format dropdown list, select More Number Formats, and select the Date category.

Widen columns to make all data and headings visible. Format titles as appropriate. Bold and center the column headings.

Your Sales Data worksheet will look something like this:

Transcript

This video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

STEP 3: Sort Data and Create Summary Report and Chart (10 points)

Back to Top

Before analyzing data, it is a good idea to copy the data to a new worksheet so that you have a back-up copy of the original data. Right click on the Sales Data sheet tab and select Move or Copy. Create a copy of the current spreadsheet and move it to the end. Name the sheet Sorted Data.

Download the Lab 3, Step 3 Instructions.

Transcript

This video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides.

** Please be patient when loading these videos as they can be large.

In order to sort the data, you must convert them to a data table. Format the data table to look attractive.

Sort the data by selling agent (A to Z) and then by asking price (largest to smallest).

Convert the sorted data back into a range, and create a summary report showing the total asking price and total selling price by selling agent.

Transcript

This video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

Create a column chart that shows the difference by agent between asking price and selling price.

STEP 4: Filter the Data (5 points)

Back to Top

Download the Lab 3, Step 4 Instructions.

Create another copy of the original sales data. Rename the sheet Filtered Data.

Convert the range to a table, and then filter to show only those properties sold in Miami by agent Carey.

Transcript

This video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

STEP 5: Apply Conditional Formatting (5 points)

Back to Top

You determine that one measure of how well the company “gets your price” is to set a scale. Excellent performance is at or above the asking price. Good performance is between 95% and 100%. Below 95% is average performance. You will create conditional formatting rules to display graphically the performance level for each sale. Make a copy of the original sales data chart. Name the new sheet Conditional Format.

Download the Lab 3, Step 5 Instructions.

Select the data range and insert a table.

Use Advanced Formatting to set up the following conditional formatting rules for the field Percent of Asking Price.

Greater than or equal to 100% = GREEN

95% to 99.9% = YELLOW

Less than 95% = RED

Use Percent of Asking Price as the Sort Key, and sort by color with green first, then yellow, and then red.

Transcript

This video walks through the current steps in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step by Step guides.

** Please be patient when loading these videos because they can be large.

STEP 6: Create a Pivot Table (10 points)

Back to Top

Now that you have sorted, subtotaled, filtered, and conditionally formatted the data, you have a few more questions to answer. You want to see average percentage of selling price by agent and by city. This will allow you to see whether there are any patterns based on the city or based on the salesperson. To do this kind of comparison, you decide to create a pivot table.

Download the Lab 3, Step 6 Instructions.

Open the Sales Data worksheet. Select the Sales Data range (A3:H33). From the Formulas tab, select Define Name from the Defined Names group. Name the range of cells SalesData.

Create a pivot table based on the named range, SalesData. Create the pivot table in a new worksheet.

Select Selling Agent as your Row Labels and City as your Column Labels. Select Percent of Sales as your Values. Change the S

Order your essay today and save 30% with the discount code: ESSAYHELPOrder Now