Reference no: EM131444578
Assignment: Cleansing Inventory Data
Project Description:
You currently work part-time in an automotive parts store. Because word of your knowledge of Excel has spread, you have been contacted by the district manager. The database used for keeping track of inventory has been corrupted, causing some issues with the inventory data. You have been asked to use your expertise of Excel to clean the inventory data.
Instructions
1 Start Excel. Open the downloaded workbook named e05ws09_grader_h1.xlsx. Save the file with the name e05ws09_grader_h1_LastFirst.xlsx, replacing LastFirst with your name.
2 Remove any duplicates that exist in the Inventory worksheet. Duplicate records are any records with the same InventoryCode and ItemNumber.
3 Enter the text Step 1 in cell G1.
4 The word PAINT was misspelled as PIANT throughout the Category/Manufacturer column. In cell G2, use a formula to substitute all spellings of PIANT with PAINT from column C. Copy the formula down the column.
5 Enter the text Step 2 in cell H1.
6 The data in column G has nonprintable characters before and after the data contained in each cell. In cell H2, enter a formula to remove any nonprintable characters from column G. Copy the formula down the column.
7 Enter the text Step 3 in cell I1.
8 There are several spaces before and after the data in column H that need to be removed. In cell I2, enter a formula to remove any extra spaces in the data from column H. Copy the formula down the column.
9 The category and manufacturer should be in two separate columns. Enter the text Category in cell J1.
10 Enter the text Brakes, Tools, and Paint in cells J2, J3, and J4, respectively. Use Flash Fill to place the category data from column I in proper case in column J.
11 Enter the text Manufacturer in cell K1.
12 Enter the text NAPA, NAPA, and 3M into cells K2, K3, and K4, respectively. Use Flash Fill to place the Manufacturer data from column I into the new column K. Owing to the nature of the data, Flash Fill will need to be invoked from the DATA tab.
13 Enter the text InvCode in cell L1. In cell L2, enter a formula to display the inventory code from column A as all uppercase letters. Copy the formula down the column.
14 Enter the text ItemCode in cell M1. The ItemCode is a combination of the inventory code in all uppercase letters (column L), with the item number (column B) appended to it. For example, the first ItemCode should be RLXF92569. In cell M2, enter a formula to create this new ItemCode for all items in the data. Copy the formula down the column.
15 Save the workbook. Close the workbook. Exit Excel. Submit the workbook as directed.
Attachment:- lastname_e05ws09_grader_h1.xlsx
|
Oxygen consumption during periods
: The reduction of a person's oxygen consumption during periods of transcendental meditation may be looked upon as a random variable having the normal distribution with mean 38.6 cc per minute and standard deviation of 6.5 cc per minute.
|
|
Sufficient funds to pay cash for entire expansion project
: Winston Enterprises would like to buy some additional land and build a new factory. The anticipated total cost is $169.47 million. The owner of the firm is quite conservative and will only do this when the company has sufficient funds to pay cash for..
|
|
Express the mean growth rate in percentage
: Estimate the first four autocorrelations of ?Yt. What are the units of the autocorrelations (quarterly rates of growth, percentage points at an annual rate, or no units at all)?
|
|
Only the interest income is distributed
: George Jefferson established a trust fund that provides $174,500 in scholarships each year for worthy students. The trust fund earns a 2 percent rate of return. How much money did Mr. Jefferson contribute to the fund assuming that only the interest i..
|
|
Remove any duplicates that exist in the inventory worksheet
: EWS09 H1- Remove any duplicates that exist in the Inventory worksheet. Duplicate records are any records with the same InventoryCode and ItemNumber.
|
|
What is the estimated ar coefficient
: Estimate an AR(1) model for ?Yt. What is the estimated AR(1) coefficient? Is the coefficient statistically significantly different from zero? Construct a 95% confidence interval for the population AR(1) coefficient.
|
|
What is the company operating cash flow
: During 2014, Raines Umbrella Corp. had sales of $790,000. Cost of goods sold, administrative and selling expenses, and depreciation expenses were $610,000, $85,000, and $190,000, respectively. In addition, the company had an interest expense of $52,0..
|
|
Compute the mean number of flips until the pattern
: (a) Compute the mean number of flips until the pattern HHTHHTT appears. (b) Which pattern requires a larger expected time to occur: HHTT or HTHT?
|
|
Premium movie channel
: Suppose that, when we survey 20 randomly selected dish owners, we fi nd that 4 of the dish owners actually subscribe to at least one premium movie channel. Using a probability you found in this exercise as the basis for your answer, do you believe..
|