The assignment is reading the excel instructions and completing them in the excel workbook as stated in the instructions. Basic stuff, change fonts, layouts, themes, sums, auto fits etc. Thank you,
ex2013_workbook.xlsx
excel_2013_instructions.docx
Unformatted Attachment Preview
Top’t Corn Sales (July)
Truck Location
Farragut Square George Washington Georgetown K Street
Old Bay
1900
800
700
950
truffle
2050
450
900
1870
Sea Salt and Caramel
3450
1400
1150
2100
Price
Loan term (in months)
Interest (annual)
$52.000,00
12
9%
Monthly payment
Total payments
Interest paid
$4.547,48
Average sales
$
4.430,00
Date
Old Bay
1-Jul
2-Jul
3-Jul
4-Jul
5-Jul
6-Jul
7-Jul
8-Jul
9-Jul
10-Jul
11-Jul
12-Jul
13-Jul
14-Jul
15-Jul
16-Jul
17-Jul
18-Jul
19-Jul
20-Jul
21-Jul
22-Jul
23-Jul
24-Jul
25-Jul
26-Jul
27-Jul
28-Jul
29-Jul
30-Jul
31-Jul
42
42
105
141
137
68
82
125
20
67
121
140
147
86
50
80
41
26
93
84
25
107
116
46
44
112
73
100
55
137
125
Black Truffle Sea Salt and Caramel Daily Total (# sold) Daily Total ($)
115
92
249
42
117
201
124
64
293
83
76
300
73
109
319
44
60
172
58
145
285
96
92
313
26
39
85
38
21
126
124
132
377
76
25
241
77
77
301
128
59
273
122
96
268
92
139
311
69
59
169
38
125
189
41
133
267
144
146
374
124
145
294
60
142
309
115
128
359
129
145
320
112
99
255
96
31
239
102
97
272
89
109
298
30
75
160
117
97
351
39
80
244
Sales Goal Met?
Current price per box:
Daily sales goal:
Average daily sales:
Lowest daily sales:
Highest daily sales:
$5
$1.250
Online Sales During Buy 1 Get 1 Free Sale
Date
Quantity
Item
6/30/2014
6 Old Bay
6/30/2014
12 Original Blend
6/30/2014
4 Original Blend
6/30/2014
8 Sea Salt and Caramel
6/30/2014
5 Sea Salt and Caramel
6/30/2014
8 Truffle
6/30/2014
2 Truffle
6/30/2014
2 Original Blend
6/30/2014
4 Old Bay
6/30/2014
14 Truffle
6/30/2014
8 Sea Salt and Caramel
6/30/2014
10 Original Blend
6/30/2014
2 Original Blend
6/30/2014
4 Old Bay
6/30/2014
2 Old Bay
6/30/2014
4 Sea Salt and Caramel
7/1/2014
2 Old Bay
7/1/2014
2 Sea Salt and Caramel
7/1/2014
5 Truffle
7/1/2014
10 Original Blend
7/1/2014
5 Old Bay
7/1/2014
2 Original Blend
7/1/2014
2 Sea Salt and Caramel
7/1/2014
4 Original Blend
7/1/2014
2 Old Bay
7/1/2014
4 Old Bay
7/1/2014
8 Old Bay
7/1/2014
10 Original Blend
7/1/2014
6 Sea Salt and Caramel
7/1/2014
4 Sea Salt and Caramel
7/1/2014
2 Sea Salt and Caramel
7/1/2014
6 Truffle
7/1/2014
12 Truffle
7/1/2014
6 Original Blend
7/2/2014
2 Truffle
7/2/2014
2 Old Bay
7/2/2014
4 Old Bay
7/2/2014
6 Sea Salt and Caramel
7/2/2014
10 Sea Salt and Caramel
7/2/2014
2 Sea Salt and Caramel
7/2/2014
2 Original Blend
7/2/2014
4 Original Blend
7/2/2014
14 Original Blend
7/2/2014
12 Original Blend
State
MD
OH
CA
MO
NJ
VA
DC
AL
MD
DC
MS
NC
VA
NJ
NJ
NY
ID
OH
NY
AL
MD
NC
MS
VA
MD
MD
VA
WV
CA
MO
MS
NY
NY
OH
NY
MD
DC
MS
AL
NC
AL
DC
NY
WV
7/2/2014
7/2/2014
7/2/2014
7/2/2014
7/2/2014
7/2/2014
7/2/2014
7/2/2014
7/2/2014
7/3/2014
7/3/2014
7/3/2014
7/3/2014
7/3/2014
7/3/2014
7/3/2014
7/3/2014
7/3/2014
7/4/2014
7/4/2014
7/4/2014
7/4/2014
7/4/2014
7/4/2014
7/4/2014
7/4/2014
7/4/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/5/2014
10
2
2
2
4
2
4
4
2
4
4
2
6
2
2
4
2
6
6
8
2
2
4
4
8
12
2
2
2
4
2
6
2
4
6
2
4
4
2
6
6
8
2
2
4
4
2
Original Blend
Truffle
Old Bay
Sea Salt and Caramel
Old Bay
Old Bay
Original Blend
Original Blend
Truffle
Original Blend
Original Blend
Sea Salt and Caramel
Sea Salt and Caramel
Old Bay
Truffle
Old Bay
Truffle
Old Bay
Original Blend
Original Blend
Sea Salt and Caramel
Old Bay
Old Bay
Original Blend
Truffle
Sea Salt and Caramel
Sea Salt and Caramel
Old Bay
Old Bay
Sea Salt and Caramel
Sea Salt and Caramel
Original Blend
Original Blend
Truffle
Old Bay
Old Bay
Old Bay
Truffle
Original Blend
Sea Salt and Caramel
Original Blend
Truffle
Old Bay
Original Blend
Old Bay
Original Blend
Sea Salt and Caramel
OH
VA
VA
DC
MD
WV
AL
SC
SC
OH
NY
NJ
NJ
NY
NY
OH
DC
CA
AL
MS
OH
CA
DC
CA
VA
NJ
WV
MD
DC
NJ
NY
MD
AL
VA
MD
MD
VA
NY
OH
CA
CA
CA
MD
MD
VA
VA
MS
7/5/2014
7/5/2014
7/5/2014
7/5/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
7/6/2014
6
4
2
6
2
2
4
2
6
2
2
4
8
10
2
4
10
4
12
2
2
4
2
6
10
4
Original Blend
Original Blend
Old Bay
Truffle
Old Bay
Truffle
Original Blend
Original Blend
Sea Salt and Caramel
Truffle
Truffle
Sea Salt and Caramel
Old Bay
Old Bay
Original Blend
Truffle
Truffle
Truffle
Original Blend
Sea Salt and Caramel
Old Bay
Sea Salt and Caramel
Sea Salt and Caramel
Truffle
Sea Salt and Caramel
Old Bay
NC
WV
WV
OH
MD
OH
CA
MD
VA
MD
VA
AL
MD
WV
WV
CA
CA
MO
WV
DC
DC
MD
VA
AL
MS
NC
SIMnet 2013: Excel 2013 Capstone Project Level 2 (Mac 2011)
Excel 2013 project
1. Worksheet: Trucks
2. Format the data as follows:
a.
b.
c.
d.
e.
f.
g.
h.
i.
AutoFit columns A:E.
Apply the Title cell style to cell A1.
Apply the Accent 5 fill color to cell A1.
Apply the Background 1 font color to cell A1.
Merge and center the worksheet title across cells A1:E1.
Apply the Heading 1 cell style to cell B2.
Merge and center cells B2:E2.
Bold cells B3:E3.
Apply the Accounting Number Format with 0 digits after the decimal to
cells B4:E6.
3. Calculate total sales for each of the truck locations.
a.
b.
c.
d.
Enter the word Total in cell A7.
Enter a SUM function in cell B7 to calculate the total of cells B4:B6.
Use AutoFill to copy the formula to cells C7:E7.
Apply the Total cell style to cells A7:E7.
4. Insert a 2-D Pie chart to show the total sales for the month by location. Each piece of the
pie should represent the total sales for a single location. Hint: You will need to select two
noncontiguous cell ranges – the total values and the location names.
5. Modify the pie chart as follows:
a. Add Title Above Chart. Change the chart title to: July Sales by
Location
b. Hide the chart legend.
c. Display the chart data labels. Hint: Outside End looks best with this chart.
d. Move the chart so it appears below the sales data.
6. Insert a 2-D Clustered Column chart to show the sales for each type of popcorn for each
location. Do not include the totals. Each location should be represented by a data series and
the popcorn types should be listed along the x axis. Hint: You may need to switch the
row/column after inserting the chart.
11. Modify the column chart as follows:
a. Add Title Above Chart. Change the chart title to: July Sales by Popcorn Type
b. Apply the 7th chart style in the top row of the Chart Styles menu.
c. If necessary, move the chart so it is next to the pie chart and the top of the charts are
aligned.
12. Preview how the worksheet will look when printed, and then apply print settings to print the
worksheet on a single page. Hint: If you have one of the charts selected, deselect it before previewing
the worksheet. Preview the worksheet again when you are finished to check your work.
a. Change the orientation so the page is wider than it is tall.
b. Change the printing scale to Fit To a single page.
13. Top’t Corn is considering a new truck purchase. Calculate the total cost of the loan.
a. Change the color of the NewTruck worksheet tab to Accent 6.
b. Set the width of column B to 1.21”.
c. In cell B6, enter a formula to calculate the total paid over the life of the loan (the monthly
payment amount in cell B5 * the number of payments in cell B2). Use cell references.
d. In cell B7, enter a formula to calculate the total interest paid over the life of the loan (the
total payments in cell B6 – the original price of the truck in cell B1). Use cell references.
e. Apply a thick box border around cells A5:B7. Use the preset border option.
f. In cell A10, type: Buy new truck?
g. In cell B10, enter a formula using the IF function to display Buy it! if the monthly payment
for the truck loan in cell B5 is less than the value in cell B9. Display Pass if it is not. Use cell
references.
14. Complete the following steps in the TysonsStore worksheet:
a. Select cells A2:A32, and apply the Custom date format that displays like this: 3/14/2001.
b. In cell F2, enter a formula to calculate the daily total in dollars. Multiply the value in the
Daily Total (# Sold) column by the current price per box in cell J1. Use an absolute
reference where appropriate and copy the formula to cells F3:F32.
c. In cell G2, enter a formula using the IF function to determine whether the daily sales goal in
cell J2 was met. Display yes if the daily sales were greater than or equal to the daily sales
goal. Display no if they were not. Use an absolute reference where appropriate and copy the
formula to cells G3:G32.
d. Create a named range DailySales for cells F2:F32.
e. In cell J3, enter a formula using the named range DailySales to calculate the average daily
sales in dollars.
f. In cell J4, enter a formula using the named range DailySales to find the lowest daily sales in
dollars.
g. In cell J5, enter a formula using the named range DailySales to find the highest daily sales
in dollars.
15. Go to the OnlineSales worksheet and format the sales data as a table using the table style
Table Style Medium 6. Hint: The table data include headers.
16. Sort the data alphabetically by values in the Item column.
17. Filter the table to show only rows where the value in the State column is NJ.
18. Save and close the workbook.
19. Upload and save your project file.
20. Submit project for grading.
…
Purchase answer to see full
attachment
Recent Comments