Expert answer:Software Applications In Business CLASS FOR THE EX
Answer & Explanation:Excel Exercise 1(1)-2.xlsx Excel Assignment 1 Cheat Sheet.docx Excel – Exercise 1(1) (1).doc file 1, file 2, file 3 and the last one Please review the 3 files for this assignment.We will work on this assignment in-class on Wednesday, January 13th. The assignment must be submitted through blackboard by 12:00 noon on January 20th.Please attach all required files to one blackboard submission.
excel_exercise_1_1__2.xlsx
excel_assignment_1_cheat_sheet.docx
excel___exercise_1_1___1_.doc
Unformatted Attachment Preview
Worksheet Inputs
Standard Product Markup
Base Shipping Charge
Shipping Rate
$
10%
10
2%
Vendor Name
Disk Professionals
Plastic Plus
Endurance Hardware
Endurance Hardware
Endurance Hardware
Cable Ready Inc.
Endurance Hardware
Endurance Hardware
Cable Ready Inc.
Disk Professionals
Cable Ready Inc.
Cable Ready Inc.
Cable Ready Inc.
Disk Professionals
Cable Ready Inc.
Cable Ready Inc.
Disk Professionals
Disk Professionals
Disk Professionals
Cable Ready Inc.
Disk Professionals
Fastener Warehouse
Plastic Plus
Disk Professionals
Disk Professionals
Fastener Warehouse
Endurance Hardware
Fastener Warehouse
Disk Professionals
Cable Ready Inc.
Fastener Warehouse
Fastener Warehouse
Fastener Warehouse
Endurance Hardware
Regent Products
Fastener Warehouse
Regent Products
Disk Professionals
Regent Products
Regent Products
Disk Professionals
Endurance Hardware
Fastener Warehouse
Regent Products
Fastener Warehouse
Regent Products
Regent Products
Fastener Warehouse
Regent Products
Fastener Warehouse
Plastic Plus
Item#
1369
1369
5066
5066
5066
5066
6431
5066
4569
5166
5066
5166
4569
5166
5166
4569
1333
1445
5066
5166
5166
4679
1369
1346
5166
4422
6377
4569
5066
5066
9764
4778
9764
6321
1369
4698
1369
1122
1369
9764
1789
5166
5066
9632
6431
9399
4569
4111
9877
6431
1369
Item Description
Hard drives
Hard drives
Shielded Cable/ft.
Shielded Cable/ft.
Shielded Cable/ft.
Shielded Cable/ft.
Hex nut
Shielded Cable/ft.
Bolt-nut package
Electrical Connector
Shielded Cable/ft.
Electrical Connector
Bolt-nut package
Electrical Connector
Electrical Connector
Bolt-nut package
Hard drives
Hard drives
Shielded Cable/ft.
Electrical Connector
Electrical Connector
Bolt-nut package
Hard drives
Hard drives
Electrical Connector
Bolt-nut package
Hex nut
Bolt-nut package
Shielded Cable/ft.
Shielded Cable/ft.
Gasket
Bolt-nut package
Gasket
Hex nut
Hard drives
Bolt-nut package
Hard drives
Hard drives
Hard drives
Gasket
Hard drives
Electrical Connector
Shielded Cable/ft.
Gasket
Hex nut
Gasket
Bolt-nut package
Bolt-nut package
Gasket
Hex nut
Hard drives
Item Cost
$ 44.00
$
5.65
$
2.00
$
2.00
$
2.05
$
2.10
$
2.55
$
2.05
$
4.65
$
2.25
$
2.05
$
2.75
$
4.75
$
2.30
$
2.65
$
4.85
$ 55.00
$ 45.00
$
2.00
$
2.55
$
2.35
$
4.70
$
4.75
$ 45.00
$
2.25
$
4.60
$
2.50
$
4.80
$
1.95
$
2.00
$ 14.10
$
4.80
$ 14.30
$
2.45
$
4.25
$
4.80
$
4.35
$ 55.00
$
4.20
$ 13.20
$ 60.00
$
2.25
$
2.10
$ 13.50
$
3.00
$ 13.60
$
3.50
$
4.30
$ 13.80
$
3.05
$
4.85
Quantity
1,950
1,000
2,300
2,150
2,250
17,500
1,300
2,250
4,250
5,600
18,100
4,500
4,200
5,500
4,850
4,150
1,550
1,250
2,500
4,750
5,650
4,800
9,000
1,500
5,425
4,585
1,200
4,200
17,500
16,500
1,500
4,250
1,550
2,500
15,000
4,200
14,000
1,450
10,000
1,250
1,800
5,650
15,000
1,450
1,100
1,985
3,900
4,600
1,470
900
1,050
Product Cost
A/P Terms
Net 30
1%/10, Net 30
Net 30
Net 30
Net 30
1%/10, Net 30
Net 30
Net 30
1%/10, Net 30
Net 30
1%/10, Net 30
1%/10, Net 30
1%/10, Net 30
Net 30
1%/10, Net 30
1%/10, Net 30
Net 30
Net 30
Net 30
1%/10, Net 30
Net 30
Net 25
1%/10, Net 30
Net 30
Net 30
Net 25
Net 30
Net 25
Net 30
1%/10, Net 30
Net 25
Net 25
Net 25
Net 30
Net 45
Net 25
Net 45
Net 30
Net 45
Net 45
Net 30
Net 30
Net 25
Net 45
Net 25
Net 45
Net 45
Net 25
Net 45
Net 25
1%/10, Net 30
Disk Professionals
Endurance Hardware
Plastic Plus
Plastic Plus
Endurance Hardware
Endurance Hardware
Disk Professionals
Regent Products
Cable Ready Inc.
Plastic Plus
Fastener Warehouse
Endurance Hardware
Cable Ready Inc.
Cable Ready Inc.
Endurance Hardware
1788
6399
4569
5689
5689
6355
1234
5066
5689
5689
6431
5689
5689
5689
5689
Hard drives
Hex nut
Bolt-nut package
Side Panel
Side Panel
Hex nut
Hard drives
Shielded Cable/ft.
Side Panel
Side Panel
Hex nut
Side Panel
Side Panel
Side Panel
Side Panel
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
60.00
2.60
4.95
5.75
6.15
2.75
55.00
2.00
6.05
5.75
3.10
6.10
6.05
6.05
6.10
1,700
1,250
4,500
150
155
1,500
1,750
25,000
130
140
1,050
150
120
110
175
Net 30
Net 30
1%/10, Net 30
1%/10, Net 30
Net 30
Net 30
Net 30
Net 45
1%/10, Net 30
1%/10, Net 30
Net 25
Net 30
1%/10, Net 30
1%/10, Net 30
Net 30
Promised Ship Date Expected Transit Time (Days)
03/31/14
8
04/03/14
6
04/05/14
6
04/10/14
6
04/15/14
6
04/15/14
12
04/20/14
11
04/20/14
7
04/20/14
6
04/20/14
6
04/20/14
11
04/23/14
6
04/27/14
7
04/27/14
6
04/28/14
7
04/29/14
6
04/30/14
9
05/01/14
8
05/01/14
9
05/01/14
6
05/01/14
6
05/01/14
10
05/01/14
6
05/04/14
8
05/06/14
6
05/06/14
16
05/08/14
10
05/11/14
21
05/11/14
9
05/11/14
16
05/16/14
6
05/16/14
14
05/21/14
6
05/21/14
10
05/21/14
6
05/21/14
25
05/23/14
7
05/24/14
8
05/25/14
6
05/27/14
6
05/27/14
8
05/27/14
6
05/27/14
10
05/29/14
6
05/31/14
6
05/31/14
6
05/31/14
6
05/31/14
16
06/02/14
6
06/05/14
6
06/05/14
7
Actual Arrival Date
04/08/14
04/11/14
04/10/14
04/17/14
04/21/14
04/28/14
04/30/14
04/28/14
04/29/14
04/24/14
05/03/14
05/01/14
05/06/14
05/02/14
05/07/14
05/07/14
05/08/14
05/07/14
05/10/14
05/11/14
05/06/14
05/16/14
05/09/14
05/11/14
05/11/14
05/26/14
05/19/14
06/10/14
05/18/14
05/31/14
05/21/14
06/05/14
05/31/14
05/30/14
05/26/14
06/20/14
05/29/14
05/29/14
05/30/14
06/01/14
06/03/14
06/01/14
06/13/14
06/03/14
06/05/14
06/06/14
06/05/14
06/16/14
06/07/14
06/13/14
06/15/14
06/06/14
06/07/14
06/10/14
06/20/14
06/20/14
06/20/14
06/20/14
06/20/14
06/23/14
06/24/14
06/24/14
06/27/14
06/28/14
07/01/14
07/01/14
9
9
6
6
9
9
9
5
9
6
6
8
9
10
8
06/16/14
06/16/14
06/17/14
06/29/14
06/29/14
06/28/14
06/29/14
06/25/14
07/03/14
06/30/14
07/06/14
07/05/14
07/11/14
07/13/14
07/10/14
For this exercise, you will need to know how to:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Merge Cells: Home, Alignment, Merge and Center
Center Text
Insert Columns: Home, Insert
Simple multiplication formula
Absolute Addresses: Use of $ in cell addresses.
Referencing different worksheets
Sum: Home, Editing, Autosum
Sort: Data, Sort
Page Footer: Page Layout, Page Setup, Header/Footer
Filename and Path: &[Path]&[File]
Pagenum and Pages: &[Page] of &[Pages]
Reprint Headings: Page Layout, Page Setup, Sheet
Set Landscape Mode: Page Layout, Orientation
Fit Columns to one page: File Print, change “no scaling”
Filter: Data Filter
Insert Worksheet
Border / Shading: Rows to Repeat at Top: : Page Layout, Page Setup, Sheet, Print Titles
Sum, Min, Max, Average: Home, Cells (change dropdown next to AutoSum icon)
INFS 3250
In Class Project – Exercise 1
Name: ____________________________
Due: See Blackboard!
Points: ______ / 25
(2.5 pts deduction for each class period late)
Download and open the data file “Excel Exercise-1.xlsx”. Notice that there are two worksheets
within the workbook that are identified by tabs at the bottom of the window. The first tab is
called “Worksheet Inputs” and it contains some values needed for formulas that you will create.
The second tab is called Exercise-1 and it is where you will perform your operations as defined
below.
Complete the following operations on the worksheet named Exercise-1.
ON THIS AND EVERY ASSIGNMENT: Create a header for this spreadsheet with your full name
on it.
Points
Awarded
Points
Possible
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
Spreadsheet operation
Insert several rows at the top of the worksheet and create a formatted and
centered page title: ABC Company – Order & Shipping Report
Format each of the columns of the worksheet to wrap the column headings,
ensure that the contents of the cells are visible and offset the column headings
with either some shading and/or cell borders.
Insert three columns after the “Product Cost”. Title the first column:
“Shipping Costs”. Title the second column: “Order Total Cost” and title the
third column: “Projected Revenue”.
Insert a column at the end of the data. Title that column: “Days Overdue”.
Create a formula for the “Product Cost”. It is derived by multiplying “Item
Cost” by “Quantity”.
Create a formula for the “Shipping Costs”. Shipping costs are made up of two
components – a base shipping charge added to the product of the shipping rate
times the product cost. Both of those values are given on the worksheet titled
“Worksheet Inputs”. The formula for the “Shipping Costs” column must
reference the cell addresses on the “Worksheet Inputs” worksheet – and use
absolute cell addressing. Also pay attention to the order of math
operations in your formula.
Create a formula for the “Order Total Cost”. It is derived by adding the
“Product Cost” and the “Shipping Costs”.
Create a formula for the “Projected Revenue”. It is calculated by multiplying
the “Product Cost” times the “Standard Product Markup” given on the
worksheet titled “Worksheet Inputs”
Create a formula for the “Days Overdue”. “Days Overdue” equals the
“Promised Ship Date” plus the “Expected Transit Time” (which yields when
we think the shipment will arrive) less “Actual Arrival Date”. Be sure that the
result is reflected in days.
_______
_______
_______
(1)
Add totals at the bottom of the spreadsheet for the appropriate columns.
(1)
Apply all necessary formatting to the worksheet to display it in a concise and
meaningful way.
(1)
Sort the data by “Vendor Name” and then by “Item#” (within each Vendor).
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
_______
(1)
Insert a page footer to print page numbers and the saved file location at the
bottom of each page. Create a header that has your name.
In case the printed document spans more than one page vertically – set the top
column headings to reprint on each subsequent page.
Force the document to print all the columns on a single page in Landscape
mode. It should span two pages in length – but all columns must appear on a
single page. Also, set the headings to redisplay on subsequent pages. Adjust
settings to ensure that the printed view is a reasonable size font and layout.
Print a PDF of the formatted worksheet for submission. Name the output
“Excel Exercise 1 – (yourname) – sheet 1”– submit to blackboard.
Change the “Standard Product Markup” to 12% and Change the “Base
Shipping Charge” to $12.50 on the Worksheet Inputs worksheet. (Do not
change any other formulas.) Print a PDF of the formatted worksheet for
submission. Name the output “Excel Exercise 1 – (your name) – base charge
increased”– submit to blackboard.
Apply the AutoFilter to the worksheet. Select to display the rows with Item#
equal to “5166”. Print a PDF of the formatted worksheet for submission.
Name the output “Excel Exercise 1 – (your name) – autofilter”– submit to
blackboard.. Redisplay all rows before going to the next step.
Insert a blank worksheet. Title the worksheet (tab) “Order Summary”.
Create a summary block of information on that worksheet. Include the
following:
A count of all orders, the total, minimum, maximum and average
values for each of the following: “Product Cost”, “Shipping Costs”,
“Projected Revenue” and “Days Overdue”. You must use the COUNT,
SUM, MIN, MAX, and AVERAGE functions. It should look similar to
the following:
Order Summary
_______
(4)
Product Cost
Shipping Costs
Projected Revenue
Days Overdue
Total Minimum
xxx
xxx
xxx
xxx
xxx
xxx
xxx
xxx
Number (Count) of orders
_______
(1)
_______
(1)
Maximum
xxx
xxx
xxx
xxx
Average
xxx
xxx
xxx
xxx
xxx
Format the Order Summary to present the data with titles, borders, shading
and/or any other helpful visual clues for reading the summary report.
Print a PDF of the Summary worksheet for submission. Name the output
“Excel Exercise 1– (your name) – Summary”.
Save your finalized document and post it to the Blackboard web site. I will not accept work
attached to an e-mail. It is your responsibility to properly post your completed work for full
credit. Type your name on the top of this form and turn it in as a vehicle to communicate your
graded project (Submit this sheet as a word document, not as a PDF). Work is only considered to
be submitted on time if you posted the completed file to blackboard.
For this project, remember, you must submit:
“Excel Exercise 1 – (yourname) – sheet 1”
“Excel Exercise 1 – (your name) – autofilter”
“Excel Exercise 1 – (your name) – Summary”
And THIS document, but titled, “Excel Exercise 1 – (your name) – Rubric”
Please ensure that all 4 documents are attached to the same submission attempt in Blackboard.
This assignment is due
…
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