# BIS 155 Entire Course Advanced PC Applications /New

• First Name
• Donated Item
• Value
• Number of tickets requested
• Sort by discount level. Have discounts increased sales volumes? Is there any discount level that appears to be more effective than others?
• Graph sales over time to see trends. Are there any peaks and lows in sales? Is there any time of year in which sales are highest? Lowest?
• Pivot the data to see total sales by quarter, country, category, and salesperson. Are there any highs? Are there any lows that need to be addressed?
• Subtotal the data. How are quarterly sales totals? Sales totals by salesperson? By Country?
• Challenge Option: Perform What-If Analysis. What if prices were raised by a certain percentage with a slight decline in sales? What combination of price increases and decline in sales makes most sense?
(TCO 3) Which of the following best describes a column chart? (Points : 2)Displays data comparisons vertically in columnsA horizontal line that borders the plot area to provide a frame of reference for measurementContains graphical representation of values in data seriesContains the entire chart and all of its elements(TCO 3) When creating a chart in Excel, a single data series _____. (Points : 2)Compares values for one set of dataGroups or clusters similar data in columns to compare values across categoriesIs a key that identifies the color, gradient, picture, texture, or pattern fill assigned to each data series in a chartCompares two or more sets of data in one chart(TCO 3) When creating a chart in Excel, a multiple data series _______. (Points : 2)Groups or clusters similar data in columns to compare values across categoriesCompares two or more sets of data in one chartIs a key that identifies the color, gradient, picture, texture, or pattern fill assigned to each data series in a chartPlaces stacks of data in segments on top of each other in one column, with each category in the data series represented by a different color(TCO 3) A pie chart with one or more slices separated for emphasis is called a(n) ____________ pie chart. (Points : 2)ExpandedExplodedDisplacedClustered(TCO 3) When you select a chart, Excel displays a Chart Tools contextual tab with three specific tabs: (Points : 2)Design, Layout, and FormatHome, Insert, and Page LayoutChart, Type, and FormulasData, Review, and Data(TCO 3) Which of the following best describes a sparkline? (Points : 2)A label that describes either the category axis or the value axisA miniature chart contained in a single cellThe value or name of a data pointA label that describes the chart(TCO 3) Groups of related numeric values in an Excel worksheet are:Data pointData rolesCategoriesData series(TCO 3) Which of the following best describes the chart area?Contains graphical representation of values in data seriesContains the entire chart and all of its elementsA vertical line that borders the plot area to provide a frame of reference for measurementA horizontal line that borders the plot area to provide a frame of reference for measurement(TCO 3) To display similar data in a single column, with each series of data distinguished by a different color, use a:Stacked column chartPie chart3-D column chartBox diagram(TCO 3) In Excel, an area chart _______Shows the high, low, and close prices for individual stocks over timeTrends using two dimensions on a continuous curveEmphasizes the magnitude of changes over time by filling in the space between lines with a colorShows a relationship between two variables(TCO 3) After creating a chart, you can change the chart type by using _____.Page Layout tab / Chart Options buttonDesign tab / Change Chart Type buttonData tab / Chart Changer toolYou cannot change the chartBIS 155 Quiz Week 2 Data Analysis with Spreadsheets with Lab DeVry (TCO 2) In Excel, a relative cell reference: (Points : 2)Indicates a cell’s specific location and the reference does not change when you copy the formula.
Contains both an absolute and a relative cell referenceIndicates a cell’s relative location from the cell containing the formula; the reference changes when you copy the formulaOccurs when a formula directly or indirectly refers to the cell containing the formula(TCO 2) In Excel, an Argument would be best described by which of the following statements? (Points : 2)Calculates the total of values contained in two or more cells.Displays a list of functions and defined names as you enter a function.Is an input, such as a cell reference or a value needed to complete a function.Is a small pop-up description that displays the results of the cell.(TCO 2) The MIN function would identify the _____. (Points : 2)The highest value in a range.Tallies the number of blank cells in a range.The lowest value in a range.The midpoint value in a range(TCO 2) The NOW function would perform which of the following? (Points : 2)FINDSEARCHHLOOKUPVLOOKUP(TCO 2) To copy a formula, you may use the _____, which is found in the bottom right-hand corner of an active cell. (Points : 2)The periodic interest rate, such as a monthly interest rate.Calculates the periodic payment for a loan with a fixed interest rate and fixed term.Looks up a value in a lookup table where the first column contains the values to compare with the lookup value.Looks up a value in a lookup table in which the first row contains the values to compare with the lookup value.(TCO 2) One benefit of using range names in formulas is that _____ . (Points : 2)A cell referenceA functionA formulaA range name(TCO 2) The PMT function is best described by which of the following? (Points : 2)It is a set of range names.It is a word or a string of characters that represent one or more cells.It is the present value of the loan.It is why you do not have to make the cell reference absolute in the formula.(TCO 2) Which of the following best describes a mixed cell reference? (Points : 2)Occurs when a formula directly or indirectly refers to the cell containing the formulaCauses a potential errorContains absolute or relative cell references, but not bothContains both an absolute and a relative cell reference(TCO 2) You have a cell that contains the formula =A1*2. When you copy this formula to other cells, you want it to always reference column A, but you want the row number to change automatically. You should make the A1 in this formula a(n) _____ cell reference. (Points : 2)AbstractRelativeMixedAbsolute(TCO 2) Which of the following describes the SUM function? (Points : 2)Identifies the midpoint value in a set of valuesCalculates the total of values contained in two or more cellsCalculates the arithmetic mean or average of values in a rangeDisplays the lowest value in a range(TCO 2) The _____ function returns a result based on a condition. (Points : 2)MaxTodayFindIf(TCO 2) Which of the following best describes the COUNT function? (Points : 2)Tallies the number of cells in a range that are not emptyTallies the number of blank cells in a rangeDisplays the current dateTallies the number of cells in a range that contain values(TCO 2) The NOW function would perform which of the following? (Points : 2)Contains another function embedded inside one or more of its argumentsEvaluates a condition and returns one value if the condition is true and a different value if the condition is falseEvaluates true or falseDisplays the current date and time(TCO 2) The _____ number is the number of the column in the lookup table that contains the return values. (Points : 2)Lookup value
Column indexLookup tableRandom
(TCO 2) To copy a formula, you may use the _____, which is found in the bottom right-hand corner of an active cell. (Points : 2)Sizing handlePointerFill handleInsertion point(TCO 2) One benefit of using range names in formulas is that _____ . (Points : 2)It directly or indirectly refers to the cell containing the formulaIt contains both an absolute and a relative cell referenceIt identifies the present value of the loanIf you copy the formula, you do not have to make the cell reference absolute(TCO 2) The PMT function is best described by which of the following? (Points : 2)It is the periodic interest rate, such as a monthly interest rateIt is the total number of payment periodsIt is the present value of a loanIt calculates the periodic payment for a loan with a fixed interest rate and a fixed term(TCO 2) In Excel, a function can be defined as a _____. (Points : 2)Predefined formula that performs a calculationCell reference or a valueList of values and defined names as you enter data into a spreadsheetSet of rules that govern the structure and components for a formula(TCO 2) The rules for constructing a function can be referred to as the function’s _____.SynthesisSynergysynchronizationsyntax(TCO 2) In the arguments to an IF function, the condition that is evaluated to determine if it is true or false is also sometimes called a _____.Logical testLookup valueBreakpointTable array action(TCO 2) If you enter an = and the first few letters of a function in the formula bar, Excel will display a drop-down list of functions beginning with those letters. This feature is called _____.Syntax HelperFormula AutoCompleteFormula WizardFunction Finder(TCO 2) Which of the following best describes the HLOOKUP function?The periodic interest rate, such as a monthly interest rateCalculates the periodic payment for a loan with a fixed interest rate and fixed termLooks up a value in a lookup table where the first column contains the values to compare with the lookup valueLooks up a value in a lookup table in which the first row contains the values to compare with the lookup value(TCO 7) Which of the following is NOT an Excel tool used to improve productivity in developing consistently formatted workbooks? (Points : 2)MacrosTemplatesThemesBackground(TCO 7) Before protecting a worksheet, you should _____ the cells that you want users to be able to edit. (Points : 2)FormatHideLockUnlock(TCO 7) A _____ is a partially completed workbook that you can use to create other workbooks that have the same structure and purpose. (Points : 2)ThemeStyleTemplateMacro(TCO 7) To unlock cells, click the _____ tab, then click Format in the Cells group and select Lock Cell. (Points : 2)Homepage layoutdataview(TCO 7) By default, a macro records cells as _____ references if they are selected while recording the macro. (Points : 2)AbsoluteRelativeFlexibleMixed(TCO 7) By default, Excel automatically _____ macros and displays a security warning when a file containing macros is opened. (Points : 2)SavesDeletesRunsDisables(TCO 7) Which of the following file formats can store macros?XlsmCSVXML
the use of a table element as a formulaA Number Filter(TCO 4) To apply a red background color to cells for employees who have sales greater than \$1,000, you can use _____. (Points : 2)A PivotTableA multiple level sortConditional formattingA range name(TCO 4) Using Conditional Formatting to draw attention to cells that are blank _____. (Points : 2)Displays a particular color based on the relative value of the cell contents to other selected cellsDisplays an icon representing a value in the top third, quarter, or fifth based on values in the selected rangeHelps locate where data may be missingHelps locate errors in cells quickly(TCO 4) For Subtotals to be useful and accurate, it is important that the data be _____ correctly. (Points : 2)sorted
formattedalignedlabeled(TCO 4) To add a PivotTable, choose the Insert PivotTable command from the _____ group. (Points : 2)TablesFormatLayoutChart(TCO 4) The Insert Calculated Field dialog box requires you do all the following EXCEPT _____. (Points : 2)Enter formulas using field names instead of cell references
Enter a description column heading for the calculated fieldBuild formulas using mathematical operands such as +, -, and *Enter formulas using cell references(TCO 4) Data displayed in the PivotTable will be updated _____. (Points : 2)When you right-click a cell in the PivotTable and select RefreshAutomatically as soon as the source data changesWhen you select the worksheet containing the PivotTableWhen you create a PivotChart(TCO 4) A quick way to display the Go to dialog box is to press the _____ key(s). (Points : 2)F4Ctrl+HomeCtrl+End
F5(TCO 4) To go to the top of your spreadsheet quickly, use the _____ key(s). (Points : 2)Ctrl+EndArrowCtrl+HomeEsc(TCO 4) Click the _____ to sort or filter the chart representation based on the values. (Points : 2)Axis Field arrowsReport Filter arrowsValues arrowsLegend Field arrows(TCO 4) Pivot Charts look best when they use basic charts, such as a _____. (Points : 2)Column chartScatter chartRadar chartSurface chart(TCO 4) Which of the following best describes a page break?Freezes only the top rowThe sequence in which pages printIndicates where data starts on a new printed pageStops the printer from printing(TCO 4) The Subtotal command is located on the _____ tab.DataFormulaPage layoutInsert(TCO 4) _____ are created to organize and summarize data in PivotTables.CategoriesTypesTitlesLevels(TCO 4) To create a calculated field, select _____ located on the PivotTable Tools Options tab.CalculationsInsert Calculated Field dialog launcherToolsPivotTable Calculation(TCO 4) A Pivot Table Style controls all the following EXCEPT _____.BoldingFont colorsNumber formatShading colors(TCO 4) The PivotChart Tools contextual tab includes all the following EXCEPT _____.DesignLayoutFormatType(TCO 4) Which of the following will NOT delete a PivotChart?Go to the Home tab and click CutRight-click the chart and click CutSelect the chart and then press DeleteClick and drag the PivotChart off the worksheetBIS 155 Quiz Week 4 Data Analysis with Spreadsheets with Lab  (TCO 8) In a tab-delimited file, ________ separate the data. (Points : 2)CommasTabs
SpacesHyphens(TCO 8) The ________ group on the Data tab contains tools to help you manage your external data connections. (Points : 2)Get External DataData ToolsAnalysisConnections(TCO 8) Which of the following is an example of a text string? (Points : 2)=B5*B7=SUM(A5:A10)=IF(B3B4, B3)John Doe
• 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….
Part 2: You must also calculate the Income Tax which is assessed at an accelerated rate as shown in the Tax Rate Table. Write a formula as if it were in Cell F2 that calculates the income tax for the first employee. Write the formula…
Part 3: Hourly employees must pay union dues of \$0.25 per hour on hours worked in excess of 25 hours up to and including 40 hours plus \$0.35 per hour on all hours worked in excess of 40 hours. Write a formula as if it were in Cell G2 that calculates the union dues (if any) for the first employee. Write the formula…
Part 4: Salaried and Exempt employees (Pay Codes E and S) are covered by the company’s health insurance. The deduction is \$10.00 per week. Write a formula as if it were in Cell H2 that calculates the Insurance deduction (if any) for the first employee. Write the formula so that it may be copied to the other employees without modification. (Points : 40)(TCO 3) You are a Microsoft Office trainer for DeVry University. The training topic for the current month is charts using Microsoft Excel. During each training session from different departments or cohorts, the following frequently asked questions were asked and you decide to create a document formalizing answers and suggestions.
1. My supervisor, Sally, likes my charts; however, she instructs me to take more time planning my charts before creating the actual chart itself. Why is planning the creation and development of a chart so important?
2. My supervisor, John, ask me to chart some comparative data but my first attempt with a column chart did not illustrate the data as well as he would have liked. What type of chart is ideal and best to illustrate comparative data of individual items in a data series and why?
3. My supervisor, Jessica, asked me to project future data trends using a chart. Most of the data is associated with sales and depending on the nature of what is sold, data either fluctuates or increases quickly. What chart feature added to a column chart can help project these data trends and why?
4. My supervisor, Thomas, asked me to add some creativity with color and enhancements on all charts used during weekly meetings. What should I consider to avoid being overly creative and why is this important? (Points : 40)
(TCO 5) You work for a local construction firm “DeVry Engineering Group” and your supervisor, Jessica, needs an updated earned value analysis index of the prior month for an existing capital project (0000001) that has a current Budget at Completion (BAC) of \$1500.00, a current earned value of (EV) of \$300, a current actual cost (AC) of \$500.00, and a current planned value (PV) of \$350.00. In order to calculate the current average index, assume the following variables and standard formulas below:Cost Variance(CV)EV–ACCost Performance Index(CPI)EV/ACSchedule Variance(SV)EV–PVSchedule Performance Index(SPI)EV/PVEstimate to Completion(ETC)EAC–ACEstimate at Completion(EAC)BAC/CPIVariance at Completion(VAC)BAC–EACStatus (Earned Value Index)(EVI)(CPI+SPI)/2Also assume that a final calculated average index of 1.00 or greater represents a project being on track and in good standing. In addition, there could be several options for the descriptive methods to design this worksheet and below is a worksheet setup with the following “3” columns including any other needed top labels like for the existing Month already calculated.
1. Based on the current calculated earned Status (Earned Value Index) for work order 0000001, is the current capital project in good standing?
2. Your supervisor, Jessica, wants the worksheet, on row 16 or 17, to automatically provide a project status as being “In the Red” (Poor Standing) or “In the Black” (Good Standing). Explain descriptively how you could use a conditional formula to report the project status.
3. Your Supervisor, Jessica, has 3 additional projects with work orders, 0000002, 0000003, and 0000004 she wants added to this Excel Workbook where each work order is monitored on a separate worksheet like work order 0000001. Jessica would like to have a summary worksheet which compiles all information for all work orders. The overall design of the summary worksheet will match the same general design as with work orders 0000001 through 0000004. Descriptively describe steps needed to link these worksheets together along with any ideas for formulas and functions to give an overall picture of all project work orders combined so that Jessica can see an overall holistic view of the Status (Earned Value Index) across all these work orders being managed.
4. Based on the summary worksheet, Jessica, would like to see a chart which can help with future projections of the Status (Earned Value Index). Descriptively describe which chart you would recommend with any features that will help Jessica with future projections. Also using your intuition and creative mind, descriptively describe if any other information needs to be charted from the summary worksheet which may help Jessica with future cost projections and why? (Points : 40)
(TCO 5) A table named “Daily Sales” in a Microsoft Access database has the following fields:Product A
Product B
Product C
Product DEach record on the table represents a day of total sales for each product and the data covers the last five years. Your boss asks you to create a visual report of the sales performance of each product during the last 12 months.Prepare a step-by-step description of how you would use Microsoft Excel to prepare the report and email it to your boss following these steps:Step 1 – Import data from Access to Excel
Step 2 – Summarize Monthly Data for each product during the last 12 months
Step 3 – Create a visual representation of monthly data
Step 4 – Email the spreadsheet with your comments to your boss (Points : 40)(TCO 9) You have been tasked with analyzing an extremely large amount of data and to ultimately produce a report to share with the Board of Directors. The data is currently in a text file and has over two thousand records of data.  Explain how you would use Excel to analyze this data and organize it to prepare a written report. Be very specific on the variety of tools you would use and the steps you would go through to analyze the data and to ultimately prepare a detailed report with recommendations.