70.2K

Verified Solution

Link Copied!

Part A

1. Your boss has decided to purchase you a phone. Create a new workbook named YourLastName-Assignmentxlsx.
2. 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

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

4. 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.
5. 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.
6. 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.

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

Answer & Explanation Solved by verified expert

See Answer