Exp22_Excel_Ch01_HOE_Souvenirs
Exp22 Excel Ch01 HOE Souvenirs
Excel Chapter 1 Hands-On Exercise – Celebrity Musician’s Souvenir Shop
Project Description:
As the assistant manager of Celebrity Musician’s Souvenir Shop, you want to calculate the retail price, sale price, and profit analysis for selected items on sale this month. You will calculate sale prices based on discount rates. Finally, you will calculate the profit margin to determine the percentage of the final sale price over the cost. You will check the spelling, use Auto Fill to complete a sequence for product codes, and find and replace text. In addition, you will adjust column and row settings, move and copy data, format the worksheet, and apply Page Setup options.
Start Excel. Download and open the file named Exp22_Excel_Ch01_HOE_Souvenirs.xlsx. Grader has automatically added your last name to the beginning of the filename.
You notice that the worksheet contains some spelling errors that need to be corrected.
Use Excel to check and correct the spelling errors in the worksheet.
You want to use Auto Fill to enter a series of codes to create the product numbers.
Enter 101 in cell B5 and use Auto Fill to complete the sequence for the range B6:B10 with a series.
To motivate customers to buy more products, you want to change the 5% (0.05) off to 10% (0.10) off.
Find all occurrences of 0.05 and replace them with 0.10.
Column C contains the cost of each product, and column D contains the markup rate. Using these values, you will calculate the markup amount for each product in column E.
In cell E5, enter a formula to calculate the markup amount, which is the result of multiplying the cost by the markup rate. Copy the formula to the range E6:E10.
Now that you have calculated the markup amount, you can calculate the retail price for each product in column F.
In cell F5, enter a formula to calculate the retail price, which is the sum of the cost and the markup amount. Copy the formula to the range F6:F10.
Each item is on sale this week. The sale price is X percent off the retail price. You need to calculate the sale price.
In cell H5, calculate the sale price. Copy the formula down the range H6:H10.
The profit margin is the ratio of the net profit as a percentage of revenue. The formula first calculates the net profit between the sale price and the cost and divides that by the sale price.
In cell I5, enter a formula to calculate the profit margin. Copy the formula to the range I6:I10.
You decide to add a column to display the profit. Because profit is a dollar amount, you want to keep the profit column close to another column of dollar amounts. You will insert a column for the profit amount and enter the column heading.
Insert a new column I and enter Profit Amount in cell I4.
Now you are ready to enter the profit amount formula.
In cell I5, enter a formula to calculate the profit, which is the difference between the sale price and original cost. Copy the formula to the range I6:I10.
You want to insert two rows for category names above their respective products. Then you want to delete the row containing the Soundtrack CD.
Insert a new row 5 and type Apparel in cell A5. Insert a new row 8 and enter Souvenirs in cell A8. Bold the words in cells A5 and A8. Delete row 11 containing the Soundtrack CD.
Column A is too narrow for the product names. You want to increase the width. In addition, you want to increase the height for the first row. Finally, you decide to hide the product code column.
Increase the width of column A to 23.00. Change the height of row 1 to 30. Hide column B containing the product codes.
The T-shirt product is below the Souvenirs heading. This product belongs in the Apparel section. You will move the T-shirt product row to be above the Souvenirs heading.
Select and cut the range A11:J11. Insert cut cells on row 8.
The travel mug product information is missing. Because it is similar to the mug data, you will copy and paste the mug data and then edit it for the travel mug.
Copy the range A10:J10 and paste it in cell A12. In cell A12, enter Travel Mug. In cell C12, enter 7.
You want to experiment with Paste Options.
In cell A1, apply bold, select 14 pt font size, and select Dark Red font color. Copy the range A1:J12 and paste it in cell A20 as Values & Source Formatting.
The worksheet title should stand out prominently on the worksheet.
Select the range A1:J1 and apply Heading 1 cell style, merge and center the data, and apply Middle Align vertical alignment.
The column headings on row 4 should be formatted to improve the appearance of the worksheet.
Select the range A4:J4. Horizontally center, apply bold, and wrap text for the headings.
To distinguish the product names from the category headings Apparel and Souvenirs, you will indent the product names.
Select the ranges A6:A8 and A10:A12. Indent twice the product names.
You want to apply a fill color to highlight the column labels. In addition, you want to draw attention to the sale information by adding a border around data.
Select the range A4:J4. Apply Blue-Gray, Text 2, Lighter 80% fill color (second row, fourth column). Select the range G4:H12 and apply the Thick Outside Borders. On a Mac, apply Thick Box Border.
You want to display the dollar signs for the monetary to improve readability.
Select the ranges C6:C12, E6:F12, and H6:I12 and apply Accounting Number Format.
The Markup Rate and Profit Margin values should be formatted with percent signs with one decimal place.
Select the ranges D6:D12 and J6:J12. Apply Percent Style and increase the decimal points to show one digit to the right of the decimal point.
The Percent Off should be formatted with percent signs and centered in the column.
Select the range G6:G12, apply Percent Style, and horizontally center the data.
You want the percentages in column J to align better below the column label.
Select the range J6:J12, apply Align Right horizontal alignment, and increase the indent one time.
You want to copy the results to another worksheet and display formulas on the duplicate worksheet.
Copy Sheet1, move the duplicate to the right of Sheet 1. Rename Sheet1 as September. Rename Sheet1 (2) as Formulas. Display formulas on the Formulas tab.
To prepare the Formulas worksheet to print, the column widths should be changed.
Set column A width to 12 and C and D widths to 6. Set columns E, F, H, I, and J widths to 7. Set column G width to 5.
Because the worksheets contain more columns than rows, it would look better in landscape orientation.
Select both worksheets and change the orientation to landscape.
You continue to apply Page Setup options to prepare the worksheets to be printed, if needed.
With both sheets selected, set a 1-inch top margin and select the option to horizontally center the data on the page. Select the scaling option to fit data on each worksheet on one page.
It is important to provide identification information in a header of the worksheets. In particular, the textbook series name, the worksheet name, and the file name to help identify the worksheet.
With both sheets selected, insert a header with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side. Click the September sheet to make it active.
Save and close Exp22_Excel_Ch01_HOE_Souvenirs.xlsx. Exit Excel. Submit the file as directed.