BIS 155 Final Exam


BIS 155 Final Exam 
(TCO 9) You have been tasked with analyzing an extremely large amount of data and to ultimately…


SKU: BIS 155 Final Exam Categories: , Tags: , , ,


BIS 155 Final Exam

BIS 155 Final Exam


(TCO 1) The salespeople for The Edwards Company earn a commission on top of their regular pay if they sell more than $5,000 in a week. You have started a worksheet shotcown below to track sales each week and calculate the commission, if any, earned for that week. Two rows of sample data have been entered to test formulas.

Part 1: Write the formulas needed in Cells I7 and J7 to total sales and calculate commission, if any, earned for the week. Use cell references in your formulas, and write them so that they may be copied to Cells I8 through I11 and J8 through J11.

I7 is the total sales of the week and…

Part 2: Write the formula needed in Cell B19 to show the amount of the highest sales for the week, and the formula needed in B20 to show the average sales by salesperson for the week.

To find the maximum sales, the…

Part 3: Describe the formatting needed to give the worksheet a professional appearance.

Professional appearance makes it…

Part 4: You know others in your company may have questions about the worksheet, so you want to be sure they know who created it and the purpose of the worksheet. Describe the best way to incorporate this information into your workbook.

The information on the purpose and the creator of a…

(TCO 3) Assume you work for a local business person who owns 3 different fast food franchise locations in town. The owner would like to analyze the productivity and operations of each store individually as well as in comparison to each other. Each location is currently tracking operations in a spreadsheet which includes sheets for analyzing three key areas of interest to the owner.

The first sheet for tracking inventory quantity and prices for food and paper products looks something like the following:

The owner also wants to track the following:

  • revenue/sales by product category and by individual item
  • employee data including schedules and salary expenses

Part 1: Select one of the above mentioned key areas and identify and explain what fields should be included in this analysis. Also explain HOW you would recommend organizing the data using features like data tables or pivot tables.

If using data tables explain how you would accomplish the sorting, filtering and formatting using the fields you identify.

If using a pivot table then explain how you would create the pivot using the categories mentioned above. Which fields would be placed in the report, column, row and value fields when organizing the data?

Using the revenue/sale by individual item I would consider using first…

Part 2: Using the data table or pivot table explained above, identify what type of chart you would recommend to use when comparing the 3 locations for the owner. Clearly explain WHY you are recommending that specific type of chart for the data – why is that data a good fit for that type of chart? Also include ideas for formatting this chart to make this important information more “readable” for the franchisee owner.

(Points : 40)

The best chart to use in this case to compare the…

Ted has been retired the last few years and has passionately resumed golfing. He has recorded the round number, date, golf course, number of putts, and total score for each round in Excel as shown in the truncated diagram above. After almost three years of data collection and 300+ rounds of golf, Ted decides to analyze the data to determine if he has improved during this time.

Part 1: Ted would like to calculate the average scores for each golf course over the past three years. What Excel tool will allow you to do this? Be sure to specify the exact steps to obtain the desired results.

The best excel tool to use in this case is the…

Part 2: Based on your response from part 1, what other Excel tool can be used to produce the same results? Again, be sure to specify the exact steps to obtain the desired results.

The other tool that can be used to….

Part 3: Ted would also like to view only the rounds of golf where he had greater than 32 putts for the round. The number of putts should be displayed from highest to lowest. Please describe the steps to achieve this result.

To select the values that are greater than 32….

Part 4: To determine if he has improved over the past 3 years, Ted would like to calculate a running average in column F. A running average displays the average for all rounds played to date. For example, the running average in cell F4 should show the average for the total scores for round numbers 1 through 3. Write this formula as if it were in Cell F2. Write the formula so that it may be copied down the column without modification.

(Points : 40)

=if(F1 + F2 +….

(TCO 5) You are the owner of three clothing stores and you would like to see how much combined profit you made from your three stores. Below are three worksheets titled “John”, “Bob”, and “Ruth” which show the ‘Total Sales’ during the months of January and February.

Using the three worksheets shown above, you will create a new worksheet called “2014” which will consolidate and summarize the information from the stores of John, Bob, and Ruth. The new worksheet will look similar to what you see below.

Part 1. Write out a 3-D formula using the SUM function as if it were in (Cell B2) of worksheet “2014” that will calculate the Total Shirt Sales for John, Bob, and Ruth. Write the formula so that it may be copied to the month of February (Cell B3) without modification.

A 3-D formula will be…

Part 2. Write out a worksheet reference formula without the SUM function as if it were in (Cell C2) of worksheet “2014” that will calculate the Total Pant Sales for John, Bob, and Ruth. Write the formula so that it may be copied to the month of February (Cell C3) without modification.

= CountIf….

Part 3. Now that there are Total Shirt Sales and Total Pant Sales for January and February in the “2014” worksheet, it is time to calculate CLOTHING TOTAL in all four worksheets (“John”, “Bob”, “Ruth”, and “2014”). Explain how you will complete this step without retyping the formula in each worksheet. Discuss the advantages of doing it this way and describe any pitfalls that might take place.

To calculate the clothing total, one can comfortably use the…

Part 4. Write out a formula using the SUM function as if it were in (Cell D2) of ALL worksheets that will calculate the CLOTHING TOTAL. Write the formula so that it may be copied to the month of February (Cell D3) and to the CLOTHING TOTAL YEARLY TOTAL (Cell D4) without modification. Also, write out a formula using the SUM function as if it were in (Cell B4) of ALL worksheets that will calculate the YEARLY TOTAL for Total Shirt Sales. Write the formula so that it may be copied to YEARLY TOTAL for Total Pant Sales (Cell C4) without modification.


Part 5. You are interested in viewing the contents of all four worksheets (John, Bob, Ruth, and 2014) side-by-side on the screen. Describe the steps that will arrange the worksheet tabs this way and discuss the advantages of doing this.

Basically, this will involve viewing of the three…

(TCO 3) A retail chain keeps daily data with summaries of the dollar amounts of sales for each of 151 different products sold at 23 retail stores. The daily data is tracked by product and by store. At the end of the day each store sends its data to the sales manager at the corporate office. The data is sent in an Excel spreadsheet. Describe a data consolidation strategy and at least two different types of visualization techniques the sales manager can use to analyze the data received from all stores. Be sure to include formulas that are to be used in the Excel spreadsheet. (Points : 40)

Data consolidation strategy is a new concept that a majority of mangers are trying to adopt in order to keep track of their…

BIS 155 Final Exam

(TCO 9) You are a current student with DeVry University and have a work study job in the College of Business & Management. The Dean understands that you have completed BIS155 and has a data set saved to a .csv file format that needs to be analyzed. There are a total of ten employees with 216 record entries for the 2014 year including individual hours worked in the range of 15 to 79 hours per week. The data set includes the following fields: Employee Name, Week Ending Date, Hours Worked, Hourly Wage, Gross Pay, and Net Pay. The Dean has asked you to use your skills learned from BIS155 to create the following reports with the support of Microsoft Excel integrated into Microsoft Word:

Report 1: Total Net Pay by Employee to Date

Report 2: Total Hours Work to Date

Report 3: Comparison of Total Net and Gross Pay to Date

Report 4: Employees that have worked Over 40 hours for any given week

In scholarly and descriptive details, share with the dean a complete step by step process in how you plan to create these four reports presented in Microsoft Word. Also take into account that the Dean may also like illustrative options with charts.

(Points : 40)

To create report 1, The user will select the….

To create report 2, The user will select the name of each employee from the employee…

To create report 3, The user will select the…

(TCO 5) Mark Jacobs, owner of Jacob’s Mini-Mart currently operates two stores in Atlanta. His business has been very successful, and he is now opening two new stores. He will no longer be able to manage all stores himself, so he is hiring managers for three of the four stores. He will continue to operate one store, and act as general manager over the company. He is looking for an easy and efficient way to collect information from the stores, and he would like your help with the following:

PART 1 Marc wants you to design a worksheet for his managers to complete showing the sales by product category by month at each store. He wants to be sure the managers don’t change the formulas in the worksheet. Describes the characteristics of the worksheet. PART 2 Describe the process Mark will use in combining the completed worksheets into on workbook and creating the summary report.(Points : 40)

Part 1 For the case of Jacob’s Mini-Mart, he needs to have a way of …

Part 2 For the completed worksheets to make sense to the users….

BIS 155 Final Exam

(TCO 9) You are assigned to analyze a large dataset containing the detailed records of invoices of a company’s customers which contain the customer ID, their address, city, region, post code, country and sales person and additional information about specific order id, order date, shipped date, shipper, product ordered, unit price, quantity, discount, ship address, ship city, ship postal code, ship country, and product category name. Explain how you would use Excel and Excel formulas 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)

The use of excel for data analysis is one the best processes that data…

(TCO 1) You are the Payroll Manager for your employer. Your payroll clerk has submitted the worksheet displayed here. All employees earn their regular pay rate times the hours worked plus an overtime bonus of 50% of the hours worked in excess of 40 hours.

BIS 155 Final Exam
Part 1: Write a formula as if it were in Cell E2 that will calculate gross pay. Write the formula so that it may be copied to the other employees without modification.
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)

BIS 155 Final Exam

(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–AC
Cost Performance Index(CPI)EV/AC
Schedule Variance(SV)EV–PV
Schedule Performance Index(SPI)EV/PV
Estimate to Completion(ETC)EAC–AC
Estimate at Completion(EAC)BAC/CPI
Variance at Completion(VAC)BAC–EAC
Status (Earned Value Index)(EVI)(CPI+SPI)/2

Also 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 : 400

ensuring that he designs his worksheet so that the managers have very minimal ways of

BIS 155 Final Exam

(TCO 5) A table named “Daily Sales” in a Microsoft Access database has the following fields:

Product A
Product B
Product C
Product D

Each 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.
Note: This is a one part question. (Points : 40)

BIS 155 Final Exam