Question

1 Approved Answer

Posted on Aug 26, 2024

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

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 margins to the preset narrow option co Change the printing scale so all columns will print on a single page. 14. Topt Com is considering a new truck purchase. Calculate the monthly loan payments and total cost of the loan. a. Insert a new worksheet between the MobileSales sheet and the Online Sales sheets. b. Name the new worksheet: TruckLoan c Change the color of the worksheet tab to Orange. Use the third color from the left in the row of Standard colors. d. Enter the loan terms as shown below. B A 55000 1 price 2 Back to top Interest (annual) Reset Progress -24 3 Loan tere (in months) Monthly payment ENE e.AutoFit column A. 1. Set the width of column B to 16. 9 Apply the Currency number format to cell B1, Display two digits after the decimal. h. Enter a formula using the PMT function in cell B4. Be sure to use a negative value for the Pv argument In cell A6 type: Total payments 3. In cell Be enter a formula to calculate the total paid over the life of the loon (the monthly payment amount * the number of payments). Use cell references k in cell A7, type: Interest paid 1.Din cell B7. enter a formula to calculate the total interest paid over the life of the loan (the total payments - the original price of the truck). Use cell references m. Apply borders using the Thick Outside Borders option around cells A6:87 n. In cell A9, type: Average sales o in cell B9, enter a formula to calculate the average sales per month for the truck locations. Hint Use cells 87:E7 from the MobileSales worksheet as the function argument. p. Apply the Currency number format to cell B9, Display two digits after the decimal in cell A10 type: Buy new truck? In cell B10. enter a formula using the IF function to display Yes if the monthly payment for the true Reset Progress Back to top average sales per month for the current trucks Display No if it is not 15. This workbook includes two worksheets for data from the Tysons store You should only be working with the latest data from 16. Complete the following steps in the TysonsStore2019 worksheet: a. Select cells A2:A32, and apply the Short Date date format. b. Find the top ten sales items for the month. Select cells B2:D32 and use conditional formatting to apply a green fill with dark green text to the top 10 values. 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 K1. Use an absolute reference where appropriate and copy the formula to cells F3:F32. din cell G2, enter a formule using the IF function to determine whether the daily sales goal in cell K2 was met Display yes if the value in the Daily Totai (5) column is greater than or equal to the daily sales goal. Display na if it is not Use an absolute reference where appropriate and copy the formula to cells G3:G32. e. Create a named range DailyTotals for cells F2:F32. t. In cell K3, enter a formula using the named range Daily Totals to calculate the average daily sates in dollars g. In cell K4 enter a formula using the named range Daily Totals to find the lowest daily sales h. In cell Ks. enter a formula using the named range Daily Totals to find the highest daily sales in dollars 1. Wrap the text in cell 37 juse Goal Seek to find the new price per box (coll K8) to reach a new daily average sales goal of Reset Progress the solution found by Goal Seek. KOModify cell K8 to show two places after the decimal 17 Make a copy of the Online Sales worksheet and name it PivotData The PivotData worksheet should be the last ho in dollars. Back to 2:49 O (Cosul In A A Wap Tent A. Merge Center Custom $ % 91 Delete- Analyte Condition form Formatting Table Styles Style Sort Find Fit Select Member Ana 11/2019 H Sales Goal Mat? 95 239 bry. Ony 340 . 1 . 1 1 1 Set 1 with Price increase Current priceper box 3.186 Daily sales goal $1.500 2,922 Average daily sales 2,196 Lowest daily sales 3,060 Highest daily sales 3.130 7,654 Average sales with price, 2,250 New price per box 56 $ 3,646 3,424 720 3.276 3,673 + 6 Aug 5 8.Aug $ B 1 Date Old Bay Black Truffle Sea Salt and Caramel Daily Totale sold) Daily Total (5) Aug B 309 1 335 185 77 407 4 45 365 366 5 4-Aug 177 510 6 S. Aug 195 110 520 7 25 13 351 409 B 7. Aug 26 196 375 9 263 141 17 441 10 9.Aug 26 174 204 404 11 29 62 29 130 12 11-Aug 250 238 98 546 13 2-Aug 369 70 273 14 1) Aug 150 56 224 460 15 14 Aug 70 55 315 16 25-Aug 2 240 643 15 Aug 30 43 200 229 all 11-Aug 53 14) 259 455 19 13 Aug 375 169 27 470 10 19-Aug 165 123 380 27 30 Aug 215 66 65 20 Aug 154 2.60 72 10 Aug S $ 1 ETO 2760 309 1,884 3.85 5 1 1 5 TOT 5 2.730 2,820 2,328 2,096 3,555 345 1 1 SINO Tyson Store2019 Tysontor 2015 JU uote Flas Datii Review Help Te Home Draw Page Layout General Calib Delete- Format $ - % 913 Conditional Format - Cell Formatting Table Style Styles Paste 11 AA 23 Wrap Text A BE EX Mega Center - Alignment BIU- Number font 033 M F G H Daily Total (# sold) 141 133 400 350 300 250 200 150 Mirpur 100 SO A 1 Date Z 1-Aug 3 2-Aug 4 3-Aug 5 4- Aug 6 5-Aug 7 6-Aug 8 7. Aug 9 8-Aug 10 9 Aug 10-Aug 11-Aug 13 12 Aug 14 13-Aug 15 14-Aug 16 15-Aug 17 16-Aug 18 17.Aug 19 18 Aug 20 19-Aug 21 20-Aug 23 21-Aug Old Bay Black Truffle Sea Salt and Carame Daily Total (sold 140 49 110 299 100 375 32 09 35 135 130 35 34 199 104 124 93 321 43 135 117 295 61 9 62 132 16 68 93 177 8 70 19 97 145 9 114 275 94 14 148 256 50 59 82 196 92 110 65 54 140 63 267 40 46 12 98 136 50 123 314 21 34 55 110 32 122 133 287 57 144 12 213 26 98 107 241 122 140 44 306 52 76 OldTown Store 13. A 267 A 25-A 11 27- 29- Daily Telodi Type here to search View Review Data Formulas Help File Draw Home Insert Page Layout General 2 Wrap Text inser a Del 11 Corbel - a-A- Fot IM Paste $ - % ! lil IU- Merge & Centers Conditional Formatas Cell Formatting Table Styles Styles 5 CH Number Alignment Font Chipboard F G H A Online Sales During Buy 1 Get 1 Free Sale B C D E Online Sales During Buy 1 Get 1 Free Sale 1 2 3 Date 4 5 6 7 8 9 10 11 12 13 14 15 16 Quantity Item State 7/30/2019 13 Old Bay MD 7/30/2019 9 Original Blend OH 7/30/2019 28 Original Blend CA 2/30/2019 9 Sea Salt and Caramel MO 7/30/2019 21 Sea Salt and Caramel NJ 7/30/2019 9 Truffle VA 7/30/2019 29 Truffle DC 7/30/2019 20 Original Blend AL 7/30/2019 17 Old Bay MD 7/30/2019 20 Truffle DC 7/30/2019 16 Sea Salt and Caramel MS 7/30/2019 24 Original Blend NC 7/30/2019 14 Original Blend VA 7/30/2019 24 Old Bay NJ 7/30/2019 9 Old Bay NJ 7/30/2019 32 Sea Salt and Caramel NY 7/38/2019 11 Old Bay ID 7/31/2019 12 Sea Salt and Caramel OH 7132/2019 33 Truffle - Rand OnlineSales TysonsStore2019 Tysons Store2018 9859258925222222 17 18 19 20 NY 22 Type here to search O 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 margins to the preset narrow option co Change the printing scale so all columns will print on a single page. 14. Topt Com is considering a new truck purchase. Calculate the monthly loan payments and total cost of the loan. a. Insert a new worksheet between the MobileSales sheet and the Online Sales sheets. b. Name the new worksheet: TruckLoan c Change the color of the worksheet tab to Orange. Use the third color from the left in the row of Standard colors. d. Enter the loan terms as shown below. B A 55000 1 price 2 Back to top Interest (annual) Reset Progress -24 3 Loan tere (in months) Monthly payment ENE e.AutoFit column A. 1. Set the width of column B to 16. 9 Apply the Currency number format to cell B1, Display two digits after the decimal. h. Enter a formula using the PMT function in cell B4. Be sure to use a negative value for the Pv argument In cell A6 type: Total payments 3. In cell Be enter a formula to calculate the total paid over the life of the loon (the monthly payment amount * the number of payments). Use cell references k in cell A7, type: Interest paid 1.Din cell B7. enter a formula to calculate the total interest paid over the life of the loan (the total payments - the original price of the truck). Use cell references m. Apply borders using the Thick Outside Borders option around cells A6:87 n. In cell A9, type: Average sales o in cell B9, enter a formula to calculate the average sales per month for the truck locations. Hint Use cells 87:E7 from the MobileSales worksheet as the function argument. p. Apply the Currency number format to cell B9, Display two digits after the decimal in cell A10 type: Buy new truck? In cell B10. enter a formula using the IF function to display Yes if the monthly payment for the true Reset Progress Back to top average sales per month for the current trucks Display No if it is not 15. This workbook includes two worksheets for data from the Tysons store You should only be working with the latest data from 16. Complete the following steps in the TysonsStore2019 worksheet: a. Select cells A2:A32, and apply the Short Date date format. b. Find the top ten sales items for the month. Select cells B2:D32 and use conditional formatting to apply a green fill with dark green text to the top 10 values. 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 K1. Use an absolute reference where appropriate and copy the formula to cells F3:F32. din cell G2, enter a formule using the IF function to determine whether the daily sales goal in cell K2 was met Display yes if the value in the Daily Totai (5) column is greater than or equal to the daily sales goal. Display na if it is not Use an absolute reference where appropriate and copy the formula to cells G3:G32. e. Create a named range DailyTotals for cells F2:F32. t. In cell K3, enter a formula using the named range Daily Totals to calculate the average daily sates in dollars g. In cell K4 enter a formula using the named range Daily Totals to find the lowest daily sales h. In cell Ks. enter a formula using the named range Daily Totals to find the highest daily sales in dollars 1. Wrap the text in cell 37 juse Goal Seek to find the new price per box (coll K8) to reach a new daily average sales goal of Reset Progress the solution found by Goal Seek. KOModify cell K8 to show two places after the decimal 17 Make a copy of the Online Sales worksheet and name it PivotData The PivotData worksheet should be the last ho in dollars. Back to 2:49 O (Cosul In A A Wap Tent A. Merge Center Custom $ % 91 Delete- Analyte Condition form Formatting Table Styles Style Sort Find Fit Select Member Ana 11/2019 H Sales Goal Mat? 95 239 bry. Ony 340 . 1 . 1 1 1 Set 1 with Price increase Current priceper box 3.186 Daily sales goal $1.500 2,922 Average daily sales 2,196 Lowest daily sales 3,060 Highest daily sales 3.130 7,654 Average sales with price, 2,250 New price per box 56 $ 3,646 3,424 720 3.276 3,673 + 6 Aug 5 8.Aug $ B 1 Date Old Bay Black Truffle Sea Salt and Caramel Daily Totale sold) Daily Total (5) Aug B 309 1 335 185 77 407 4 45 365 366 5 4-Aug 177 510 6 S. Aug 195 110 520 7 25 13 351 409 B 7. Aug 26 196 375 9 263 141 17 441 10 9.Aug 26 174 204 404 11 29 62 29 130 12 11-Aug 250 238 98 546 13 2-Aug 369 70 273 14 1) Aug 150 56 224 460 15 14 Aug 70 55 315 16 25-Aug 2 240 643 15 Aug 30 43 200 229 all 11-Aug 53 14) 259 455 19 13 Aug 375 169 27 470 10 19-Aug 165 123 380 27 30 Aug 215 66 65 20 Aug 154 2.60 72 10 Aug S $ 1 ETO 2760 309 1,884 3.85 5 1 1 5 TOT 5 2.730 2,820 2,328 2,096 3,555 345 1 1 SINO Tyson Store2019 Tysontor 2015 JU uote Flas Datii Review Help Te Home Draw Page Layout General Calib Delete- Format $ - % 913 Conditional Format - Cell Formatting Table Style Styles Paste 11 AA 23 Wrap Text A BE EX Mega Center - Alignment BIU- Number font 033 M F G H Daily Total (# sold) 141 133 400 350 300 250 200 150 Mirpur 100 SO A 1 Date Z 1-Aug 3 2-Aug 4 3-Aug 5 4- Aug 6 5-Aug 7 6-Aug 8 7. Aug 9 8-Aug 10 9 Aug 10-Aug 11-Aug 13 12 Aug 14 13-Aug 15 14-Aug 16 15-Aug 17 16-Aug 18 17.Aug 19 18 Aug 20 19-Aug 21 20-Aug 23 21-Aug Old Bay Black Truffle Sea Salt and Carame Daily Total (sold 140 49 110 299 100 375 32 09 35 135 130 35 34 199 104 124 93 321 43 135 117 295 61 9 62 132 16 68 93 177 8 70 19 97 145 9 114 275 94 14 148 256 50 59 82 196 92 110 65 54 140 63 267 40 46 12 98 136 50 123 314 21 34 55 110 32 122 133 287 57 144 12 213 26 98 107 241 122 140 44 306 52 76 OldTown Store 13. A 267 A 25-A 11 27- 29- Daily Telodi Type here to search View Review Data Formulas Help File Draw Home Insert Page Layout General 2 Wrap Text inser a Del 11 Corbel - a-A- Fot IM Paste $ - % ! lil IU- Merge & Centers Conditional Formatas Cell Formatting Table Styles Styles 5 CH Number Alignment Font Chipboard F G H A Online Sales During Buy 1 Get 1 Free Sale B C D E Online Sales During Buy 1 Get 1 Free Sale 1 2 3 Date 4 5 6 7 8 9 10 11 12 13 14 15 16 Quantity Item State 7/30/2019 13 Old Bay MD 7/30/2019 9 Original Blend OH 7/30/2019 28 Original Blend CA 2/30/2019 9 Sea Salt and Caramel MO 7/30/2019 21 Sea Salt and Caramel NJ 7/30/2019 9 Truffle VA 7/30/2019 29 Truffle DC 7/30/2019 20 Original Blend AL 7/30/2019 17 Old Bay MD 7/30/2019 20 Truffle DC 7/30/2019 16 Sea Salt and Caramel MS 7/30/2019 24 Original Blend NC 7/30/2019 14 Original Blend VA 7/30/2019 24 Old Bay NJ 7/30/2019 9 Old Bay NJ 7/30/2019 32 Sea Salt and Caramel NY 7/38/2019 11 Old Bay ID 7/31/2019 12 Sea Salt and Caramel OH 7132/2019 33 Truffle - Rand OnlineSales TysonsStore2019 Tysons Store2018 9859258925222222 17 18 19 20 NY 22 Type here to search O