Do some data entry -- 2

I have some work, in an Excel spreadsheet. Background An integral part of any business is business analysis of the target market. [login to view URL] provided you a small subset of your existing customer list (download the [login to view URL] file). Like much data in a commercial environment, the Excel file contains poorly formatted purchase data and some preferences information about your customers. Demographic Research Data about Music World customers is useful only if it is usable and understandable, in other words when converted to information. You will first need to format some of the data and then perform an analysis on the demographics of your customers. You will need to complete the following tasks to finish the analysis. I. Data: The provided workbook contains a worksheet called "data" with raw data about your customers. You will use this data to complete your analysis. The data provided to you is in a non-edited format. Rename the 'data' sheet to "Customer Data". Make a table of the data in this worksheet, formatted using Table Style Dark 6 in the styles group of the Home tab. The data must be appropriately formatted, i.e., dates [12/24/2002], currency [$14,531.94]. Use custom format under Format Cells. (Hint: don’t forget zip, phone, and fax. You need to use the special type in the function “More number formats…”) The column title formatting for columns N through Q should be wrapped to fit the title on two lines for viewing purposes. Edit the format of your data in the worksheet Customer Data such that the actual data in the spreadsheet can be seen clearly and entirely (i.e. adjust width and height as necessary). All heading cells should be centered vertically and horizontally. Insert spaces between words in column names wherever appropriate. Since the list of customers is so long we want to know what each column represents as we scroll down the list of customers. Since we track so much data on each customer, it helps to keep their name persistently visible. Freeze the top row and two leftmost columns so that the column headers and customer names are always on the screen. Throughout the project, any data that is not formatted correctly will result in point deductions. II. Demographics: After completing the above task, copy the entire worksheet "Customer Data" to a new worksheet and name it "Demographics". You would like to know how much your customers spend on computer products (sales) and how this amount is related to their product preference. To get a rough impression of this relationship, you will need to perform a double sort of your data. Sort it by Preferred Genre and then by Total Sales to Customer. (Hint: you need to use the add level button in the “custom sort…” function) Note: Be careful! The sorting of your data needs to be simultaneous for all columns selected. III. Customer Who Spent the Most on CDs: Copy the Demographics worksheet to a new worksheet and rename it “Big Spender”. You now want to find the largest amount for a single customer spent on CDs (sales) last year. Use one of Excel's functions max() (Note: Do not use sorting!) to find this amount (just the amount at this point, not the person). Put the formula in cell S2, and label it appropriately in S1. Use conditional formatting to highlight the top 10% in total sales (any colors you wish). IV. Analyzing the Data: Create a copy of the Demographics worksheet and rename it "Store Title". You will need to insert 5 lines above the row containing the column titles. Every business needs a logo so that customers can distinguish your music store from others. Your next task is to use WordArt and give your business a logo. You can choose any style of WordArt, but you must use font size 44. Place the created business logo in the upper-left corner of the worksheet. You may need to change the row height appropriately. ALTERNATIVE: Merge and Center cells A1:C5, and use a middle vertical alignment. Enter the name of your store here, and use the Title cell style. Now we would like to calculate some financial figures. These figures will help familiarize you with your store. The financial information you want to look at is as follows: Total Sales, Total Cost of Goods Sold, Total Shipping Cost, Total Number of Visits, and Average Profit per Visit. The functions and/or formulas used need to be in cells Q1-Q5 and the appropriate labels in cells P1-P5. Format your results appropriately with 0 decimal places. (Hint: Average Profit per Visit = (Total Sales - Total Cost of Goods Sold - Total Shipping Cost) / Total Number of Visits) V. Using Pivot Tables: Use the Demographics worksheet to create one pivot table. Create a new worksheet titled Pivot Tables Create a pivot table which compares Genre by Gender to see the total sale in each category. (Hint: Genre goes on the left, Gender go on top, and Total Sales to Customer is the actual data that will appear in your pivot tables) VI. Car Loan Amortization: Music World is interested in purchasing a new Smart Car BRABUS Cabriolet. The question is, do they want a 5 year 5% loan or 6 year 4.5% loan? Refer to the worksheet titled Car Loan which provides information necessary to carry out this task. Create two copies of this worksheet and name one "Car Loan Solution 5" and the other "Car Loan Solution 6". Perform all your analysis in these worksheets. Use the Car Loan worksheet as a reserve copy of the initial information in case you make a mistake. (Caution: monthly payments must be calculated by the PMT() function. Direct number copy from others will not give you any score for this question.) The following values should be used in your analysis: The amount of the loan (including all taxes and fees) is $24,000. The dealership is offering the following financing options: 5 year 5% loan or 6 year 4.5% loan When completed, answer the following questions in a worksheet titled "Question-Answer". The answers need to be typed in a text box. Compare the monthly payments for the two financing options. Compare the total of all interest payments. Which option would you recommend and why?