Data Analysis/Excel

Excel and Data Analysi Part. 1 Start Excel. Enter the worksheet title, A Healthy Body Shop, in cell A1 and the worksheet subtitle, Annual Revenue Analysis, in cell A2. Beginning in row 3, enter the franchise locations, fitness activities, and annual revenues shown in Table 1–7. Table 1-7 A Healthy Body Shop Annual Revenues Atlanta Boston New York Phoenix Portland Aquatics 72528.50 53461.85 88367.00 87288.00 65367.37 Cardiovascular 65371.00 92549.86 78345.85 91692.21 58936.63 Dance 53187.96 45493.24 72808.19 57019.34 70114.93 Weight Training 45910.25 85703.71 78719.20 88500.60 47105.30 Yoga & Stretching 58292.50 66884.85 74348.30 76219.00 57390.23 Apply a theme of your choice to your worksheet. Create totals for each franchise location, and fitness activity. Create average, max, and minimum for each franchise location. Format the worksheet title with the Title cell style. Center the title across columns A through G. Do not be concerned if the edges of the worksheet title are not displayed. Format the worksheet subtitle to 14-point Blue, Accent 1, Darker 50% bold font, and center it across columns A through G. Use Cell Styles to format the range A3:G3 with the Heading 3 cell style, the range A4:G8 with the 40% - Accent3 Cell style, and the range A9:G9 with the Total cell style. Center the column headers in row 3. Apply the Accounting Number format to the range B4:G4 and the range B9:G9. Apply the Comma Style to the range B5:G Adjust any column widths to the widest text entry in each column. Select the range A3:F8 and then insert a 3-D Clustered Column. Move the chart to a new sheet in the workbook and resize the chart so that it appears presentable. Apply the worksheet name, 3-D Analysis, to the sheet tab and apply a color of your choice to the sheet tab. Change the document properties. In the document properties write your first name as the Author, and the Title Final Exam. Part. 2 Create a new worksheet. Enter the worksheet title, University of Roger Williams, in cell A1 and the worksheet subtitle, Students Final Grades, in cell A2. Beginning in row 3, enter the students names, lab and homework grades, and shown in Table 1–8. Table 1-8 Students Final Grades Name Lab1 Homework1 Lab2 Homework2 Lab3 Steven Ortiz 85 95 75 97 68 Jessica Martinez 55 90 95 98 99 Laurie Johnson 75 85 90 95 96 Russell Wilson 70 75 70 75 80 Vicky Sanchez q 100 50 100 65 75 Apply a theme of your choice to your worksheet. Create a totals and average row for each student’s lab and homework, assignment. Format the worksheet title with the Title cell style. Center the title across columns based on your worksheet. Format the worksheet subtitle to 14-point Blue, Accent 1, Lighter 40% bold font, and center it across columns A through G. Use Cell Styles to format the range A3:G3 with the Heading 3 cell style, the range A4:G8 with the 20% - Accent3 Create a grades section and, use the Nested IF function to display a letter grade for each student based on the following grading rubric. Greater than or equal to 90 = A Greater than or equal to 85 = B+ Greater than or equal to 80 = B Greater than or equal to 70 = C Greater than or equal to 60 = D Anything under 60 should result in a F Part. 3 Create a new worksheet. Enter the worksheet title, Concatenate, in cell A1 and the worksheet subtitle, Full Name, in cell A2. Beginning in row 3, enter the students first and last names as shown in Table 1–7. Table 1-7 Student Names First Last Steven Ortiz Jessica Martinez Laurie Johnson Russell Wilson Vicky Sanchez Create a Full Name column and use concatenate to combine students first and last name. Create an upper and lower section to display students name in upper and lower case. Use Rand and RandBetween to randomly display all five students’ names in a different section on the worksheet. Create a replace or substitute column and use replace or substitute to replace Steven with Stephen. Create a new worksheet and demonstrate use of Future Value (FV) Create a new worksheet and demonstrate the use of Calculating a loan using (PMT) Create a new worksheet and demonstrate use of Goal Seek. Please submit workbook to Bridges once you have completed all the steps above. Part. 4 Create a new worksheet and title it Future Value Using one of the formula’s you learned throughout the year calculate, the future value of the following: Sam recently graduated college and landed her first job as an accountant. She currently has zero dollars in the bank and wants to preview a saving scenario in worksheet 1. At an interest rate of 4.5 % with an opening balance of zero dollars, and a deposit of $1000.85 dollars she wants to know what the interest rate will be for the first year. Sam decided to deposit at-least $1000.85 dollars into her account for the next ten years, investing with a standard 4.5% rate. Please be sure your worksheet has the following titles Year, Opening Balance, Deposit, Interest, and Current Balance. Same decided she would love to see what this type of investment would look like in the next 30 years and needs your help deciding which function could perform this type of calculation for her. Please be sure to show all your work in the worksheet. Part. 5 Using the final exam worksheet attached. Please demonstrate how to use the following formulas: 1) Using a formula, calculate the monthly payment on all three scenarios in the worksheet Q1 2) Using a formula, calculate the future values of all three scenarios in the worksheet Q2 3) Using a formula, calculate the payment of a personal loan for $25,000.00 in worksheet Q3 4) Using What-If Analysis, calculate the result of the monthly payments for all three scenarios listed in the worksheet Q3 5) Using What-If analysis Scenario Manager, in worksheet Q4, calculate the following Scenarios and show a summary of all three on one worksheet called Summary Analysis: Reduce the Taxes paid to $400.00 Cut Advertising in half Cut Office Personnel in half 7) Using Hlookup and VLOOKUP please fill in the grey colored squares in worksheet Q5 to list the number of parts sold in the associated months for part # asd-04. 8) Using the Pivot Table tool, create a pivot table from the data on the Pivot Table worksheet on a new worksheet tab that shows the following information: Please create four slicers from your pivot table for any of the fields your choice. Please be sure to organize your slicers with different colors to demonstrate organizing. Preview image 10) On worksheet Q7, please use a single nested formula to put a “TRUE” in the box next to products that are Yellow, Type C, AND have Sales >40,000. For products that are Brown please put a “Bonus” in the box, for products that are “Black” put a “$25,000” in the box, and lastly false for all other products.    

Sample Solution