I have some work, in an Excel spreadsheet. Background
An integral part of any business is business analysis of the target market. [url removed, login to view] provided you a small subset of your existing customer list (download the [url removed, login to view] file). Like much data in a commercial environment, the Excel file contains poorly formatted purchase data and some preferences information about your customers.
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.
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.
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?
Bu iş için 35 freelancer ortalamada $142 teklif veriyor
i understand your project i can do this please contact me thanks Relevant Skills and Experience Excel, Data entry, Web search, Web scraping Proposed Milestones $155 USD - full I am a professional Freelancer. I would Daha Fazla
Hi, Reading your project description about completing your data analysis on Excel makes me feel like you have me in mind when creating the project. I have all it takes to deliver. Relevant Skills and Experience You wi Daha Fazla
I have worked with large data, sorting and grouping and organizing them into useful information that can be used to make timely and precise decisions. Relevant Skills and Experience I have a vast knowledge in Microsof Daha Fazla
provide all the data what we required regarding this project Relevant Skills and Experience all Proposed Milestones $111 USD - what you need i will provide data Additional Services Offered $1 USD - all MS OFFICE rel Daha Fazla
Fast and cheap, but quality work. Habilidades y experiencia relevante Advanced use of Excel. Finance knowledge. Htos propuestos $15 USD - Tasks I, II and III. $20 USD - Tasks IV, V. $5 USD - Task VI.
Will do your work properly Relevant Skills and Experience bsc hons(IT) degree holder Proposed Milestones $133 USD - learn something
I will complete this project according to the directions and deliver a satisfactory product. Relevant Skills and Experience Detailed experience with excel. Proposed Milestones $222 USD - Final Project Additional Ser Daha Fazla
I have extensive data entry experience, with results driven attention to detail, and confidentiality. I am thorough, and work efficiently. Please accept my proposal for consideration. Relevant Skills and Experience My Daha Fazla
Hi sir, I am Ali Ajaz from Pakistan. I have these skills data entry,articles writing and database. I want to work with [url removed, login to view] send me the information and all [url removed, login to view] I can start this job.
Having relevant experience in Excel Relevant Skills and Experience More than 10 Years of Exposure Proposed Milestones $144 USD - -