Excel file and instructions.

Anonymous
timer Asked: Mar 29th, 2017

Question Description

This assignment is fairly easy, it is about Excel file and instructions. You have to follow the instructions and complete the change on the excel file, it's mainly formula changes and minor adjustments, style types, font, color, sums, layout, auto fit etc.

Thank you!

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.
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.
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

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors