Excel Sheet

1. General requirements and notes for the Project

1. ***!!!***In ALL the problems (provided with Excel spreadsheet or Word tables, or in a picture showing the spreadsheet), do NOT insert any row or column, and do not move any given contents to a different cell.

2. *** !!! *** Use proper references (relative, absolute, or mixed).

3. Follow “rules of thumbs” found in the box on P.5 of this project assignment – CRITICAL!!

4. Note: Do NOT round the numbers – format them to show appropriate decimal places.

5. All worksheets must be properly formatted ($ / %, 12-point fonts, alignment, grid line, and borders, etc).

6. All other general requirements for Excel HWs (font size, displaying entire formula, etc) apply here.

1. Problems for the Project

Problem 1: LA County Health Department - Revenues/expenses update

( VLOOKUP and IF combined )

LA County Health Department (LACHD) supervises private healthcare providers that undertake government projects. The LACHD would make estimates of the cumulative revenues/expenses of the providers on monthly basis, and on the tenth day after each quarter ends, update its estimated data for the quarter with the new data provided by the providers.

The rules of the update: (1) If the updated data from a provider matches LACHD’s estimate, the amount stays as the estimated value; (2) if there is no report from a provider, the amount stays as the estimated value; (3) if the estimated value deviates from the reported actual value, the reported value is used to update (replace) the estimated value.

A section of the spreadsheet is given below. Copy and paste the data onto an Excel spreadsheet (so that the last number “30,354,195.00” is in E18), then write appropriate formulas to perform the “check and update” task, following the rule above.

Put your name at the upper-right corner (Last, First)
Care Provider (Note: in alphabetical order)	Estimated	Checked & Updated	Care Provider (Note: Not in order!!)	Reported
Bay View Rehab. Center	686,078.00		Homes for Life Service
Children's Bureau of So Bay	5,197,762.00		Olive Hilltop Centers, Inc	130,816.00
Children's Paradise Inc.	3,121,157.00		San Fernando Children's Center
Foothill Family Counseling	3,861,414.00		South Central Rehab Program	616,644.00
Hamburger Family Center	3,353,699.00		Bay View Rehab. Center	686,078.00
Homes for Life Service	100,000.00		Pacific Asian Psychiatric Services	853,400.00
Intercommunity Child Center	1,609,745.00		ProviCare Comm. Serv.	915,785.00
LAUSD	1,877,623.00		Intercommunity Child Center	1,609,745.00
Olive Hilltop Centers, Inc	130,861.00		LAUSD	1,876,623.00
Pacific Asian Psychiatric Services	853,400.00		Children's Paradise Inc.	3,121,175.00
ProviCare Comm. Serv.	915,785.00		Hamburger Family Center	3,353,699.00
San Fernando Children's Center	587,063.00		SHIELDS for Women Project, Inc.	3,734,250.00
SHIELDS for Women Project, Inc.	3,734,250.00		Foothill Family Counseling	3,861,414.00
South Central Rehab Program	616,644.00		Star View Adult Day Care Center	4,396,804.00
Star View Adult Day Care Center	4,396,804.00		Children's Bureau of So Bay	5,197,762.00
Total by the Department:	31,042,285.00		Total reported:	30,354,195.00
Column A	Column B	Column C	Column D	Column E

Problem 2: Real Estate List (Nested IF first part; VLOOKUP and IF combined for second part)

SweetteHome is a real estate company in Northridge. The following is a list of properties listed (spreadsheet contents must be entered exactly – in fact you only need to copy and paste, begin with A1, with A1:H1 merged for the title “Real Estate List”. Your name – Last, First – are in I1 and J1)

With given data, develop formulas for columns E, H (same logic!), and K. For K, the “Status” has the following categories according to the # of days the property was/is on the market (similar to the Library problem in HW3):

1-14 days: Fast // 15-45 days: Regular // 46-90 days: Slow // 91+ days: Problem

Also, develop appropriate formulas for the home lookup below the home list, so that an agent can enter either the MLS# OR the home address, and that corresponding home’s price, price-per-SqFt, and Status will be displayed.

