Đã Đóng

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. [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.

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?

Kỹ năng: Excel

Xem thêm: do some data entry --2, i can do data entry work, google spreadsheet data entry 2, google spreadsheet data entry -- 2, do some data entry 2, do some data entry 2 do some data entry 2, Do some data entry -- 2, do some data entry -- 2 do some data entry -- 2, Do Data Entry For Me, data entry 2, data entry 2 hours work, data entry -2, Data Entry --2, data entry -- 2, companys that do data entry

Về Bên Thuê:
( 0 nhận xét ) United States

Mã Dự Án: #15763746

37 freelancer đang chào giá trung bình $142 cho công việc này

flashsaiful

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 Thêm

$155 USD trong 3 ngày
(129 Đánh Giá)
6.4
dvcontact

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 Thêm

$150 USD trong 3 ngày
(14 Đánh Giá)
4.4
razlan95

This is the kind of opportunity I have been waiting for and I am absolutely interested in your project. I am sure that I can reach your expectations at my earliest convenience Relevant Skills and Experience I am worki Thêm

$155 USD trong 3 ngày
(0 Đánh Giá)
0.0
MarianaEtty

A proposal has not yet been provided

$200 USD trong 10 ngày
(0 Đánh Giá)
0.0
StephanieKirui

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 Thêm

$166 USD trong 4 ngày
(0 Đánh Giá)
0.0
$133 USD trong 3 ngày
(0 Đánh Giá)
0.0
sabarish05

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 Thêm

$111 USD trong 4 ngày
(0 Đánh Giá)
0.0
ramgohil

A proposal has not yet been provided

$133 USD trong 1 ngày
(0 Đánh Giá)
0.0
felipetor

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.

$40 USD trong 5 ngày
(0 Đánh Giá)
0.0
$45 USD trong 2 ngày
(0 Đánh Giá)
0.0
$166 USD trong 3 ngày
(0 Đánh Giá)
0.0
$155 USD trong 3 ngày
(0 Đánh Giá)
0.0
elibarnett1996

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 Thêm

$222 USD trong 4 ngày
(0 Đánh Giá)
0.0
leepup

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 Thêm

$155 USD trong 3 ngày
(0 Đánh Giá)
0.0
Abhraneel

A proposal has not yet been provided

$111 USD trong 3 ngày
(0 Đánh Giá)
0.0
FarhanAjaz

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.

$133 USD trong 5 ngày
(0 Đánh Giá)
0.0
KIRMON

Having relevant experience in Excel Relevant Skills and Experience More than 10 Years of Exposure Proposed Milestones $144 USD - -

$144 USD trong 3 ngày
(0 Đánh Giá)
0.0
sunilkataria15

A proposal has not yet been provided

$222 USD trong 3 ngày
(0 Đánh Giá)
0.0
$100 USD trong 3 ngày
(0 Đánh Giá)
0.0
$155 USD trong 3 ngày
(0 Đánh Giá)
0.0