excel work ….. edit excel document ……. do today…… 10 hours……
this is due today…… 10 hours……
Edit the excel attachment using the microsoft word attachment instructions
due in 8 hours
TruckLoan
Price $ 55,000.00
Loan term (in months) 24
Interest (annual) 3%
Monthly payment $ 2,363.97
Total payments
Interest paid
Average sales $ 4,500.00
BOGOSale2018
Online Sales During Buy 1 Get 1 Free Sale
Date Quantity Item State
7/30/18 21 Old Bay MD
7/30/18 16 Original Blend OH
7/30/18 25 Original Blend CA
7/30/18 19 Sea Salt and Caramel MO
7/30/18 14 Sea Salt and Caramel NJ
7/30/18 21 Truffle VA
7/30/18 5 Truffle DC
7/30/18 11 Original Blend AL
7/30/18 23 Old Bay MD
7/30/18 13 Truffle DC
7/30/18 10 Sea Salt and Caramel MS
7/30/18 11 Original Blend NC
7/30/18 6 Original Blend VA
7/30/18 18 Old Bay NJ
7/30/18 18 Old Bay NJ
7/30/18 20 Sea Salt and Caramel NY
7/31/18 18 Old Bay ID
7/31/18 24 Sea Salt and Caramel OH
7/31/18 11 Truffle NY
7/31/18 16 Original Blend AL
7/31/18 14 Old Bay MD
7/31/18 20 Original Blend NC
7/31/18 14 Sea Salt and Caramel MS
7/31/18 9 Original Blend VA
7/31/18 19 Old Bay MD
7/31/18 25 Old Bay MD
7/31/18 20 Old Bay VA
7/31/18 15 Original Blend WV
7/31/18 5 Sea Salt and Caramel CA
7/31/18 7 Sea Salt and Caramel MO
7/31/18 7 Sea Salt and Caramel MS
7/31/18 12 Truffle NY
7/31/18 11 Truffle NY
7/31/18 6 Original Blend OH
8/1/18 5 Truffle NY
8/1/18 12 Old Bay MD
8/1/18 8 Old Bay DC
8/1/18 20 Sea Salt and Caramel MS
8/1/18 13 Sea Salt and Caramel AL
8/1/18 23 Sea Salt and Caramel NC
8/1/18 12 Original Blend AL
8/1/18 9 Original Blend DC
8/1/18 20 Original Blend NY
8/1/18 7 Original Blend WV
8/1/18 21 Original Blend OH
8/1/18 16 Truffle VA
8/1/18 14 Old Bay VA
8/1/18 24 Sea Salt and Caramel DC
8/1/18 23 Old Bay MD
8/1/18 17 Old Bay WV
8/1/18 11 Original Blend AL
8/1/18 25 Original Blend SC
8/1/18 6 Truffle SC
8/2/18 19 Original Blend OH
8/2/18 22 Original Blend NY
8/2/18 25 Sea Salt and Caramel NJ
8/2/18 13 Sea Salt and Caramel NJ
8/2/18 22 Old Bay NY
8/2/18 18 Truffle NY
8/2/18 14 Old Bay OH
8/2/18 5 Truffle DC
8/2/18 5 Old Bay CA
8/3/18 10 Original Blend AL
8/3/18 12 Original Blend MS
8/3/18 11 Sea Salt and Caramel OH
8/3/18 22 Old Bay CA
8/3/18 11 Old Bay DC
8/3/18 18 Original Blend CA
8/3/18 22 Truffle VA
8/3/18 13 Sea Salt and Caramel NJ
8/3/18 21 Sea Salt and Caramel WV
8/4/18 5 Old Bay MD
8/4/18 5 Old Bay DC
8/4/18 14 Sea Salt and Caramel NJ
8/4/18 11 Sea Salt and Caramel NY
8/4/18 15 Original Blend MD
8/4/18 6 Original Blend AL
8/4/18 17 Truffle VA
8/4/18 20 Old Bay MD
8/4/18 7 Old Bay MD
8/4/18 17 Old Bay VA
8/4/18 19 Truffle NY
8/4/18 6 Original Blend OH
8/4/18 13 Sea Salt and Caramel CA
8/4/18 17 Original Blend CA
8/4/18 22 Truffle CA
8/4/18 18 Old Bay MD
8/4/18 17 Original Blend MD
8/4/18 12 Old Bay VA
8/4/18 13 Original Blend VA
8/4/18 21 Sea Salt and Caramel MS
8/4/18 6 Original Blend NC
8/4/18 24 Original Blend WV
8/4/18 17 Old Bay WV
8/4/18 18 Truffle OH
8/5/18 19 Old Bay MD
8/5/18 16 Truffle OH
8/5/18 17 Original Blend CA
8/5/18 10 Original Blend MD
8/5/18 16 Sea Salt and Caramel VA
8/5/18 22 Truffle MD
8/5/18 16 Truffle VA
8/5/18 23 Sea Salt and Caramel AL
8/5/18 12 Old Bay MD
8/5/18 21 Old Bay WV
8/5/18 8 Original Blend WV
8/5/18 22 Truffle CA
8/5/18 7 Truffle CA
8/5/18 19 Truffle MO
8/5/18 24 Original Blend WV
8/5/18 12 Sea Salt and Caramel DC
8/5/18 22 Old Bay DC
8/5/18 17 Sea Salt and Caramel MD
8/5/18 7 Sea Salt and Caramel VA
8/5/18 13 Truffle AL
8/5/18 22 Sea Salt and Caramel MS
8/5/18 21 Old Bay NC
TysonsStore2019
Date Old Bay Black Truffle Sea Salt and Caramel Daily Total (# sold) Daily Total ($) Current price per box: $6
1-Sep 239 83 209 531 Daily sales goal: $1,500
2-Sep 225 185 77 487 Average daily sales:
3-Sep 45 165 156 366 Lowest daily sales:
4-Sep 240 93 177 510 Highest daily sales:
5-Sep 195 215 110 520
6-Sep 25 133 251 409
7-Sep 126 53 196 375
8-Sep 263 141 37 441
9-Sep 26 174 204 404
10-Sep 29 62 29 120
11-Sep 210 238 98 546
12-Sep 269 70 273 612
13-Sep 150 86 224 460
14-Sep 70 189 55 314
15-Sep 221 182 240 643
16-Sep 30 43 206 279
17-Sep 53 143 259 455
18-Sep 274 169 27 470
19-Sep 166 121 101 388
20-Sep 211 66 69 346
21-Sep 114 240 72 426
22-Sep 81 132 267 480
23-Sep 273 241 194 708
24-Sep 266 233 42 541
25-Sep 168 237 273 678
26-Sep 54 265 255 574
27-Sep 165 85 189 439
28-Sep 274 38 55 367
29-Sep 25 222 188 435
30-Sep 121 119 245 485
TysonsStore2018
Date Old Bay Black Truffle Sea Salt and Caramel Daily Total (# sold)
8/1/18 42 115 92 249
8/2/18 42 42 117 201
8/3/18 105 124 64 293
8/4/18 141 83 76 300
8/5/18 137 73 109 319
8/6/18 68 44 60 172
8/7/18 82 58 145 285
8/8/18 125 96 92 313
8/9/18 20 26 39 85
8/10/18 67 38 21 126
8/11/18 121 124 132 377
8/12/18 140 76 25 241
8/13/18 147 77 77 301
8/14/18 86 128 59 273
8/15/18 50 122 96 268
8/16/18 80 92 139 311
8/17/18 41 69 59 169
8/18/18 26 38 125 189
8/19/18 93 41 133 267
8/20/18 84 144 146 374
8/21/18 25 124 145 294
8/22/18 107 60 142 309
8/23/18 116 115 128 359
8/24/18 46 129 145 320
8/25/18 44 112 99 255
8/26/18 112 96 31 239
8/27/18 73 102 97 272
8/28/18 100 89 109 298
8/29/18 55 30 75 160
8/30/18 137 117 97 351
8/31/18 125 39 80 244 1. Open the start fileEX2019-Capstone-Level1.
Note:If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the worksheet so you can modify it
2. The file will be renamed automatically to include your name. Change theproject filename if directed to do so by your instructor, andsaveit
3. Insert a new worksheet and rename it:MobileSales
4. If necessary, move theMobileSalesworksheet so it appears first in the workbook.
5. Enter the text and sales data as shown in the table below. Check your work carefully.
6.
A
B
C
D
E
1
Topt Corn Mobile Sales (September)
2
Truck Location
3
Farragut Square
GW
Georgetown
K Street
4
Old Bay
2600
900
650
1100
5
Truffle
2900
600
1300
1700
6
Sea Salt and Caramel
4000
1700
1300
1500
7. Format the data as follows:
a. Apply theAccent1cell style to cellA1.
b. Apply theLight Greenfill color to cellB2. Use the fifth color from the left in the row of Standard colors.
c. Apply theWhite, Background 1font color to cellB2. Use the first color at the left in the first row of Theme colors.
d. Merge and center the worksheet title across cellsA1:E1.
e. Merge and center cellsB2:E2.
f. Bold cellsB3:E3.
g. Apply theAccounting Number Formatwith0digits after the decimal to cellsB4:E6.
h. AutoFit columnsA:E.
8. Calculate total sales for each of the truck locations.
a. Enter the wordTotalin cellA7.
b. Enter a SUM function in cellB7to calculate the total of cellsB4:B6.
c. Use AutoFill to copy the formula to cellsC7:E7.
d. Apply theTotalcell style to cellsA7:E7.
9. Insert a pie chart(2-D Pie) to show the Old Bay sales for the month by location. Each piece of the pie should represent the
Old Bay
sales for a single location.
Note:You must complete this step correctly in order to receive points for completing the next step. Check your work carefully.
10. Modify the pie chart as follows:
a. Apply theLayout 6 Quick Layout.
b. Move the chart so it appears below the sales data.
11. Insert a clustered column chart (2-D Column) to show the sales for each type of popcorn for each location. Do not include the totals.Hint: You may need to switch the row/column after inserting the chart.
Note:You must complete this step correctly in order to receive points for completing the next step.Check your work carefully.
12. Modify the column chart as follows:
a. If necessary, modify the chart so each location is represented by a data series and the popcorn types are listed along the x axis.
b. Change the chart title to:September Sales by Popcorn Type
c. Apply theStyle 7chart Quick Style.
d. Display the chart data labels using theOutside Endoption.
e. If necessary, move the chart so it is next to the pie chart and the top of the charts are aligned.
13. 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 so all columns will print on a single page.
14. Topt Corn is considering a new truck purchase. Calculate the total costof the loan.
a. Change the color of theTruckLoanworksheet tab toYellow. Use the fourth color from the left in the row of Standard colors.
b. Set the width of columnBto16.
c. In cellB6, enter a formula to calculate the total paid over the life of the loan (the monthly payment amount in cellB4 *the number of payments in cellB2). Use cell references.
d. In cellB7, enter a formula to calculate the total interest paid over the life of the loan (the total payments in cellB6 -the original price of the truck in cellB1). Use cell references.
e. Apply borders using theThick Outside Bordersoption around cellsA6:B7.
15. Complete the following steps in theTysonsStore2019worksheet:
a. In cellF2, enter a formula to calculate the daily total in dollars. Multiply the value in theDaily Total (# Sold)column by the current price per box in cellI1. Use an absolute reference where appropriate and copy the formula to cellsF3:F31.
b. In cellI3, enter a formula to calculate theaveragedailysales in dollars.
c. In cellI4, enter a formula to find thelowestdaily salesin dollars.
d. In cellI5, enter a formula to find thehighestdaily salesin dollars.
16. Save and close the workbook.
