CMR 282 Computer-Based Business Analysis Assignment- Miami University
To Do:
• Performance Assessment 2
• Case Study Part 1
• Case Study Part 2
• Dashboard Forum Post - Find an Example
o For the Dashboard forum, post a link to an example of a dashboard, then explain the pros and cons of your example - how useful is it? How can it be improved?
Submit the following files:
(1) U2-RSRHelpDesk-2.xlsx
(2) 2-PBMSales2.xlsx
(3) Worksheet Analysis document (Word)
For your essay:
• Essay length - 200 words
• What insights do you have about the data presented in the spreadsheet? Are there any anomalies or interesting facts you see?
• What kinds of decisions can you make from the data, or what recommendations can you make to improve the business, process, etc. that is presented in the spreadsheet?
• What can be changed to improve the worksheet? Why would your recommendation be an improvement?
Use Conditional Logic Formulas in a Help Desk Worksheet
1. Open U1-RSRHelpDesk.
2. Save the workbook with the name U1-RSRHelpDesk-2
3. Create range names of your choosing for each of the following ranges:
• A4:E6, which will be used in a lookup formula
• E8:E30 in the Operator ID column
• I8:I30 in the Time Spent column
• J8:J30 in the Status column
4. In cells I5 and I6, create COUNTIF formulas to count the number of active calls (I5) and the number of closed calls (I6). Use range names in the formulas.
5. Create COUNTIF formulas in cells K3 through K6 to count the calls assigned to operator IDs 1, 2, 3, and 4, respectively. Use range names in the formulas.
6. Create SUMIF formulas in cells L3 through L6 to calculate the total time spent on calls assigned to operator IDs 1, 2, 3, and 4, respectively. Use range names in the formulas. Format the results to display two digits after the decimal point.
7. Create AVERAGEIF formulas in cells M3 through M6 to find the average time spent on calls assigned to operator IDs 1, 2, 3, and 4, respectively. Use range names in the formulas. Format the results to display two digits after the decimal point.
8. Create the HLOOKUP formula with an exact match in cell G8 to return the last name of the operator assigned to the call. Use the range name for the lookup table in the formula.
9. Create the HLOOKUP formula with an exact match in cell H8 to return the first name of the operator assigned to the call. Use the range name for the lookup table in the formula.
10. Copy the HLOOKUP formulas in the range G8:H8 and paste them into the remaining rows in the list.
11. Save, print, and then close U1-RSRHelpDesk-2
Case Study 1
Yolanda Robertson, your manager at NuTrends Market Research, has been pleased with your previous work and has assigned you to a project with a new client. Yolanda is preparing a marketing plan for a franchise expansion for the owners of Pizza By Mario. The franchise was started in Michigan and has stores in Ohio, Wisconsin, and Iowa. The owners plan to double the number of locations within the next two years by expanding into neighboring states. The owners have provided a confidential franchise sales report to Yolanda in an Excel file named PBMSales.xlsx. Yolanda needs your help with Excel to extract some statistics and calculate franchise royalty payments. With this information, Yolanda will develop a franchise communication package for prospective franchisees.
Open the workbook and save it with the name 2-PBMSales. Yolanda has asked for the following statistics and wants you to read the instructions found on the next page before you begin.
• A count of the number of stores with sales greater than 500,000
• A count of the number of stores located in Michigan with sales greater than 500,000
• Average sales for the stores in Detroit, Michigan
• Average sales for the Michigan stores established prior to 2006
• Total sales for the stores established prior to 2014
• Total sales for the Michigan stores established prior to 2014
Create the formulas for Yolanda in rows 3 to 16 of columns H and I. Create range names for the data so Yolanda will be able to easily understand the formula when she reviews the worksheet. You determine the layout, labels, and other formats for the statistics section. The royalty rate and fee will be completed in Part 2. Save the workbook.
Case Study 2
In the marketing package for new prospects, Yolanda plans to include sample sales figures and related franchise royalty payments. Pizza By Mario charges each store a royalty percentage based on its annual sales. As sales increase, the royalty percentage increases. For example, a store that sells $430,000 pays a royalty of 2% of sales, while a store that sells $765,000 pays a royalty of 5% of sales. A royalty rate table is included in the worksheet. Create a range name for the table and then create a lookup formula to insert the correct royalty percentage for each store in column F. Next, create a formula to calculate the dollar amount of the royalty payment based on the store's sales multiplied by the percentage in column F. Format the royalty percentage and royalty fee columns appropriately. Save the revised workbook.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Excel-Data-Files-2019.zip