Expert answer:Software Applications In Business, CLASS FOR THE E
Answer & Explanation:Here are the filesexcel___exercise_2_1__2.docexcel_exercise_2_1__2.xlsx
excel___exercise_2_1__2.doc
excel_exercise_2_1__2.xlsx
Unformatted Attachment Preview
INFS 3250
Exercise 2 – IF( ), CountIF( ) & Data Validation
Name: ____________________________
Due: See Blackboard!
Points: ______ / 25
(2.5 pts deduction for each class period late)
This assignment requires the proper use of absolute, relative and mixed cell addressing, the IF() and
CountIF() functions, and the proper use of data validation and worksheet protection.
Download the Excel workbook file: Excel Exercise 2.xlsx. There are three worksheets imbedded –
perform the functionality as described below.
You will be graded on the following:
______ 1. 2 pts. On worksheet Students1: Use the IF function to evaluate and identify if the
student is on probation.
______ 2. 3 pts. In the area to the right of the Student Data, create a table that summarizes the
Student Rankings on the worksheet. Use column headings. Use the CountIF() function to count
how many students are of each rank and provide a total at the bottom of your table. Use cell
addresses for all parts of the CountIF() formula. If you do it properly, you should be able to
write the CountIF() formula once and copy it down for the other 3 “Ranks”. The summary table
will look similar to the following:
Rank
Count
FR
SO
JR
SR
Totals
xxx
xxx
xxx
xxx
xxx
______ 3. 4 pts. Select the IncomeStatement worksheet. Write the formulas required to complete
the projected income statement using the data from the assumptions area of the spreadsheet.
Formulas are defined within the worksheet. Use named Ranges as appropriate.
______ 4. 3 pts. On worksheet IncomeStatement: Use the IF function to calculate the required
taxes on Gross Income for the quarter. Use cell addresses or named ranges for each part of the
IF( ) function.
______ 5. 3 pts. Use absolute and relative cell addressing properly to achieve the instruction:
“Create a column of formulas for Quarter 1 that will allow you to copy and paste the formulas to
the columns for Quarters 2, 3 and 4 without changing any part of the formulas”.
______ 6. 2 pts. Format the data and print only the Income Statement portion of the
spreadsheet and attach it to your Grading Sheet. Provide a report title centered across the
Income Statement. Add any other formatting that will make the report easier to read.
______ 7. 5 pts. Select the ExpenseForm worksheet. Add data validation that requires the User to
enter a team value that is between 5 – 8 characters in length. Add validation that allows the user
to select the appropriate G/L account number from a list (as listed in L27, L28 and L29). Create
meaningful input prompts for the user as well as clear error messages in the data validation
wizard.
______ 8. 3 pts. Lock the cells on any part of the Expense form that you would not want a user to be
able to alter (such as the mileage and meal allowance rates or any calculated formulas, headings
or instructions on the worksheet). Do not lock down cells that users would need access to when
entering their expense data. Apply the locks by Protecting the worksheet – using the password
iLoveExcel.
Save your Excel file and upload it to the Blackboard site. Write your name on this
document, attach the required prints and turn it in to me (or drop in into the folder outside
my door), as a vehicle to prompt and communicate your grade.
Instructions: Work with the IF function — add a column titled “Probation”.
Test the value of the student’s major — if the GPA is lower than 2.5,
display the word “Probation” in the column, otherwise display nothing (blanks).
Create a summary (to the right of the data) of the Student Rankings using the CountIF function.
Best solutions will use cell addresses for all parts of the formula
You don’t need to print this worksheet. I will grade from within your posted file.
Student #
99912348
99393943
99393975
99655983
99352761
99974647
99083766
99796619
99393970
99686022
99393962
99881542
99630877
99017479
99393966
99393943
99393944
99625129
99699395
99534889
99604061
99393967
99393958
99813175
99837519
99393952
99393971
99393945
99223080
99393964
99036578
99393965
99129206
99393960
99393963
99393959
99393969
99660130
99393961
Last Name
Ackerman
Adams
Arston
Aziz
Bertin
Booth
Bose
Bows
Brewski
Brown
Cameron
Chan
Cooper
DeFleur
Drummond
Earle
Edsell
Farris
Flanders
Garcia
Getz
Goode
Gray
Guerroro
Hoyer
Hamel
Hedges
Henderson
Hendrickson
Hillman
Horton
Huberty
Hurley
Lee
Lemiski
Lewis
Lockwood
Lopez
MacPherson
First Name
Jerry
Cecilia
Jean
Nashir
Julia
Wesley
Denise
Graham
Randy
Raymond
Jacqueline
Dorothy
Sara
Gregg
Chris
Jenna
Camilla
Linda
Dawn
Rosa
Anthoney
Barb
Stacey
Tamara
Brian
Darryl
John
Kela
Mel
Frances
Samantha
Tessa
Stuart
Jerri
Ryan
Nathan
Shaine
Victor
Jack
Address
748 Douglas Cresent
#110-9022 64th Street
190 Greer Rd.
894 Regent Drive
32-987 Parkdale Road
388 Harrington Avenue
3210 Hemlock Avenue
5746 Rose Hill Road
1201 Broadway
392 Greenfield St
151 Greer Rd.
11928-14th Street
541 Victoria Drive
2090 Fairway Drive
14489 3rd Ave.
8740 Thrup St
200 Union St.
177 Sunshine Parkway
392 Meadow Drive
3302 Arvida Drive
8493 McArthur St
1485 Sonama Way
20 Cactus Lane
329 Delnor Cresent
310-293 Rue Verde
100 Bosley Lane
6144 Silver Star Rd.
111 Union St.
23-984 Victoria Drive
540 Cactus Lane
940 32 Avenue
1010 Tower Plaza
4033 Selkirk Way
101 West 6th Ave.
220 Main St.
4500 Kalview Place
190 Greer Rd.
5984 East Hill
468 Michael Drive
Phone
419-555-1375
419-825-5661
419-692-3101
419-555-2199
419-555-3189
419-555-2918
419-555-1450
419-555-4156
419-882-3567
419-555-3981
419-660-9845
419-555-4451
419-555-4886
419-555-9906
419-321-6580
419-555-9411
419-882-1147
419-555-9313
419-555-8821
419-555-2978
419-555-8163
419-654-1234
419-123-4546
419-555-7134
419-555-1328
419-321-4567
419-654-9870
419-882-9374
419-555-8802
419-315-2255
419-505-9803
419-315-3002
419-555-7988
419-315-9874
419-650-5522
419-315-6525
419-660-6655
419-555-4853
419-882-9545
99393977
99063698
99881015
99393953
99393978
99393957
99494467
99283926
99149177
99362225
99393946
99685412
99393956
99393973
99393976
99393949
99393974
99393955
99393968
99393950
99393954
99393951
99638446
99393947
99393972
99035674
99393948
Maki
McLean
McLeish
Morgan
Morris
Mulvaney
Murray
Palma
Pitt
Provost
Schuler
Singh
Skubiak
Smith
Suel
Swift
Tanaka
Tang
Tarlton
Thompson
Todd
Walsh
Williams
Williams
Winkler
Withers
Yap
Ed
Barb
Cindy
Cindy
Alice
Marcey
Dwayne
Emerald
Dennis
Everett
Presley
Balwant
Jaime
Albert
Ingvar
Gilles
Brian
Kim
Victor
Murray
Shig
Moira
Peter
Rich
Janice
Martin
Stevey
1321 Cordova Ave.
3201 Sun Valley
21-909 Taylor Way
110 Glen Vista
3710 Bush St.
2219 Hilltop Way
8893 Thrup Street
84 Columbia
280-4993 Lodgepole Place
22A-8930 Cotton Place
1091 Panorama Ridge
3987 Valleyview Street
400 Eastlake Trail
550 Montgomery St.
235 Johnston St.
4210 Bush St.
298 Primore St.
3109 East 5th Ave.
17 Windy Way
4957 Cordova Ave.
2110 Hilltop Way
#17-1120 Main Street
490 Malahat Drive
1789 East 17th Ave.
100 Greer Rd.
3984 Hudson Blvd
15201 Johnston Rd.
419-645-3352
419-555-8332
419-555-7287
419-654-6585
419-660-9985
419-654-3322
419-555-3327
419-555-2876
419-555-1982
419-555-6360
419-654-1085
419-555-7330
419-660-3255
419-882-3337
419-132-6511
419-512-7411
419-654-3658
419-315-1123
419-654-4560
419-643-7744
419-882-3365
419-660-2895
419-555-5576
419-660-0767
419-650-3200
419-555-6387
419-654-3860
hing (blanks).
Probation Threshhold
nkings using the CountIF function.
ur posted file.
Major
English
Business Admin
Business Admin
Geography
Geography
History
Sciences
Geography
Business Admin
Agriculture
Health & Human Svcs
Business Admin
Business Admin
Business Admin
Business Admin
Computer Science
Business Admin
Sciences
Geography
History
Fine Arts
Health & Human Svcs
Education
Computer Science
Business Admin
Health & Human Svcs
Education
Business Admin
Agriculture
Business Admin
Geography
Business Admin
Sciences
Health & Human Svcs
Business Admin
Business Admin
Education
Sciences
Health & Human Svcs
GPA
2.7
2.1
3.8
2.1
3.6
1.8
2.8
3.4
3.6
2.7
2.7
2.5
3.3
3.5
3.4
2.8
3.9
3.2
3.3
2.5
2.6
2.7
3.4
3.8
3.3
3.3
3.4
2.9
2.3
1.9
2.5
3.3
3.6
2.5
3.8
3.4
3.6
1.9
2.7
Rank
JR
JR
SR
SO
SR
JR
JR
FR
SR
SR
JR
SR
SO
JR
SO
JR
SO
SO
JR
SO
SR
SR
SR
JR
SO
SR
SR
JR
FR
SR
FR
SR
JR
SR
SO
SO
SR
SR
SO
2.5
Business Admin
Fine Arts
English
Health & Human Svcs
Health & Human Svcs
Education
History
Computer Science
English
Computer Science
Business Admin
History
Education
Education
Education
Education
Health & Human Svcs
Health & Human Svcs
Health & Human Svcs
Business Admin
Health & Human Svcs
Business Admin
Fine Arts
Business Admin
Business Admin
History
Business Admin
2.7
1.7
3.5
2.9
2.8
3.9
2.9
3.9
3.8
2.5
2.1
2.8
3.3
1.8
2.5
1.9
1.8
1.9
3.6
3.6
2.5
1.9
3.5
2.5
2.8
4.0
2.5
JR
SO
JR
SO
JR
SR
FR
FR
SR
JR
JR
SO
SO
FR
FR
SR
SO
FR
FR
SO
SR
FR
SR
SR
FR
SO
SR
Assumptions
Sales Growth
Sales Commissions
Variable Costs
Fixed Costs
Tax Bracket on Gross Profit <=
Tax Bracket on Gross Profit >
Base Sales (Q0)
Product 1
Product 2
Product 3
$
$
$
250,000.00
250,000.00
$
$
$
118,000
65,000
82,500
Base Sales
(Qtr-0)
Sales Revenue
Product 1
$
118,000
Product 2
$
65,000
Product 2
$
82,500
Total Sales
Less Variable Costs
Less Sales Commission
Less Fixed Costs
Total Expenses
Gross Profit
Less Taxes
Net Income
Qtr-1
10%
15%
20%
17,500 $
Qtr-2
12%
17%
22%
17,500 $
Qtr-3
14%
19%
18%
20,000
28%
33%
Qtr-1
Qtr-2
Qtr-3
$
Qtr-4
18%
22%
20%
21,500
Qtr-4
Annual Totals
INSTRUCTIONS
Create a column of formulas for Quarter 1 that will allow you
to copy and paste the formulas to the other quarters without change
— using correctly, absolute, relative and mixed cell addressing.
Product Sales for each quarter is computed as :
((product sales for the previous Qtr) * (100% + Sales Growth% for that Qtr)
Total Sales for each quarter is computed as :
(sum of the sales for Products 1, 2 and 3)
Variable costs is computed as a percentage of sales:
(total sales * variable costs percentage)
Sales Commission is computed as a percentage of sales:
(total sales * sales commission percentage)
Fixed costs — remains constant over all periods
Gross Profit = total sales less total expenses
Tax is computed as Gross Profit * Tax Bracket (use IF( ) function )
if Gross Profit <= $250,000: Tax = Gross Profit * 28%
if Gross Profit > $250,000: Tax = Gross Profit * 33%
Use Cell addresses or Named Ranges for each part of the formula.
Net Income = Gross Profit less taxes
Hide the Base Sales (Q0) numbers – so that they are not visible in the completed
income statement. And – be sure to calculate quarterly sales by Product
Format the data — in such a way that the income statement is easy to read.
Provide a report title centered across the income statement and print only
the Income Statement portion of this worksheet.
The ABC Company
Travel – Reimbursement Expense
Name (Payee)
Address
City, State Zip
provide mailing address if not on file
Date of
Departure
Date of
Return
Team
Destination & Purpose of Travel
Hotel
$
–
Notes / Additional Info:
SUBMITTED By:
REVIEWED By:
APPROVED By:
Instructions: List each travel event separately. Only include on this document those expenses paid for and reimburseable to
Original receipts must be submitted to support each expense itemized above.
Receipts for club (direct) paid expenses should be submitted on a Credit Card Receipt transmittal form.
Requests for reimbursement of expenses submitted without original receipts for amounts over $10.00
may, at the club’s discretion, be denied.
Expenses over 60 days old, by IRS law, will be treated as taxable income and reported on W2’s or 1099’s.
BC Company
ursement Expense Form
Meals or
Per Diem
Car Rental
$
–
$
–
Round Trip
Mileage
Per Diem Rate
$
25.00
Breakfast
$
5.00
Lunch
$
10.00
Dinner
$
10.00
Mileage Rate
$
0.35
Mileage
Allowance
Tolls
Total
G/L Account
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
Subtotal $
–
$
–
Less any
Advance
DATE:
Total $
–
DATE:
DATE:
ses paid for and reimburseable to you.
ard Receipt transmittal form.
s for amounts over $10.00
Summary of G/L Accts
League: 5100.00
Tourney: 5110.00
Other: 5120.00
nd reported on W2’s or 1099’s.
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades