BIS 155 Final Exam (Updated)

BIS 155 Final Exam (Updated)

1. (TCO 1) You are a financial analyst for a Thrift Shoppe. You have been asked to develop a quarterly revenue analysis worksheet for the 5 shop locations.

Given the data above describe how to perform the below steps.
1. Step 1- Center and merge the headings of each location.
2. Step2 – Enter totals for each item and each shop location.
3. Step3 – Determine the lowest and highest of each shop location. (Points : 40)

2. (TCO3) When creating Excel Charts, it is important to insure the dataset is created properly and that an appropriate chart is selected to best describe the data.  For the spreadsheet shown below:
1) Identify the appropriate chart type.
2) Identify and describe data problems that will adversely impact interpretation of the chart.

3. (TCO 4) You have been hired by Nike Inc. as an Analyst Consultant. Your first task is to determine which shoe style has the highest, average, and lowest sales. Additionally, you have been asked to determine which months have the highest sales. You have been provided a spreadsheet that contains the shoe style and month of sale. (Points : 40)

4. (TCO 5) Super Clean Carpet Cleaning business wants to develop an Excel workbook that contains 12 identical formatted worksheets (1 for each month of the year). On these worksheets the monthly expense descriptions are listed in Column A and expense amounts are in Column B. A total of the expenses is the last entry of Column B. The business controller needs to know for tax purposes what the yearly totals are for each expense category and for the total of all expenses for the year. Given this assignment by the business controller, summarize how, using the tools for managing multiple worksheets, you would create the 12 identical worksheets. Also describe how to consolidate this data into a yearly Excel sheet showing the totals of each expense category and the total of all the expenses in the business for the year.

5. (TCO 9) As a data analyst, a company provided you with a large invoice dataset in a text file. The data contains data about the customers’ information, products ordered, shipping information, sales person, unit price, discount, taxes, and freight costs. You have been tasked with analyzing this data to produce a report to share with the Board of Directors. 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 your recommendations. (Points : 40)