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

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)

(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:

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

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

week 3