BIS 155 Final Exam

Q 1) 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.
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.
Part 3: Describe the formatting needed to give the worksheet a professional appearance.
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.

2. (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)
3. (TCO 4) You are an analyst for the Vanguard Mortgage Company has been using a spreadsheet created by you in the past which functions well. The current worksheet you created currently contains the customer’s account, the cost of the house, down payment, amount financed, mortgage rate, loan years (e.g. 30 year), % financed, date financed and payoff year. You have been tasked with enhancing the worksheet to include noted basic summary statistics. Which sorts of summary statistical methods would you include in the updated workbook? Describe at least six statistical functions that you would use so that you could explain them to your supervisor. (Points : 40)
4. 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)
5. TCO 9) Note: This question has three parts:
   1. As the Sales Manager of a global company, you would like to use MS Excel to analyze the sales data for the entire company. The first challenge is, the Sales Data System cannot export its data as a MS Excel Workbook. It can however, generate a TXT file. Please explain how you would get the data into Excel for analysis
   2. As the Sales Manager, decide two things you would like to know from the sales data (data available: country, product, price per product, date product was sold, quantity sold, sales person). Then select two features you would use to analyze the data to provide you with the information you desire. Please be clear on what two things you would like to know and how you will use the selected feature to provide that information.
   3. With the information now known, please describe how you would present this information to senior management. Please be specific and be sure to consider the audience.