Client Total Purchases in Prior Year Average Order Customer Rank By Purchases Company Name Average Annual...

Client	Total Purchases in Prior Year	Average Order	Customer Rank By Purchases	Company Name	Average Annual Sales
A	2,379	1,190		A		(use hlookup formula here)
B	72,560	6,596		E		(use hlookup formula here)
C	10,366	5,183		M		(use hlookup formula here)
D	77,745	7,068
E	11,106	5,553
F	83,295	7,572
G	11,899	5,950
H	89,243	8,113
I	12,749	6,375
J	95,618	8,693
K	13,660	6,830
L	102,450	9,314
M	14,636	7,318
N	109,770	9,979
O	15,681	7,841
P	117,608	10,692

Average	44,121



The assignment:

(1) Use the attached document and dataset to do the following:

a. In the area indicated on the spreadsheet create the hlookup formula to find the average annual sales for companies A, E, and M

b. Find the median total purchases for the company’s customers.

c. Complete the column ranking the customers from high (highest = #1) to low based on annual sales to those customers.

I this is actually in an excel spread sheet but I couldnt get it to paste in correctly. I need to know the formulas for the hlookup. It is copied the exact way its laid out in the spead sheet. PLEASE help!

Expert Solution

	B	C	D	E	F	G	H	I	J	K	L	M	N
2	client	Total Purchases in Prior Year	Average Order	Customer Rank By Purchases		Company Name	Average Annual Sales	remarks
3	A	2379	1,190	16		A	2379	VLOOKUP(G3,B3:C18,2,TRUE)
4	B	72560	6,596	8		E	11106	VLOOKUP(G4,B3:C18,2,TRUE)
5	C	10366	5,183	15		M	14636	VLOOKUP(G5,B3:C18,2,TRUE)
6	D	77745	7,068	7		remarks in formula		VLOOKUP(Company Name ,Data Range,2,TRUE)			2 used for second colmun in data range
7	E	11106	5,553	14		HLOOKUP used in Horizontal data format. But here vertical data provided, so we can use VLOOKUP Function for Vertical data.
8	F	83295	7,572	6
9	G	11899	5,950	13
10	H	89243	8,113	5
11	I	12749	6,375	12
12	J	95618	8,693	4
13	K	13660	6,830	11
14	L	102450	9,314	3
15	M	14636	7,318	10
16	N	109770	9,979	2
17	O	15681	7,841	9
18	P	117608	10,692	1
19
20	Median	44121
21	remarks	MEDIAN(C3:C18)

ekkarill92 answered 2 years ago

Refer to the random sample of customer order totals with an average of $78.25 and a...

Refer to the random sample of customer order totals with an average of $78.25 and a population standard deviation of $22.50. a. Calculate percent 90 confidence interval estimate of the mean, given a sample size of 40 orders. b. Calculate 9 0 percent confidence interval for the mean, given the sample size of 75 orders. c. Explain the difference. d. Calculate the minimum sample size needed to identify a %90 confidence interval for the mean, assuming a $5.00 margin of...

Customer Profitability Score Exercise: (10 marks) If the average annual cost per customer of a software...

Customer Profitability Score Exercise: If the average annual cost per customer of a software house is $1592, customer A generates a profit of $365, and Customer B generates a profit of $1425. Calculate the CPS and compare the results. Please type your answer below. Search Engine Rankings (by keyword) and click-through rate Why is this indicator important? Along with page views and bounce rates (see KPI on page 155), search engine rankings (by keyword) and click-through rate are...

Rank in order of total heat input (Note: don’t confuse with heat input RATE) and overall...

Rank in order of total heat input (Note: don’t confuse with heat input RATE) and overall size of heat affected zone: GTAW, SMAW, Oxyfuel, laser beam welding What are the advantages of reducing total heat input in a weld?

On January 1, Morris Company offered a customer a 10% trade discount if the customer purchases...

On January 1, Morris Company offered a customer a 10% trade discount if the customer purchases 1,000 units of an item within the next 6 months. Each item sells for $100. Based on the customer’s previous purchase history, Morris believes there is a 60% chance that the customer will purchase more than 1,000 units. On January 10, the customer purchases 200 units on credit. Required: How much revenue should Morris recognize related to this customer? Prepare the entry to record...

What is the firm's total change in cash from the prior year to the current year?

CategoryPrior YearCurrent YearAccounts payable3,136.005,912.00Accounts receivable6,865.008,909.00Accruals5,736.006,053.00Additional paid in capital20,381.0013,763.00Cash??????Common Stock2,8502,850COGS22,644.0018,617.00Current portion long-term debt500500Depreciation expense964.001,040.00Interest expense1,294.001,127.00Inventories3,079.006,686.00Long-term debt16,952.0022,082.00Net fixed assets75,263.0074,206.00Notes payable4,068.006,510.00Operating expenses (excl. depr.)19,95020,000Retained earnings35,375.0034,409.00Sales46,36045,043.00Taxes350920What is the firm's total change in cash from the prior year to the current year?Answer format: Number: Round to: 0 decimal places

What is the firm's total change in cash from the prior year to the current year?...

What is the firm's total change in cash from the prior year to the current year? Accounts payable 3,194.00 5,965.00 Accounts receivable 6,995.00 9,057.00 Accruals 5,777.00 6,028.00 Additional paid in capital 19,562.00 13,579.00 Cash ??? ??? Common Stock 2,850 2,850 COGS 22,838.00 18,682.00 Current portion long-term debt 500 500 Depreciation expense 952.00 1,006.00 Interest expense 1,287.00 1,145.00 Inventories 3,096.00 6,734.00 Long-term debt 16,588.00 22,141.00 Net fixed assets 75,663.00 74,153.00 Notes payable 4,057.00 6,584.00 Operating expenses (excl. depr.) 19,950 20,000 Retained...

Name five community support services that you could refer a client to in order to meet...

Name five community support services that you could refer a client to in order to meet their needs

The recommended total fiber intake for a 13-year-old boy is _____ g/day. The average annual growth...

The recommended total fiber intake for a 13-year-old boy is _____ g/day. The average annual growth during the school years is _____ pounds in weight. The average annual growth during the school years is _____ inches in height. A child’s statute, or standing height, should be measured without shoes. Based on the DRIs, the recommended protein intake for school-age children is _____ gram of protein per kg body weight per day for 4- to 13-year-old girls and boys. Calorie/protein calculations...

Firenza Company manufactures specialty tools to customer order. Budgeted overhead for the coming year is: Purchasing...

Firenza Company manufactures specialty tools to customer order. Budgeted overhead for the coming year is: Purchasing $40,000 Setups 37,500 Engineering 45,000 Other 40,000 Previously, Sanjay Bhatt, Firenza Company's controller, had applied overhead on the basis of machine hours. Expected machine hours for the coming year are 50,000. Sanjay has been reading about activity-based costing, and he wonders whether or not it might offer some advantages to his company. He decided that appropriate drivers for overhead activities are purchase orders for...

Customer # Order Size (Quantity) Total Cost of Order 10211 28 1631 10212 31 1923 10213...

Customer # Order Size (Quantity) Total Cost of Order 10211 28 1631 10212 31 1923 10213 43 2070 10214 47 2392 10215 32 1886 10216 43 2307 10217 25 1486 10218 46 2448 10219 41 2210 10220 48 2401 10221 29 1860 10222 32 1786 10223 49 2485 10224 44 2203 10225 33 1855 10226 46 2380 10227 42 2102 10228 31 1683 10229 30 1706 10230 35 1955 10231 34 1992 10232 33 1926 10233 27 1852 10234 32...

Question