Excel Project for Cis Class (Sort of Accounting Project)
Baruch College, CUNY Prof. Tansel
CIS 2200
Spread Sheet Project
Following is the sales data for Medical NY Inc. The headings are self-explanatory.
|
CustNo |
Customer |
Type |
St |
Rep |
Balance Owed |
YTD Sales |
|
1 |
Bristol Pharmacy |
P |
RI |
4 |
2,647.10 |
59,500.00 |
|
2 |
Nepco Labs |
L |
MA |
4 |
3,274.25 |
6,866.25 |
|
3 |
EMG & EEG Labs |
L |
MA |
4 |
12,583.97 |
31,685.19 |
|
4 |
Oaklawn Pharmacy |
P |
RI |
4 |
4,513.21 |
5,176.26 |
|
5 |
St. Josephs Hospital |
H |
RI |
3 |
47,113.50 |
64,000.00 |
|
6 |
Cape Psych Center |
H |
MA |
3 |
31,509.10 |
55,173.24 |
|
7 |
Bioran Medical Lab |
L |
MA |
3 |
2,799.12 |
11,927.84 |
|
8 |
Bayshore Pharmacy |
P |
MA |
3 |
6,010.36 |
44,140.87 |
|
9 |
St Anne's Hospital |
H |
MA |
3 |
1,009.53 |
2,431.80 |
|
10 |
Landmark Medical Center |
H |
RI |
3 |
22,630.79 |
29,000.00 |
|
11 |
Lypho-Med Laboratory |
L |
RI |
2 |
538.62 |
3,279.89 |
|
12 |
Gregg's Pharmacy |
P |
MA |
2 |
2,052.70 |
3,771.28 |
|
13 |
Bradley Hospital |
H |
MA |
2 |
9,430.72 |
24,500.00 |
|
14 |
Braintree Hospital |
H |
MA |
2 |
36,609.80 |
48,208.00 |
|
15 |
Miriam Hospital |
H |
MA |
2 |
14,800.44 |
20,200.00 |
|
16 |
Forgary Labs |
L |
MA |
1 |
2,890.08 |
6,670.41 |
|
17 |
De Bellis Pharmacy |
P |
RI |
1 |
2,715.35 |
35,300.00 |
|
18 |
Woman & Infants |
H |
RI |
1 |
47,915.99 |
59,600.24 |
|
19 |
Depasquale Pharmacy |
P |
RI |
1 |
4,214.50 |
18,000.00 |
|
20 |
Kent Hospital |
H |
MA |
1 |
1,987.44 |
4,120.74 |
|
21 |
Butler Hospital |
H |
RI |
1 |
31,215.67 |
45,000.00 |
|
22 |
Foster Blood Tests |
L |
MA |
1 |
2,594.27 |
4,275.56 |
TOTAL 291,056.51 582,827.57
Do the following steps in Excel.
Step 1: Prepare a documentation worksheet (similar to the one you did in the previous assignment).
Step 2: Use the above data to create a worksheet called “Sales”.
Step 3: Create another worksheet, called “%Owed”, that uses the same dada as “Sales”. Add a column, titled “%Owed”, whose values represent balance owed as percentage of YTD sales.
· Color the cells yellow in “%Owed” column where the percentages are more than 50%.
· Add another column, titled “Status” and place an asterisk (*) in its cells if the balance owed is more than $10000 or a double asterisks (**) if the balance owed is more than $30000.
Strep 4 a: Copy the data in “Sales” (after Step 2) to create a new worksheet called “SummaryT”. Calculate the following and place them at the top (preferably) or the bottom of the worksheet:
Type Number of Total YTD Minimum Maximum
Customers Sales YTD Sales YTD Sales
P 6 165888.4 …… …….
H 10 ….. …… …….
L 6 …. …… …….
b. Prepare a chart to show Total YTD Sales by Type.
Step 5: Repeat step 4 for the states. Name the worksheet “SummaryS”.
Step 6: (Bonus not required) Create a new sheet, called SalesWNames that includes the data Sales worksheet with the names of reps not their numbers. Assume that the following data is given to you:
Rep Number Rep Name
|
1 |
Tom Jones |
|
2 |
John Dove |
|
3 |
Amy Jones |
|
4 |
Bill James |
|
5 |
Ann Mary |
|
6 |
Tim Gary |