BIS 155 Advanced PC Applications
with iLABs Complete Course (Devry)
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 todays job market
Week 1 Quiz
(TCO 1)Which of the following describes the function of the Formula Bar (as it applies to Excel 2010)? (Points : 2)
(TCO 1) Which of the following best describes a cell address? (Points : 2)
(TCO 1) If you wanted to insert the current date into an active cell, which of the following would you use? (Points : 2)
(TCO 1) In Excel a Formula is _______. (Points : 2)
(TCO 1) Which of the following describes the Auto Fill in Excel 2010? (Points : 2)
(TCO 1) Ribbon Commands with arrows indicate_____. (Points : 2)
(TCO 1) The three types of data that can be entered in a cell in an Excel worksheet are __________. (Points : 2)
(TCO1) In Excel, a border ________. (Points : 2)
(TCO 3) A data series is a group of related ____________. (Points : 2)
(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. (Points : 2)
(TCO 3) When creating a chart in Excel, a single data series _____. (Points : 2)
(TCO 3) To display similar data in a single column, with each series of data distinguished by a different color, use a: (Points : 2)
(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: (Points : 2)
(TCO 3) Which of the following best describes the X Y (scatter) chart? (Points : 2)
(TCO 3) Which of the following best describes a trendline? (Points : 2)
Week 1 iLABS
BIS 155 Week 1 iLab (Upper Saddle River Marching Band)
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 2 Quiz
(TCO 2) An absolute cell reference: (Points : 2)
(TCO 2) Which of the following best describes a circular reference? (Points: 2)
(TCO 2) =$E2-G$4 contains examples of _____ cell references. (Points: 2)
(TCO 2) In Excel, an Argument would be best described by which of the following statements? (Points: 2)
(TCO 2) The MEDIAN function would identify the ____. (Points: 2)
(TCO 2) The MAX function identifies the _____. (Points: 2)
(TCO 2) The NOW function would perform which of the following? (Points:2)
(TCO 2) The _____ number is the number of the column in the lookup table that contains the return values. (Points : 2)
(TCO 2) Arithmetic operations, cell references, and ____can be used in Excel formulas. (Points : 2)
(TCO 2) To simplify entering ranges in formulas, you can use _____. (Points : 2)
(TCO 7) A _____ helps to ensure consistency and standardization for similar workbooks, such as detailed income statements for all 12 months in a year. (Points : 2)
(TCO 2) When calculating a PMT function, the PV is best described by which of the following? (Points : 2)
(TCO 7) Before protecting a worksheet, you should __ the cells that you want users to be able to edit. (Points : 2)
(TCO 7) Which of the following file formats can store macros? (Points : 2)
(TCO 7) By default, Excel automatically _____ macros and displays a security warning when a file containing macros is opened. (Points : 2)
Week 2 iLABS
BIS 155 Week 2 iLab (Excel s Advanced Formulas & Functions)
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 3 Quiz
(TCO 4) If there are certain columns of information that are not needed at a particular time but might be needed later,_ the unneeded columns to allow users to focus on only the essential information. (Points:2)
(TCO 4) In Excel, the print area is defined as _____. (Points : 2)
(TCO 4) When sorting in Excel, it arranges records in a table _____. (Points : 2)
(TCO 4) Filtering the Cost column (field) to show only records greater than $10,000 is……__. (Points:2)
(TCO 4) To create a new Conditional Formatting Rule, _____. (Points : 2)
(TCO 4)In addition to sorting and filtering cells by content, you can sort and/or filter by _____. (Points : 2)
(TCO 4)When data is grouped, the margin area displays the _____. (Points : 2)
(TCO 4) A _____ allows you to identify relationships between variables in your data. (Points : 2)
(TCO 4) Data mining techniques such as PivotTables can detect _____ of data. (Points : 2)
(TCO 4) A faster way to expand all categories at one time is to click _____. (Points : 2)
(TCO 4) Data displayed in the PivotTable will be updated _____. (Points : 2)
(TCO 4) A quick way to display the Go to dialog box is to press the _____ key(s). (Points : 2)
(TCO 4) After a PivotChart has been created, the PivotTable Settings can still be changed….___.(Points:2)
(TCO 4) To build a PivotTable and PivotChart at the same time, click the PivotTable arrow……__. (Points:2)
(TCO 4) Which of the following will NOT delete a PivotChart? (Points : 2)
Week 3 iLABS
BIS 155 Week 3 iLab (Alice Barr Realty Analysis)
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 4 Quiz
(TCO 8)Which of the following is one of the most common file types imported into Excel? (Points : 2)
(TCO 8)Which of the following is NOT a text file? (Points : 2)
(TCO 8)Which of the following is NOT a method for refreshing data? (Points : 2)
(TCO 8) The Convert Text to Columns Wizard allows you to choose the file type, such as Delimited____(Points:2)
(TCO 8) Which of the following is a frequently used function to manipulate txt? (Points : 2)
(TCO 8)To change the text string Jack Doe to JACK DOE, use the ________ function. (Points : 2)
(TCO 8)Which of the following cannot be used in a CONCATENATE function? (Points : 2)
(TCO 8) If you want to send a letter to all your customers using Mail Merge, and you already have the customers’ names and addresses stored in an Excel file, you should choose _________ from the Select……. (Points : 2)
(TCO 8)The New Comment command is located on the ________ tab. (Points : 2)
(TCO 8) An alternative method for inserting a comment in a cell includes: (Points : 2)
(TCO 8)The command to track changes is located on the ________ tab. (Points : 2)
(TCO 8) The command to send an Excel workbook by e-mail is located on the ________ tab. (Points:2)
(TCO 8) Windows can search for files by author name or keywords that have been entered into_(Points:2)
(TCO 8) Which function is similar to the Find and Replace feature? (Points : 2)
(TCO 8) To encrypt a workbook with a password, click Info on the File tab, click _____…… (Points : 2)
Week 4 iLABS
BIS 155 Week 4 iLab (Create Address Labels 1)
BIS 155 Week 4 iLab (Create Address Labels 2)
BIS 155 Week 4 iLab (Create Address Labels 3)
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 5 Quiz
(TCO 5) When multiple worksheets are selected, the word ________ appears in the title bar. (Points : 2)
(TCO 5) In Excel, which of the following would you perform to group adjacent worksheets? (Points : 2)
(TCO 5) When two windows of the same workbook are opened, Excel adds ________ to the…. (Points:2)
(TCO 5) After saving the arrangement of windows, a workbook can be opened by opening __file. (Points:2)
(TCO 5) Which of the following functions do NOT work with 3D formulas? (Points : 2)
(TCO 5) Excel’s ability to manage worksheets allows you to do all EXCEPT: (Points : 2)
(TCO 5) If A1 contains the hourly pay rate of $10.25, A2 contains the hours worked (40), and A3 contains =A1*A2, which cell is the dependent cell(s)? (Points : 2)
(TCO 6) In a what-if analysis of a car purchase, the original interest rates of 6% and 5%, 5.5% and 6.5% are the ________ values. (Points : 2)
(TCO 6) Which What-If Analysis tool would be best at comparing the combined effects of…..? (Points : 2)
(TCO 6) After entering the substitution values and the reference to a formula, the next step to complete a two-variable data table and view the results is to: (Points : 2)
(TCO 6) The Goal Seek command is located in the ________ command on the Data tab. (Points : 2)
(TCO 6) Which of the following is NOT in the What-If Analysis command in the Data Tools…..? (Points:2)
(TCO 6) Which of the following is NOT one of the three parameters in Solver? (Points : 2)
(TCO 6) Which of the following is NOT required for Solver to generate a solution? (Points : 2)
(TCO 6) The initial results of a Goal Seek are: (Points : 2)
Week 5 iLABS
BIS 155 Week 5 iLab (Bruno s Pizza Analysis)
Week 6
Bringing It All Together (Graded)
This week will be an opportunityto share ideas and approaches to the Excel course project. What are some spread sheet features you will use to make your spreadsheet professional, readable, reliable, andrepeatable? Which spreadsheet functions and data analysis types do you think will be the most challenging to implementin this project? What questions do you have about those functions and types of data analysis
Week 6 Course Project
BIS 155 Week 6 Course Project; Excel Project
BIS 155 Week 6 Course Project; ProjectMemoTemplate
Week 6 I LABS
BIS 155 Week 6 iLab (Day Care Center)
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?
Week 7 iLab
BIS 155 Week 7 iLab (Access Database)
Week 8
Looking Ahead (Not – Graded)
Class,looking back over the Terminal Cours eObjectives for this course, what are you looking forward to learning more about throughout your education and career?
Week 8 Final Exam
(TCO 1) You work for a local construction firm, “DeVry Engineering Group” and your supervisor wants to test your knowledge and skills with Microsoft Excel and has instructed you to develop a spreadsheet to calculate weekly payroll for 15 employees with the following assumptions:……. (Points : 40)
(TCO 3) You currently work for an automotive parts supply store. Your company is growing and is considering expansion. The company currently has three locations (North, South, and Central) in one state. Each parts supply store carries inventory in four categories. You have been presented with the sales figures for the last three years for each location and inventory category by store. Based on this information, you’re tasked with analyzing current sales for each store by category and overall total sales by store and category
.. (Points : 40)
(TCO 4) You are given a spreadsheet with daily sales numbers ordered by date from January 1st to December 31st. You have been tasked with finding the average sales of each month, then to reorder the months so they are listed in order from highest to lowest average sales. Give a step-by-step explanation of how you will rearrange the data so you can analyze the best and worst months
.. (Points : 40)
(TCO 5) You’ve just joined the staff of the XYZ Manufacturing Company (XYZ, for short). XYZ manufactures only one product, the gizmo. It comes in two sizes, the mini-gizmo and the magna-gizmo. Both are difficult to manufacture, and consequently, the company closely monitors rejected units. The company has three locations, each of which produces both the mini and the magna-gizmos. You are automating the weekly production reports so that you can easily calculate total production for the entire company each week. The mini-gizmo is priced at $3.25 per unit. The magna-gizmo is priced at $7.00 per unit. The unit cost for a reject mini-gizmo is $1.75. The cost for a reject magna-gizmo is $3.50. Respond fully to the following questions regarding this task:
(Points : 40)
5. (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
(Points : 40)