Real Estate List	(Last name,)	(First name)
MLS#	Address	Listed Price	Sqft	Price per sqft	# Beds	# Baths	Bath/ Bed Ratio	Date listed	Date sold	Status
76044	9001 Darby Ave	$506,900	2,245		4	3		10/13/2015	10/27/2015
05672	4352 Shirley Ave	$369,100	1,852		3	2		9/13/2015	10/28/2015
77221	18724 Chase St	$524,900	2,814		7	5		9/14/2015	10/30/2015
78901	1324 Vintage St	$352,800	1,919		3	2		9/24/2015
71234	7405 Amigo Ave	$1,356,300	3,616		5	4.5		8/31/2015
55660	9901 Calvin Ave	$471,600	2,018		3	2		3/7/2015	4/22/2015
78787	18167 Chase St	$355,200	1,295		3	2		3/11/2015	5/22/2015
Days w/in	Fast:	14	Regular:	45	Slow:	90		Problem:	After 90
Cross-Sheet Reference Component: Add a lookup function portion on a separate sheet next to the above sheet that will allow a lookup, using EITHER MLS# OR Address. The format of the lookup portion is as follows:
	Home lookup:	MLS#	Add-ress	Price	$/Ft	Status
	Enter (
		(Data entry)	(Data entry)	Formula	Formula	Formula

Problem 3: Commission (VLOOKUP, nested IF, SUMIF)

[Special Acknowledgement] This was by a student last year in his work for “Excel in Life”. I found it good in the design and format; so I adapted it (I changed/added some parts of the problem).

Cross-Sheet Reference Component: Add a lookup function portion on a separate sheet next to the above sheet that will allow a lookup, using EITHER EmpID OR Salesperson. The format of the lookup portion is as follows:
	Home lookup:	EmpID	Salesperson	Sales	Commission	Status
	Enter (
		(Data entry)	(Data entry)	Formula	Formula	Formula

For the “Status” cell, the formula should be able to display the corresponding status (of “highest” or “lowest”), or when the status is blank, display “About average”.

Problem 4: Pivot Table for Decision Analyses

【All instructions are given below; if you feel an instruction is not exact, you have your freedom to take the printout as you understood the instruction. The purpose of this problem is for you to practice the pivot table】

For this problem, you will use a given spreadsheet to build pivot tables that would allow you to analyze the data from various angles. You will also create charts to clearly present the result of data analyses.

*** If there is any requirements for a printout that you feel unclear/unspecified, clearly state your understanding/interpretation of the requirement, and then go with that understanding/interpretation. ***

Use the ee2-0701.xls spreadsheet (provided)【】, perform the following tasks:

Pivot tables:

Insert yourself as the first record (before Adamson). Your position is Admin Assist (Note: you need to use the words EXACTLY; otherwise your insertion will not be grouped or calculated correctly) for Accounting department, with a salary of $33,000 and a hire date of 11/1/08. Don’t worry about shift.

Follow the instruction of Spreadsheet Handout 4 (Pivot Tables), add your name in cell J5, and then replicate the procedure in the handout, take screen prints of the following (your name – in J5 - must appear in the screen prints):

1. Sum of salary by positions within department. 1 print (Note: similar to “a” onP.6 of Handout 4)

2. Pivot tables: Find average of salaries for Admin Assist by department, print the screen. [Note: You need to resize several columns so the whole pivot table will show on the screen. 1 print

3. Create and print charts (with appropriate title, and your full name appearing on the title):

(1) a pie chart showing the percentage of number of employees by dept; [Hint: Count salary] 1 print

(2) a column chart showing the # of Admin Assistants and Group Assistants by departments. 1 print

4. Make your observations based on 1~3 above, write a brief report to the VP of Human Resources Christine Williams on the salaries of employees of this company. The report should be about half a page to one page, organized in bullets. 1 print – Word

Please assemble outputs in order

【Values of Col C must be that of the providers in Col A (rather than that of column D】

This part data

This part data