BIS155 Final Exam Study Guide The Final Exam has a total of five essay questions. The focus of the exam is to evaluate your ability to select and apply appropriate tools/concepts based on descriptions of common business situations. TopicQuestion TypeNumber of QuestionsPoints Possible TCO 1 through 9: ExcelEssay4160 TCO 10: Database ConceptsEssay140 TOTAL5200 POINTS TCO 1 – 2: Spreadsheet Development 1. Basic steps/tasks involved in spreadsheet development. 2. Appropriate cell references required in various situations (relative, mixed, or absolute). . Appropriate formatting for titles, column headings. 4. When to use ranges and how to meaningfully name cell ranges. 5. Simple formula creation using constants and/or cell references 6. When and how to use the functions covered in this class, including but not limited to PMT, VLOOKUP, HLOOKUP, MIN, MAX, SUM, IF, COUNT, COUNTIF, AVERAGE, and MEAN. 7. How to add comments to a worksheet. 8. Various options for print documentation of spreadsheet values and formulas, including page headers and footers. TCO 3, 4, and 9: Data Manipulation and Analysis 1.
Types of files that can be imported to Excel, and the import operation. 2. Tools available to “clean” data and prepare it for analysis; how and when to use each. 3. How to effectively plan and set up a spreadsheet “list” to store company records, and to facilitate efficient data entry and retrieval. 4. Select and apply appropriate sorts and filters based on a business information need. 5. Application of conditional formatting to highlight based on criteria. 6. Analysis of data via use of group subtotals, various “What-If” tools, and pivot tables/charts. 7.
Use of appropriate charts (refer to Chapter 3) for communication of findings, and how to format for readability. TCO 5 -6: Data Consolidation and Decision Making 1. Under what conditions and how to accomplish consolidation of similar data for summary information. 2. Creation and use of spreadsheet templates. 3. Benefits of and when to use 3-D references. 4. Tools available for “What-If” analysis and under what situations each would be used. Includes: one-variable data tables, two-variable data tables, goal seek, scenario manager, and solver. TOC 7 – 8: Automation and Integration 1.
Simple automation—Tasks accomplished by Autofill, and copying using the fill handle. 2. Means for assuring data integrity including drop down lists, data validation, and worksheet protection. 3. Identify tasks for automation, and recommend macro development. 4. Develop and apply appropriate data validation rules and dialog boxes. 5. Using either an Excel list or a list imported to Excel, generate form letters and/or mailing labels. 6. Formatting the data source and the merged document. 7. Differences in linking files and embedding files, how to accomplish each, and when to use each. 8.
How to link or embed an Excel chart into a Word document or Power Point slide. 9. Given a case such as the Week 6 project requiring analysis, develop a report to present findings and recommendations. TCO 10: Overview of Microsoft Access 1. Compare and contrast Excel and Access focusing on common capabilities, unique capabilities, and when to use each. 2. Appropriate data types for specific data in the database. 3. The purpose of a primary key, and the appropriate selection and assignment of a field as primary key. 4. The function of an Access form, and how to create a form using the Wizard. 5.
Various types of queries, and how to set up basic queries using the Wizard or the query grid. 6. The function of an Access report, and how to create a report using the Wizard including proper formatting, and use of section, page, and report headers and footer. SAMPLE QUESTION: Essay. The Essay questions will present you with a situation in which you need to demonstrate a broad understanding of Access and Excel concepts. Example: You work for a Video store with a database of all the films owned by the store as well as rental information that shows all check-out information for all of the films and customers.
Part A: Describe the purpose of a report you might provide the owner of the store. Part B: Explain the data required to generate this report, and how the data would be prepared Part C: Describe the organization of information. Part D: Explain formatting for the report. Part E: Describe visual presentation of the information (charts). Model Answer: A. The owner of the store is most interested in the overall income from rental of films. In order to help her make decisions about how much stock to carry, I would create a report that shows rental history by category by month.
This would allow her to see peak periods for rentals and categories most in demand throughout the year. B. To generate this report, records of all rentals for a period of at least one year will be required. The records will include the rental dates, the category of video rented in each rental. C. I would use a sorting level by month and also by film category, so that the owner could see the total sales per month and also the types of films that are most popular. The report would include the aggregate value of sales in each category by month.
The Detail section would include Category and Total sales. I would also want to include Total Sales by month in the Group Footer for the month. The report would also have the date it was run in the Page Footer section so that the date would appear on each page of the report. I would add a report Footer to show the overall sales year-to-date. D. The report would have a Report Header with the company logo and a Report Title, Sales by Month and Category. All Titles on the report will be bold and centered using the merge and center tool. Font color will match the company logo.
Column headings will also be centered and bold, and aggregate totals will be bold. I might want to include Conditional Formatting to highlight categories in which there were no rentals in any month. E. The information will be captured in a line chart with one line for each category, and with data series for the total rentals in each month. The chart will be created on a separate chart sheet in Excel. Study Tips: (1)Start your study efforts in advance. Check the Syllabus and any announcements from your instructor for the Final Exam schedule.
Plan your Final Exam time accordingly. (2)Review all course reading assignments to include all assigned textbook chapters, lectures, and online material. (3)Review all course software assignments, as well as review the assignment solution file if you had any remaining questions after completion of your assignment. (4)Ask any remaining questions of your instructor. Test-Taking Tips: (1)Have all of your materials ready to take the test to include the textbook chapters, pencil to jot down notes, online course ready, and a quiet place for concentration. 2)Read the question carefully to understand exactly what is being asked. You may want to make notes. (3)Provide enough detail in your responses to the questions to ensure that the instructor can see your understanding of the key concepts. (4)Most questions will be broken into multiple parts. Be sure that you specifically address all parts of the question. Response to each part should be 2 to 5 sentences, depending on the number of parts. Response length for the entire question is expected to be 9 to 12 sentences (approximately 250 to 350 words).