Expert answer:Excel question
Expert answer:please follow the instruction PDF to finish the two assignment!
follow the requirements listed on the next page.
You must not use Excel’s built-in functions for solving this assignment.
Use formulas to complete the Caterpillar, Trips and Formulas worksheets
of this assignment using the information listed on the next pages.
Enter your name on cell H1 of each of three worksheets.
Submit your assignment9.xlsx le to Sakai using the Sakai-> Assignments
Thank you
assignment9.xlsx
assignment9_instructions.pdf
assignment10.xlsx
assignment10_instructions_2.pdf
Unformatted Attachment Preview
IT products
Taxes
Discount
7%
3%
Expense
factor
60%
IT Products – Income Statement
Florida
Desktops
Laptops
Servers
Revenue
Expenses
Gross Profit
Taxes
Net Profit
New Jersey
New York
2000
7000
9500
3000
3500
6000
5000
12000
15000
Page 1
(in thousands of US$)
Car Sales
Car Sales – 2016
Number of Cars Sold
Make
Mercedes Benz
Tesla
Toyota
GM
Ford
Quarter 1
9,000
3,000
17,000
34,000
29,000
Quarter 2
12,000
2,000
18,000
26,000
35,000
Quarterly Totals
Page 2
Quarter 3
11,000
5,000
27,000
25,000
32,000
Quarter 4
8,000
7,000
28,000
32,000
29,000
Yearly % of
Totals Total
Formulas
Formulas
Volume of a sphere
radius
Pi
Volume
7
3.1416
Period of pendulum
Pi
Length
gravity
3.1416
17
9.8
Volume of a pyramid
Base
Height
180
95
Future Value
Cash
rate
number of periods
Angular Motion
t1
t0
w
a
2000
0.08
12
7
2
1.5
7.23
Page 3
CS170 – Computer Applications for Business
Fall 2017 • Assignment 9
Due Date:
Before 11:55 p.m. on Friday, December 1st, 2017
Accept Until:
Before 11:55 p.m. on Friday, December 8th, 2017
Evaluation:
15 points
Submit to Sakai:
assignment9.xlsx file
Learning Objective:
This assignment is designed to introduce you to Spreadsheets using MS
Excel. For this assignment, you will upload your Excel file to Sakai so that it
can be accessed by just clicking on the file’s link. The main topics exercised
in this assignment are: formulas, formatting and charting.
To get credit for this assignment:
1 Deliver the assignment9.xlsx file to Sakai on time.
2 Your TA should be able to open your file by clicking on its link.
Directions:
Follow the requirements listed on the next page.
You must not use Excel’s built-in functions for solving this assignment.
Use formulas to complete the Caterpillar, Trips and Formulas worksheets
of this assignment using the information listed on the next pages.
Enter your name on cell H1 of each of three worksheets.
Submit your assignment9.xlsx file to Sakai using the Sakai-> Assignments
link.
Page 1 of 4
Requirements:
1) IT Products worksheet
Use the necessary formulas to calculate the appropriate values for Florida, New Jersey
and New York (the cells where the formulas should be inserted are highlighted in yellow
on the spreadsheet). Use absolute or relative reference as needed.
a) Revenue: addition of the income values for each state.
b) Expenses: Revenue multiplied by the appropriate expenses factor as listed in the
upper left data section.
c) Gross Profit: Revenues minus Expenses.
d) Taxes: Gross Profit multiplied by the Taxes factor on the upper left section of the
spreadsheets.
e) Net Profit: Gross Profit minus Taxes.
Charts:
g) Create a 3D Pie chart based on the Net Profit with the names of the states as labels.
h) Apply Style 3 and Layout 2 to the chart.
i) Enter Net Profit as title for the chart.
j) Create a 2D Clustered Column chart to display Revenue and Gross Profit. Include the
state names and the Revenue and Gross Profit labels.
k) Apply Style 3 and Layout 3 to the chart.
l) Enter Revenue vs. Gross Profit as title for the chart
Formatting: Apply Accounting format to the sales numeric data. Apply Total style to the
Net Profit data.
2) Car Sales
Use the necessary formulas to calculate the following:
a) Yearly Totals: Totals per row (by Car Make)
b) Quarterly Totals: Totals per columns (by Quarter)
d) Apply the Title cell style to the title: Car Sales – 2016.
e) Apply Heading 1 cell style to the subtitle: Number of Cars Sold.
f) Apply the 20% – Accent5 cell style to the car make labels.
Page 2 of 4
g) Apply the 20% – Accent6 cell style too the headers on row 4.
h) Apply the Total cell style to the numeric totals on row 10.
i) % of Total: Yearly total for each manufacturer divided by the sum of all the yearly
totals.
Charts
Pie Chart:
-Create a 3D Pie Chart (Pie in 3D) of the GM sales (excluding Yearly Totals).
-Select Chart Layout 2
-Select Chart Style 3
-Enter as chart title: GM Sales
Column Chart:
-Create a Column Chart (3D Column: 3D Clustered Column) of GM and Ford sales
(excluding Yearly Totals)
-Show the Quarters in the horizontal axis
-Select Chart Layout 1
-Select Chart Style 3
-Enter as chart title: GM vs. Ford
Line Chart:
-Create a Line Chart (3D Line) for the Quarterly Totals on row 10 (excluding Yearly
Totals)
-Show the Quarters in the horizontal axis
-Select Chart Layout 4
-Select Chart Style 4
Formattting: Apply Percentage format (with one decimal) to the numbers in the % of
Total column.
Page 3 of 4
3) Formulas spreadsheet
Enter a formula in the cells indicated – yellow background – using the formulas listed
below. (Note: the dots indicate multiplications)
(4 /3)⋅π⋅r
1. Volume of a sphere:
2. Period of a pendulum:
3. Volume of Pyramid:
3
2⋅π⋅√ ( L/ g)
2
(1/ 3)⋅B ⋅H
4. Future value of a present amount:
5. Angular motion:
n
C⋅(1+r)
2
w .(t 1−t 0)+(1/2). a .(t 1−t 0)
References:
1. Chapter 13 of the Fluency6 textbook
2. Recitations and Lectures
Page 4 of 4
Hotel
Information
Standard
room rate
Suite rate
Hotel Tax
Sales Tax
Tip
Construction
Fee
Summary
$
$
190
300
Highest Total Bill
Lowest Total Bill
5.00%
8.25%
10.00%
Highest Length of Stay
Lowest Length of Stay
3.00%
Average Length of Stay
Guest ID
Room
Category
G101
G102
G103
G104
G105
G106
G107
G108
G109
G110
G111
G112
G113
G114
G115
G116
G117
G118
G119
G120
G121
Suite
Suite
Standard
Suite
Standard
Standard
Standard
Standard
Standard
Suite
Standard
Suite
Standard
Standard
Standard
Standard
Suite
Suite
Suite
Standard
Standard
Room
Number
Stay
(nights)
201
202
203
204
205
501
207
502
209
210
503
302
303
505
305
504
307
507
309
508
402
Room
Charge
1
8
8
3
9
2
7
9
5
5
2
3
4
8
1
10
4
7
4
3
10
Page 1
Room
Service Restaurant Bill
90
88
65
36
25
41
91
23
48
66
75
77
95
63
59
66
20
36
70
49
92
512
204
610
470
778
804
378
382
660
782
305
204
865
983
425
854
977
111
434
449
901
Hotel
ength of Stay
Bill Amount Hotel Tax
Sales Tax
Construction
Surcharge
Page 2
Tip
Total Bill
Amount
Taxicab4U
Initial Charge
Charge per mile
Charge per minute
Charge per gallon
Night Surcharge
TripID
501
502
503
504
505
506
507
Membership Data
Categories
Discount
Bronze
15%
Gold
20%
Silver
25%
$2.50
$3.00
$0.50
$0.30
$2.00
Member
Bronze
Silver
Gold
Bronze
Bronze
Silver
Silver
Miles
10
12
32
41
9
3
21
Time (minutes) Fuel (gallons)
7
0.28
6
0.29
43
1.2
50
1.9
10
0.12
5
0.11
15
0.93
Page 3
Time
Day
Night
Night
Night
Day
Day
Night
Taxicab4U
Total $
Member
Discount $
Net Total $
Additional
Discount $
Page 4
Additional
Charges $
Net Fare $
F1 Constructors
Season
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
Country
United Kingdom
United Kingdom
United Kingdom
Italy
United Kingdom
United Kingdom
Italy
United Kingdom
United Kingdom
United Kingdom
United Kingdom
France
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
Italy
Italy
Italy
United Kingdom
Italy
United Kingdom
United Kingdom
Italy
Italy
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
United Kingdom
Italy
Italy
Italy
Italy
Italy
Italy
France
Constructor
Vanwall
Cooper
Cooper
Ferrari
BRM
Lotus
Ferrari
Lotus
Brabham
Brabham
Lotus
Matra
Lotus
Tyrrell
Lotus
Lotus
McLaren
Ferrari
Ferrari
Ferrari
Lotus
Ferrari
Williams
Williams
Ferrari
Ferrari
McLaren
McLaren
Williams
Williams
McLaren
McLaren
McLaren
McLaren
Williams
Williams
Williams
Benetton
Williams
Williams
McLaren
Ferrari
Ferrari
Ferrari
Ferrari
Ferrari
Ferrari
Renault
Engine
Vanwall
Climax
Climax
Ferrari
BRM
Climax
Ferrari
Climax
Repco
Repco
Ford
Ford
Ford
Ford
Ford
Ford
Ford
Ferrari
Ferrari
Ferrari
Ford
Ferrari
Ford
Ford
Ferrari
Ferrari
TAG
TAG
Honda
Honda
Honda
Honda
Honda
Honda
Renault
Renault
Renault
Renault
Renault
Renault
Mercedes
Ferrari
Ferrari
Ferrari
Ferrari
Ferrari
Ferrari
Renault
Page 5
Tyre
Dunlop
Dunlop
Dunlop
Dunlop
Dunlop
Dunlop
Dunlop
Dunlop
Goodyear
Goodyear
Firestone
Dunlop
Firestone
Goodyear
Firestone
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Michelin
Goodyear
Goodyear
Goodyear
Goodyear
Michelin
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Goodyear
Bridgestone
Bridgestone
Bridgestone
Bridgestone
Bridgestone
Bridgestone
Bridgestone
Michelin
Poles
Wins
5
5
4
6
1
7
2
6
3
2
5
2
3
6
3
10
2
9
4
2
12
2
3
2
3
8
3
2
4
12
15
15
12
10
15
15
6
4
12
10
12
3
10
11
10
8
12
7
6
5
6
5
4
7
3
6
4
4
5
6
6
7
5
7
4
6
6
4
8
6
6
4
3
4
12
6
9
9
15
10
6
8
10
10
7
11
12
8
9
6
10
9
15
8
15
8
F1 Constructors
2006
2007
2008
2009
2010
2011
2012
2013
2016
2014
2015
France
Italy
Italy
United Kingdom
Austria
Austria
Austria
Austria
Germany
Germany
Germany
Renault
Ferrari
Ferrari
Brawn
Red Bull
Red Bull
Red Bull
Red Bull
Mercedes
Mercedes
Mercedes
Renault
Ferrari
Ferrari
Mercedes
Renault
Renault
Renault
Renault
Mercedes
Mercedes
Mercedes
Michelin
Bridgestone
Bridgestone
Bridgestone
Bridgestone
Pirelli
Pirelli
Pirelli
Pirelli
Pirelli
Pirelli
7
11
8
5
15
18
8
11
20
18
18
8
9
8
8
9
12
7
13
19
16
16
Conditional formatting (use formulas inside Conditional Formatting)
a) Apply yellow fill to the Seasons where the ratio between Podiums and Wins is greater than 2
b) Apply red fill to the Countries with more than 10 Poles
Statistical Data
1
2
3
4
5
Average Wins for McLaren constructor with Honda engines
Number of Years with Wins less than 10 and Margin greater than 15
Average Podiums for United Kingdom, with Fastest Laps greater than 3, and Points gr
Total Poles for Dunlop or Goodyear tyres
Total Podiums for Ford engine and Goodyear tyres
Page 6
F1 Constructors
Podiums
Fastest Laps
9
13
14
14
8
9
10
7
9
14
9
10
7
11
8
15
10
11
13
16
14
13
18
13
11
12
18
12
19
18
25
18
18
18
21
22
13
15
21
15
20
17
21
24
27
16
29
18
3
5
5
5
3
6
2
6
2
2
5
6
1
4
4
7
1
6
7
3
7
6
5
7
2
3
8
6
11
7
10
8
5
4
11
10
8
8
11
9
9
6
5
3
12
8
14
3
Points
48
40
48
45
42
54
45
54
42
63
62
66
59
73
61
92
73
72.5
83
95
86
113
120
95
74
89
143.5
90
141
137
199
141
121
139
164
168
118
137
175
123
156
128
170
179
221
158
262
191
Margin
8
8
14
10
6
18
3
9
11
19
13
17
7
37
10
10
8
18.5
9
33
28
38
54
34
5
10
86
8
45
61
134
64
11
14
65
84
15
25
105
21
23
4
18
77
129
14
143
9
Page 7
F1 Constructors
19
22
19
15
20
28
14
24
33
31
32
5
11
13
4
6
10
7
12
9
11
13
206
204
172
172
498
650
460
596
765
701
703
5
103
21
18.5
44
153
60
236
297
296
275
Wins is greater than 2
n greater than 15
Laps greater than 3, and Points greater than 45
Page 8
CS170 – Computer Applications for Business
Fall 2017 • Assignment 10
Due Date:
Before 11:55 p.m. on Friday, December 8th, 2017
Accept Until:
Before 11:55 p.m. on Friday, December 11th, 2017
Evaluation:
15 points
Submit to Sakai:
assignment10.xlsx file
Learning Objective:
This assignment is designed to provide additional practice to gain proficiency
in the use of Spreadsheets Functions including Math and Statistical Functions
using MS Excel. For this assignment, you will upload your Excel file to Sakai
so that it can be accessed by just clicking on the file’s link.
To get credit for this assignment:
1 Deliver the assignment10.xlsx file to Sakai on time.
2 Your TA should be able to open your file by clicking on its link.
Directions:
Follow the requirements listed on the next page.
Use the appropriate functions and/or formulas on the spreadsheets
provided as indicated.
Enter your name on cell H1 of each of three worksheets.
Submit your assignment10.xlsx file to Sakai using the Sakai->
Assignments link.
Page 1 of 4
Requirements:
1) Hotel worksheet
Use the necessary functions and/or formulas to calculate the following:
a) Room Charge: equal to Length of Stay multiplied by Room Rate. The Room Rate is
indicated in the Information section (Upper left side) and is $190 if the room is
Standard or $300 for the other rooms (Suite rooms).
b) Bill Amount: equal to Room Service plus Restaurant Bill plus Room Charge.
c) Hotel Tax: Bill amount multiplied by Hotel Tax.
d) Sales Tax: Bill amount multiplied by Sales Tax.
e) Construction Surcharge: This surcharge helps to pay for the expenses incurred for
the construction of the 5th floor. It is equal to the Construction Fee multiplied by the Bill
Amount if the room is numbered 500 or greater. For other rooms the surcharge is 0.
f) Tip: equal to Room Service by Tip Percentage.
g) Total Bill Amount: equal to Bill Amount plus Hotel Tax plus Sales tax plus
Construction Surcharge plus Tip.
h) Summary Section: complete the summary section with the appropriate functions
based on the labels of those cells.
2) Taxicab4U worksheet:
Taxicab4U is a taxi company that needs to calculate the net fare to be charged to their
customers. Several factors play a role in the calculation of the fare. Those factors are
listed on the upper left side of the spreadsheet. Additionally, a discount is given to the
customers based on their membership level.
Use the necessary functions and/or formulas to calculate the appropriate values for
the cells highlighted in yellow on the spreadsheet. Use absolute or relative reference as
needed.
a) Total $: obtained by multiplying the miles, time, fuel values by their corresponding
factors on the upper left corner of the spreadsheet and then adding up the resulting
dollar amounts
Page 2 of 4
b) Member Discount $: calculated by multiplying the Total $ by the percentage
discount accorded to each member based on the corresponding membership level
listed on the Membership data table.
c) Net Total $: Total minus Member Discount $.
d) Additional Discount $: if the numbers on the miles, time, fuel columns add up to 50
or more for a customer then the customer gets a $1 discount. Otherwise the customer
gets no discount.
e) Additional Charges $: All of customers get charged an Initial Charge. Only those
who had a night ride get charged a Night Surcharge. The dollar amounts for these
charges are listed in the upper left corner of the spreadsheet. Both charges added up
make up the Additional Charges $.
f) Net Fare $ : Net Total $ minus Additional Discount $ plus Additional Charges $.
3) F1 Constructors worksheet
Complete the blue colored cells – at the bottom of the worksheet – with the appropriate
Math and Statistical function or formula using such functions.
Apply also the Conditional Formating indicated on the F1 Constructors worksheet by
selecting the appropriate option that allows the use of a formula or function from inside
the Conditional Formatting functionality. [Note: Those using Apple machines might need
to use the Classic option of Conditional Formatting].
Page 3 of 4
References:
1. Chapter 13, 14 of the Fluency6 textbook
2. Recitations, Lectures and Online resources.
Page 4 of 4
…
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