9.12: Chapter Review - Practice Exercises
-
- Last updated
- Save as PDF
Practice Exercises
23 .
Refer to Figure 9.7
.
Using part a of the figure, move to the next column to the right. This new column will be for adding the average. This average can easily be calculated by typing the AVERAGE function. Research the syntax and use it in the new column. Once you find the average, copy the formula down the column.
24 .
WorldCorp’s sales team uses a customer relations management (CRM) software that gives the user the option to export data. This exported data needs to be formatted when you open it with Excel. Design a table with the following headers: Date, Product, Qty, Price per Unit, Total Value, and Notes and preformat each column based on its respective type of data.
25 .
Using the table from the last exercise, add the data shown in the following image. Insert a calculation for the total value instead of typing in the dollar amount from the table.
| Date | Product | Price | Qty | Total |
|---|---|---|---|---|
| 12/20/2020 | Headphones | $48.00 | 57 | $2,736.00 |
| 12/28/2020 | Headphones | $48.00 | 74 | $3,552.00 |
| 1/3/2021 | LED 65 | $780.00 | 13 | $10,140.00 |
| 1/3/2021 | Headphones | $48.00 | 9 | $432.00 |
| 1/5/2021 | Headphones | $48.00 | 10 | $480.00 |
| 1/7/2021 | OLED 45 | $48.00 | 77 | $30,800.00 |
| 1/8/2021 | LED 65 | $780.00 | 14 | $10,920.00 |
| 1/8/2021 | Headphones | $48.00 | 16 | $768.00 |
| 1/11/2021 | LCD 42 | $380.00 | 48 | $18,240.00 |
| 1/13/2021 | Plasma 65 | $855.00 | 12 | $10,260.00 |
| 1/13/2021 | Headphones | $48.00 | 85 | $4,080.00 |
| 1/15/2021 | Headphones | $48.00 | 158 | $7,584.00 |
| 1/17/2021 | LCD 42 | $380.00 | 55 | $20,900.00 |
| 1/17/2021 | Headphones | $48.00 | 115 | $5,520.00 |
| 1/21/2021 | Plasma 65 | $855.00 | 11 | $9,405.00 |
| 1/22/2021 | Headphones | $48.00 | 15 | $720.00 |
| 1/25/2021 | LCD 42 | $380.00 | 54 | $20,520.00 |
| 1/27/2021 | QLED 55 | $620.00 | 10 | $6,200.00 |
| 1/30/2021 | LED 55 | $550.00 | 10 | $5,500.00 |
| 2/4/2021 | LCD 32 | $170.00 | 43 | $7,310.00 |
| 2/7/2021 | QLED 55 | $620.00 | 15 | $9,300.00 |
| 2/9/2021 | OLED 45 | $400.00 | 37 | $14,800.00 |
| 2/11/2021 | Plasma 65 | $855.00 | 34 | $29,070.00 |
| 2/15/2021 | DLP 32 | $250.00 | 97 | $24,250.00 |
| 2/18/2021 | LED 65 | $780.00 | 54 | $42,120.00 |
| 2/21/2021 | LED 55 | $550.00 | 21 | $11,550.00 |
| 2/25/2021 | LCD 42 | $380.00 | 54 | $20,520.00 |
| 2/27/2021 | LCD 32 | $170.00 | 14 | $2,380.00 |
26 .
Refer to Figure 9.22
.
Use the figure to find the average of the marginal profit value. First, determine the total volume sold and total marginal profit. Then, divide those two numbers to find the average marginal profit per unit sold. Design this extended table.
27 .
Refer to Figure 9.23
.
WorldCorp handed you an assignment to use the figure to determine the FOB $ average of all orders and then to compare the average order and the invoice order individually in a new column. Create the new column to show the difference between each individual order and the FOB $ average. Finally, divide that difference by the FOB $ to get a percent difference. Be sure to format the data as a percentage. Construct this table.
28 .
WorldCorp sells various business tablets that are designed to be rugged. In the past month, the model FE-546 sold 435, the model FR-765 sold 324, and the model FH-985 sold 213. They cost $356, $467, and $586, respectively. Construct a table with these values. Include a total column as well.
29 .
Refer to Figure 9.28
.
Using the data in the figure, construct a line chart comparing dates and sales. Build this chart. Look for information on the internet on how to build basic graphs using Excel.
30 .
Your manager at WorldCorp asks you to design a sales report with the following columns: date, model, price, quantity, and sales total. Enter four different fictional orders in the table. Use formatting that you feel is appropriate for the header rows.
31 .
Your manager at WorldCorp tells you to print the document. Explain in detail what you would do to print five copies of the document, which contains only one worksheet.
32 .
WorldCorp makes many products and sells them to thousands of customers. The sales team keeps call logs for when they make calls to their corporate clients to generate sales. These clients are retailers that buy directly from WorldCorp because of the volume discount. Design and create a call log with the date of the call, the purchasing executive’s name, the client’s name, the phone number, and the order details. The order details are separated in different columns for text description, units, sale value, and optional notes. These optional notes contain special delivery requests from the client. Use the cell formatting methods you have learned so far.
33 .
Create an address book using information for four friends or family members. The table should include their names, street addresses, phone numbers, and email addresses. Their street addresses should be separated into four columns: the street and house/apartment number, the city, the state, and the zip code. Use the formatting methods you have learned so far.
34 .
WorldCorp is releasing a new universal remote control model that has a touch screen and is programmable for any model of TV, stereo, cable box, and movie player. The sales figures for the preorders need to be added together by location and then multiplied by the price per unit to get the final sales figures. Enter the data into Sheets and complete the table using functions.
35 .
WorldCorp sells tablets to educational organizations. Each basic tablet’s price is $157.64, on a discounted rate because they are purchased for hundreds of students. The tablet comes with its own keyboard, all in one package. WorldCorp sold 654 in Fairfax, Virginia; 867 in Portland, Maine; and 532 in Deeville, Texas. Make a table forecasting the revenues of each location. Compare the locations with the total sales revenue using a proportion. Make a graph comparing these three locations’ performances. Use the best practices discussed in the chapter for designing tables.
36 .
WorldCorp sells tablets to educational organizations. Each basic tablet’s price is $157.64, on a discounted rate because they are to be bought for hundreds of students. The tablet comes with its own keyboard, all in one package. WorldCorp sold 654 in Fairfax, Virginia; 867 in Portland, Maine; and 532 in Deeville, Kansas. Make a table forecasting the revenues of each location. Use a conditional formatting of “Greater than” and set the minimum to be $100,000. Explain the significance of the conditional formatting in these three revenue comparisons.