Corital Collectibles Excel 2013 Exercise Worksheet

Problem 1: You are working for a travel agency. You want to track and compare the prices for four of your customers favorite destinations using the popular 5-Day packages. Your worksheet will show the packages in the first column and the package prices for the four destinations in the other columns, as shown in Table 1A.

undefined

Instructions: Perform the tasks below to design and create the Vacation Package Analysis Workbook

Create a folder for your exam files in your Excel One Student Data Files called Your Name Excel Exam Folder. In step 8 you will save your Excel file here.

1. Open up Excel and using the data in Table 1A (bottom of page), create the worksheet shown in Figure 1B(next page). Step by step instructions follow:
2. Use the Solstice Theme.

3. Use AutoSum to create the total revenue for each of the four vacation spots and the totals for each of the four vacation packages.

4. Format the worksheet title, VACATION PACKAGE ANALYSIS, as Heading 1 style, and merged across columns A through F.

5. Format the range A2:F2 using the Heading 3 style. Format the range A7:F7 using the Total style. Use appropriate comma style and currency style to format the remaining cells so the worksheet displays as shown in Figure 1B.

6. Insert a 3-D Clustered column chart in cells A9 through F22 (if need be can be larger to ensure legend names are on one line) shown on the worksheet in Figure 1B. Apply the Style 18 chart style to the chart.

7. Name the worksheet tab – Vacation. Colour the tab aqua.

8. Change the document properties to Your Name/Excel One/ Exam/vacation analysis.

9. Print the worksheet in Landscape Orientation and fit to one page.

10. Change the amount for the All Inclusive package for Alaska to 6,538. Decrease the amount for Hotel Only in Cancun to 1,787 and the amount for Tours Plus in Hawaii to 3,208. Print the worksheet containing the new values. Close the workbook without saving the changes.

Problem2: You are an accountant for a collectibles store that has locations all throughout the state. You are trying to determine if stores are meeting their sales quotas. You know the sales amount, returns amount and sales quota. You will calculate the rest using functions.

Instructions: Perform the following tasks to create the worksheet shown in Figure 2A. See Step # 11 for file name.

1. Enter and format the worksheet title Corital Collectibles and worksheet subtitle Yearly Sales Analysis in cells A1 and A2. Change the theme of the worksheet to the Concourse theme. Apply the Title cell style to cells A1 and A2. Change the font size in cell A1 to 28 points. Merge and center the worksheet title and subtitle across columns A through F. Change the background color of cells A1 and A2 to the Indigo, Accent 5, Lighter 60% color. Change the font color of cells A1 and A2 to the Yellow Standard color. Draw a thick box border around the range A1:A2.

2. Change the width of column A to 18.00 points. Change the widths of columns B through F to15.00 points. Change the heights of row 3 to 33.00 points and row 11 to 30.00 points.

3. Enter the column titles in row 3 and row titles in the range A10:A13, as shown in Figure 2A. Center the column titles in the range A3:F3. Apply the Heading 1 cell style to the range A3:F3.Apply the Total cell style to the range A10:F10. Bold the titles in the range A10:A13. Change the font size in the range A3:F13 to 12 points.

4. Enter the data for the Store Location (A4:A9), Sales (B4:B9), Returns (C4:C9), and Sales Quota (E4:E9) columns using the column info from Figure 2A.

5. Use the following formulas to determine the Net Sales in column D and the Above Quota in column F for the first store location. Copy the two formulas down through the remaining store locations.

a. Net Sales (cell D4) = Sales – Returns

b. Above Quota (F4) = Net Sales – Sales Quota

6. Determine the totals in row 10.

7. Determine the average, maximum, and minimum values in cells B11:B13 for the range B4:B9, and then copy the range B11:B13 to C11:F13

8. Format the numbers as follows: (a) assign the Currency style with a floating dollar sign to the cells containing numeric data in the ranges B4:F4 and B10:F13, and (b) assign a number style with two decimal places and a thousand’s separator (currency with no dollar sign) to the range B5:F9.

9. Use conditional formatting to change the formatting to dark red font Indigo, Accent 5, Lighter 80% background in any cell in the range F4:F9 that contains a value less than 0.

