Your boss has decided to purchase you a phone Create a new workbook named Your Last Name Assignment xlsx Create a worksheet that compares the cost of the listed items for the 3 phones Organize the worksheet so that each component is listed separately As you can see from the (fictitious) list, some of the specs are included in the base phone price (e g battery upgrade is included in the price for the Google phone) In those cases, enter a 0 for that respective cell value Google Pixel 3 XL Apple iPhone XR Samsung Galaxy S10 Qualcomm Snapdragon 8452 5Ghz 1 6Ghz, 64Bit Octa Core, 128gb internal memory, 12 2 MP camera, 3 02 in width x 6 22 in height, 3 year warranty, battery upgrade, headphones included, shipping included$899 Apple iOS 12, 128gb internal memory, 2 98 in width x 5 94 in height, 12 MP camera, Bluetooth included, case included$749 Android Pie (9 0), 12 MP camera, 2 77 in width x 5 9 in height, 256gb internal memory, shipping included$899 Additional items Additional items Additional items Case $35 Battery upgrade $100 Case $40 Bluetooth $50 Shipping cost $35 Battery upgrade $90 2 year warranty $120 3 year warranty $150 Headphones $30 Headphones $29 Bluetooth $49 Include the following elements A title of Phone Purchase Analysis at the very top of the worksheet formatted in Ariel, size 16, bold, and italic Merge and center the title above your worksheet and add a light green fill to the background of the respective cell The text color should remain black Appropriate column and row headings so that your worksheet is easy to understand Do not leave them in the default font style Numbers in the first row and in any summation rows formatted with the Accounting Number Format Format all other dollar values with the Comma Style Note that zeros will appear as dashes Using the SUM() function, calculate the total cost of each item and then format the total with Accounting Number Format Use Total Cost as the label for the total cost row Right align the label and format it as bold and italic Highlight the cell containing the name of the least expensive phone in yellow Do not highlight the amount cost but rather the actual title of the product The corporation is planning to purchase at least 30 similar phones for other employees Because of this volume, the following price reductions are now available Google has agreed to a rebate of $40 per phone Apple has agreed to give a 15 across the board discount on everything but shipping, which remains at $35 per phone Samsung has declined to give any volume purchase discount Skip at least two rows at the bottom of your current data In a separate area, calculate the total cost of a single phone from each competitor using this new pricing structure Reference the values you have previously calculated as needed Just below the calculation for Step 6, calculate the cost of purchasing the 30 phones with this new pricing structure for each option Highlight the cell containing the lowest final cost for 30 phones in a shade of light blue

The Answer is in the image, click to view...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 30, 2020

Your boss has decided to purchase you a phone. Create a new workbook named Your Last Name-Assignment xlsx. Create a worksheet that compares the cost

Your boss has decided to purchase you a phone. Create a new workbook named Your Last Name-Assignment xlsx.
Create a worksheet that compares the cost of the listed items for the 3 phones. Organize the worksheet so that each component is listed separately. As you can see from the (fictitious) list, some of the specs are included in the base phone price (e.g. battery upgrade is included in the price for the Google phone). In those cases, enter a 0 for that respective cell value.

Google Pixel 3 XL	Apple iPhone XR	Samsung Galaxy S10
Qualcomm® Snapdragon™ 8452.5Ghz + 1.6Ghz, 64Bit Octa-Core, 128gb internal memory, 12.2 MP camera, 3.02 in width x 6.22 in height, 3-year warranty, battery upgrade, headphones included, shipping included…$899	Apple iOS 12, 128gb internal memory, 2.98 in width x 5.94 in height, 12 MP camera, Bluetooth included, case included…$749	Android Pie (9.0), 12 MP camera, 2.77 in width x 5.9 in height, 256gb internal memory, shipping included…$899
Additional items:	Additional items:	Additional items:
Case $35	Battery upgrade $100	Case $40
Bluetooth $50	Shipping cost $35	Battery upgrade $90
	2-year warranty $120	3-year warranty $150
	Headphones $30	Headphones $29
		Bluetooth $49

Include the following elements:

A title of “Phone Purchase Analysis” at the very top of the worksheet formatted in Ariel, size 16, bold, and italic. Merge and center the title above your worksheet and add a light green fill to the background of the respective cell. The text color should remain black.
Appropriate column and row headings so that your worksheet is easy to understand. Do not leave them in the default font/style.
Numbers in the first row and in any summation rows formatted with the Accounting Number Format. Format all other dollar values with the Comma Style. Note that zeros will appear as dashes.

Using the SUM() function, calculate the total cost of each item and then format the total with Accounting Number Format. Use “Total Cost” as the label for the total cost row. Right-align the label and format it as bold and italic.
Highlight the cell containing the name of the least expensive phone in yellow. Do not highlight the amount/cost but rather the actual title of the product.
The corporation is planning to purchase at least 30 similar phones for other employees. Because of this volume, the following price reductions are now available.
- Google has agreed to a rebate of $40 per phone.
- Apple has agreed to give a 15% across-the-board discount on everything but shipping, which remains at $35 per phone.
- Samsung has declined to give any volume purchase discount.

Skip at least two rows at the bottom of your current data. In a separate area, calculate the total cost of a single phone from each competitor using this new pricing structure. Reference the values you have previously calculated as needed.

Just below the calculation for Step 6, calculate the cost of purchasing the 30 phones with this new pricing structure for each option.
Highlight the cell containing the lowest final cost for 30 phones in a shade of light blue.