10. Change the worksheet name from Sheet1 to Sales Analysis and the sheet tab color to the Yellow standard color. Change the document properties, to Your Name/Excel 2010 Exam/ sales quota. Change the worksheet header to your name and Performing Retail Sales Analysis.

11. Spell check the worksheet. Preview and then print the worksheet in landscape orientation.

12. Print the range A3:E13. Also print the formulas version and attach printouts to the back of the exam. Close the workbook without saving these final changes.

Problem3: The time has come for you to purchase a car. After years of saving, you are ready to find the car of your dreams. You have a down payment and now wish to develop an amortization schedule so you can plan your payments. You hope to borrow no more than $26,000. Interest rates are low, but may soon be rising. Because you are not sure how long it will take to find the right car, you wish to examine several different payment plans. You hope to pay off the loan in 5 years.

Develop a worksheet that will show the monthly payment, the beginning and ending balance for each year of the loan, the annual cost of the loan, and the annual interest paid for each year of the loan.

.

1. Select the entire worksheet and change the font size to 12.

2. Change the column widths to the following: A = 11.00; B, C, and E = 20.00; D = 19.00.

3. Change the row heights to the following: 1, 5, and 12 = 39.00.

4. Enter the worksheet title, Car Loan Analysis, in cell A1. Merge and center cell A1 across the range A1:E1. Change the font type to Bodoni MT Black, the font size to 20, the font color to Dark Blue, Text 2, and the background to Orange, Accent 6.

5. Enter the following labels: A2 = Principal; A3 = Rate; A4 = Years; C4 = Payment; E4 = per month; A5 = Year; B5 = Beginning Balance; C5 = Ending Balance; D5 = Total Paid; E5 = Interest. Change the font color to Dark Blue, Text 2.

6. Enter the =Now() function in cell E2 to display the current date and format as shown in Figure 3A. (Note: The date will be different from that in Figure 3A.)

7. Enter the principal amount of $26,000 in cell B2. Format the cell to currency format with no decimal places.

8. Enter the interest rate of 8.90% in cell B3. Format the cell as shown in Figure 3A.

9. Enter the number 5 in cell B4 for the number of years. Create names to make the following steps easier to follow. Select the range A2:B4 and from Formulas tab/ Create from Selection. Also repeat with C4:D4.

10. Enter the PMT function –PMT(Rate/12,12*Years,Principal) in cell D4 to calculate the monthly payment on a loan of $26,000 (cell B2) at 8.90% (cell B3) for 5 years (cell B4). Format it as shown in Figure 3A.

11. Use the fill handle to fill the range A6:A10 with the numbers 1 through 5.

12. Enter the formula =B2 in cell B6 to reference the principal, which is the beginning balance for year 1.

13. Enter the PV function in cell C6 to determine the ending balance for year 1. Use =IF(A6<=Years,PV(Rate/12,12*(Years-A6),-Payment),0)

14. Enter the formula =$D$4 * 12 in cell D6 to determine the annual amount paid on the loan.

15. Enter the formula =D6-(B6-C6) in cell E6 to calculate the amount of interest paid for the year.

16. Enter the formula =C6 in cell B7 to obtain the beginning balance for year 2. Copy this formula to the range B8:B10.

17. Copy the PV function entered in cell C6 to the range C7:C10.

18. Copy the formula in cell D6 to the range D7:D10.

19. Copy the formula in cell E6 to the range E7:E10. If all is done properly, the value in cell C10 should be zero.

20. Use the =SUM function in cells D11 and E11 to sum the payment and interest amounts.

21. Format all cells as shown in Figure 3A. Pay attention to borders, text alignment, font, colours, etc. (The figure shows the font that is in use.)

22. In cell A12, enter the label, The Effect of Various Interest Rates. Format this entry the same as the formatted entry in cell A1 by using the Format Painter.

23. Enter the following labels: B13 = Rate; C13 = Total Paid; D13 = Total Interest.

24. Enter the formula =D11 in cell C14 and the formula =E11 in cell D14.

25. Enter and format the interest rates shown in Figure 3A into the range B15:B21.

26. Create a one-variable data table that displays the total amount paid and the total amount of interest for the 5 year amortization schedule created in this exercise.

27. Rename the Sheet 1 tab to Car Loan.