Description

You will receive this product immediate after placing the order

Solution Manual Of Succeeding In Business With Microsoft Excel 2013 By Debra Gross

Microsoft® Excel® 2013:

A Problem-Solving Approach

 

“With knowledge comes opportunity,

with opportunity comes ;

 

— Anonymous

Chapter 1 Applying Fundamental Excel Skills and Tools in Problem Solving 22

 

Conceptual Review

1. What is the meaning of each of the following error messages?

• ######
• #NAME?
• #N/A
• #REF!
• #VALUE!
• #NUM!
• #DIV/0!

2. The formula =1,100/25 is entered into a cell exactly as shown. Will Excel be able to

calculate a result based on this formula? Explain.

3. If you enter =12–1*10 in a cell exactly as shown, what value would result?
4. List each of the following operations in order of precedence, from 1 to 4 (first to last):

• Addition and subtraction
• Parentheses ( )
• Multiplication and division
• Exponentiation

5. When you write a formula, why should you use cell references rather than typing in

values

directly into the formula?

6. In the worksheet below, cell A3 contains the formula =A1+A2. Explain the most

likely reason the value calculated appears to be incorrect.

7. Referring to the preceding worksheet, if you wrote the formula =B1*5, what value

would result, assuming the displayed value is the precise value?

8. What formula would you write to do each of the following?
9. Add a range of numbers in cells B2:B12.
10. Find the largest value in cells C2:F2.
11. Find the smallest value in cells A1 through X10.
12. Find the average value in cells C1 through C10, assuming blank cells will be ignored.
13. Find the total number of values listed in cells A1 through F1, excluding any that

contain

text.

9. Define the following terms: syntax, arguments, and algorithm.
10. If you copy the formula =$B$4–SUM(C1:C5) from cell A9 to cell B11, what is the

resulting

formula?

11. Refer to the following worksheet. What formula would you write in cell B2 that can

be copied down the column and across the row to complete the multiplication table?

12. What new formula results for each of the following formulas if you copy it from

cell C10 to cell E12?

1. =A1+A2
2. =$A$1+A2
3. =$A1+A2
4. =A$1+A2
5. What formula could you use to add up cell C1 from Sheet1!, Sheet 2!, and Sheet 3!

(assuming the worksheets are adjacent and in the same workbook)?

14. Cell B1 has been given the range name discount. How would you write a formula in

cell C1 that multiplies discount by cell A1? What new formula results if you copy this

formula into cell C2?

Case Problems

Level 1 – Purchasing a Computer for Durban & Associates

You are currently employed at a medium-sized financial management firm, Durban &

Associates, as a financial analyst. Your boss recently authorized the purchase of a new

laptop computer for your use at home and in the office. She asked you to obtain three

competitive bids before she approves the purchase. The minimum requirements of the

system you want to buy are as follows:

Finance

• Long-life battery
• DVD recordable device
• Video card
• Full 3-year warranty
• Carrying case
• Shipping

Table shows information for three possible computers based on Web site pricing. The

features do not match up exactly, so you need to create a worksheet comparing all three

systems and their total prices.

Table : Data for three computers

Note that the models, features, and pricing are fictitious and do not constitute advice on any computer purchases.

Dell Laptop Sony Laptop Lenovo Laptop

Dell Alienware ZBY;

Intel Core i7-2670QM

Quad-

Core including 8 GB

of DDR3 RAM, 500 GB hard

drive, DVD-R/W, 18” display,

long-life battery, video camera

and video card, Windows 8

Professional, Wi-Fi/ Bluetooth/

broadband capable, 3-year

warranty, shipping included…

$1749

Sony VAIO RMV w/3rd Gen Intel Core

i7-3612QM () including 8

GB of RAM, 300 GB hard drive, ;

display, video card, DVD-R/W drive,

built-in video camera, Windows 8 Home

Basic, Wi-fi/ broadband/Bluetooth

capable, carry case, standard 1-year

warranty… $1449

Lenovo ThinkPad D Intel

Core Intel Core i7-3520M

Processor, ( GHz), 4GB

of RAM, 250 GB hard drive,

; display, Windows

8 Professional and video

card, Wi-Fi and broadband

capable, 1-year warranty,

free shipping… $1104

Additional items: Additional items: Additional items:

Carrying case $ Hard drive upgrade to 600 GB $ Upgrade to 8 GB RAM $130

Upgrade to Windows 8 Professional

$69

Upgrade to 500 GB hard

drive 7200 rpm $60

Long-life battery upgrade $149 Built-in video camera $29

3-year warranty $ Bluetooth $30

Shipping cost $46 DVD R/W drive $115

Long-life battery $70

3-year warranty $

Carrying case $65

© 2014 Cengage Learning

Complete the following:

1. Create a new workbook named Computer , and then save the file in

the Chapter 1 folder.

2. Create a worksheet that compares the cost of each of the listed items for the three

computers. Organize the worksheet so that each component is listed separately. If

an item is included in the base computer price, enter a zero. Be sure to include the

following

elements:

• A title formatted in Cambria, size 14, bold, and italic. Merge and center the title

above your worksheet and add a light blue fill.

• Appropriate column and row headings so that your worksheet is easy to understand.

If necessary, wrap the text headings into more than one row in the cell.

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

3. Calculate the total cost of each system, assuming all items listed for that system are

purchased,

and then format the totals with Accounting Number Format. Enter a

label for the row of total, right align the label, and use bold italic formatting.

4. Verify that the totals are accurate even if other values are later substituted for any of

the system component costs.

5. Highlight the cell containing the name of the least expensive computer system in yellow.
6. You learn from your boss that the corporation is planning to purchase at least 40 similar

systems. Because of this volume, the following price reductions are now available:

• Dell has agreed to a rebate of $150 per machine.
• Sony has agreed to give an 18% across-the-board discount on everything but

shipping,

which remains at $46 per system.

• Lenovo 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 machine 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 40 machines

with this new pricing structure for each option.

8. Highlight the cell containing the lowest final cost for 40 machines in a shade of light green.
9. Save and close the Computer workbook.

Level 2 – Compiling Relocation Information for Devcon Finn, Inc.

You work in the Human Resources Department of Devcon Finn, Inc., a computer

consulting firm. An employee is considering a transfer to one of the company’s other

locations and is qualified for several different positions. Your task is to help the employee

choose the most appropriate position based on a number of criteria. For example, you need

to determine the value of each position in terms of the disposable income the employee

can expect. The position with the highest salary is located in the company’s New York

City office, but a studio apartment there costs about $2,650 per month. However, in the

New York office, the employee would not need a car.

You have documented each position in an Excel worksheet. On this sheet, you have

recorded the positions, the annual salary, the cost of living multiplier (which you obtained

from a Web site) and estimates of a monthly car payment including insurance, assuming

for some of these jobs the employee will need to purchase a car. You have also recorded

information regarding starting bonuses (a one-time payment when hired) offered to the

employee. Now, you need to finalize the worksheet.

Complete the following:

1. Open the workbook named ;located in the Chapter 1 folder, and then save

the file as Job

2. In cells F2:F4, calculate the associated annual adjusted salary. This adjusted salary is

the annual salary divided by the cost of living multiplier minus the expected annual

car payments.

(Note that car payments are given in dollars per month ($/month).

3. In cell F6, write a formula to calculate the average adjusted salary of the three positions.
4. In cell F7, write a formula to determine the value of the lowest adjusted salary.

This formula

should automatically update if any of the data inputs are later changed.

5. In cell F8, write a formula to determine the value of the highest adjusted salary.

This formula

should automatically update if any of the data inputs are later changed.

6. In cell G9, write a formula to determine the number of positions that include a bonus.
7. In cells H2:H4, calculate the value of the adjusted salary package for each position

over a two-year period, including bonuses. The bonus does not need to be adjusted

for location (multiplied by location factor) because the employee plans to use the

bonus toward a vacation. Assume that the employee will receive a raise after the

first year of employment in the new position.

8. Display dollar values in columns E to H without cents, and include a dollar sign only

in the first row of columns with dollars. Format the cost of living multipliers with two

decimal places displayed, and align these values on the decimal point.

9. Another position for which the employee is qualified has just become available. This

position,

a senior consultant position, is located in Washington , has an annual

salary of $68,000, plus a $1,500 hiring bonus. Because the position is located in

downtown

Washington, assume that the employee will not be purchasing a car and

that the cost of living multiplier is Insert the data for this new position just below

the New York position. Complete the calculations for the adjusted salary and the

total two-

year financial package. Verify that all of the other values you’ve calculated

update correctly. Adjust the formatting of the new data, as needed, to match the

formatting

of the existing data.

10. Highlight in yellow the row of the position with the highest two-year financial

package,

and bold the text in this row.

11. Save and close the Job workbook.

Level 3 – Analyzing Regional Sales Information for CKG Auto

As a regional sales manager for CKG Auto, you have just finished summarizing sales data for

the first half of this year (January through June), aggregated by car model. You have started

to enter data in an Excel worksheet, which lists by model the following information:

• Sales Volume, indicating the number of cars sold to dealers.
• Manufacturing (Mfg.) Cost per Vehicle.
• Total Cost of all vehicles sold for the model, which is based on the sales volume and the

manufacturing costs per vehicle.

• Markup Percentage, which is the percentage charged above manufacturing cost to dealers.
• Total Sales to dealers, which is the Total Cost plus Markup. Markup equals the markup

percentage times the manufacturer cost of the vehicle.

• % of Total Volume, which is based on the volume for the model compared to the

volume

of all models sold for the time period.

First, you need to complete the January through June computations based on the data

contained in the worksheet and the information given. Then, you have been asked to

create a similar worksheet to estimate sales for July through December based on volume

supplied by the marketing group. These volumes are based on the historical values adjusted

for seasonal demand of specific models and from market research on the popularity of

models. After you have completed both the first half actual sales and the second half

estimated sales, combine the data to determine expected yearly sales. Management is

interested in not only the absolute value of those sales, but also each model’s contribution

to the total yearly sales in each half of the year and in aggregate.

When completing the workbook, be sure that all data is correctly referenced so that your

formulas will work as you copy them down the column or across the row, as necessary.

To complete the workbook, use the following steps:

1. Open the workbook named ;located in the Chapter 1 folder, and then save

the file as CKG Rename the Sheet1 worksheet tab as 1st Half.

2. In the highlighted cells, enter formulas to perform the necessary calculations for

January through June and to summarize. Be sure to write all formulas so that they

can be copied as necessary. Note the following:

• Display all dollar values in whole numbers and include the dollar sign in the first

row and total rows only.

• When calculating averages, your formula should automatically exclude models that

had no sales. Display all average values (other than the percentage) with commas

and no decimal places.

• When calculating summary data (total, average, etc.), keep in mind that additional

car models (rows) may eventually be inserted at the bottom of the list.

• The formulas in column G need to determine the percent of total volume sales that

the vehicle represents. (That is, if model A sold 100 cars and a total of 1000 cars

were sold for all models, then model A would represent 10% of the total volume.)

Format the cells in column G to display values to the nearest tenth of a percent.

Be sure to calculate the number of models available for sale.

3. Your next task is to estimate the July through December sales based on the marketing

data and the sales volume for the second half of the year. The marketing group has

provided a list of all car models in identical order to the original data you received

with the expected sales volumes for each car model. You can find this list in the

Market.

xlsx workbook, located in the Chapter 1 folder. The marketing group

assumes that the manufacturing costs and markups will be the same for the second

half of the year as they were for the first half. With the data and assumptions in mind,

insert a new worksheet named 2nd Half in the CKG workbook, identical

to the 1st Half worksheet. Copy and paste the sales volumes from the

workbook into your new worksheet.

Verify that all the calculations in the new worksheet

reflect the new data.

TROUBLESHOOTING: In order to complete this task successfully, copy the entire

1st Half worksheet

to a new worksheet. Then, copy only the values for the sales volume,

excluding the heading, from the Market workbook by highlighting the column

values

and clicking the Copy button. Next, place the insertion point in the cell of the new

worksheet corresponding to the first vehicle’s volume, and then paste the data. Check

the calculated fields (Total Cost, Total Sales to Dealers, etc.) to make sure vehicles

that were not sold during the first half of the year have values calculated and vehicles

not sold during the second half do not have values.

4. Add another new worksheet named Summary, and include the column headings

shown in Table on this new worksheet.

Table : Column headings for Summary worksheet

Model Annual

Volume

Jan–June

Sales to

Dealers

July–Dec

Sales to

Dealers

Total Sales

to Dealers

%Total

Sales to

Dealers

Jan–June

%Total

Sales to

Dealers

July–Dec

%Total

Sales to

Dealers

Annual

1. Insert the model numbers in the identical format as shown on the 1st Half and

2nd Half worksheets.

1. Insert the annual volume for each model—the combined totals of the January

through June and the July through December volumes. Make sure that the

values

will automatically update if any of the input values are changed.

1. Insert the Jan–June sales to dealers, again ensuring that these values will

automatically

update if any of the input data changes.

1. Insert the July–Dec sales to dealers, again ensuring that these values will

automatically

update if any of the input data changes.

1. Create a combined total of sales to dealers for the entire year.
2. Calculate the total volumes and the total sales to dealers for each time period

and annually in a row below the data.

1. Calculate the percentage of sales to dealers that each model represents, as a percentage

of the total sales to dealers for all models, for January through June,

July through December, and the year. Use only one formula for this calculation,

and make sure that the formula can be copied down the column to calculate the

percentages for the corresponding

models and across the row to calculate the

percentages for the corresponding time frames. Display the percentages with an

appropriate format and number of decimal places.

5. Format all three worksheets so that they have a professional appearance.
6. Save and close the CKG workbook.

 

 

Chapter 2 Solving Problems with Statistical Analysis Tools 78

 

Conceptual Review

1. What formula could you write to calculate the mean of the following data set: 4, 10,

8, 6, 2, 4, 14? (Note that a resulting value is not required.)

2. What is the median value of the data set given in Question 1?
3. What is the mode of the data set given in Question 1?
4. The data set given in Question 1 has a standard deviation of compared with

another data set that has the same mean but a standard deviation of What general

differences would you expect to find between the two sets of data?

5. In the chapter, the original labor rate for QC inspectors was $35 per hour. However,

due to a contract renegotiation, this value is now $40. What mathematical expression

could you use to determine the percent increase in labor costs? (Note that a resulting

value is not required.)

6. When you use the Increase Decimal button in the Number group on the HOME tab,

the precise value in the cell is modified. True or false?

7. The formula =ROUND(,-1) results in what precise value?
8. Write a formula to round down to the nearest percent.
9. What is the symbol for the less than or equal to relational operator in Excel?
10. What is the symbol for the not equal to relational operator in Excel?
11. Review the following worksheet, and then use the COUNTIF function to write a

formula that determines the number of Ford cars on this list.

12. Using the worksheet shown in Question 11, write a formula to determine the number

of cars that cost more than $20,000.

13. Using the worksheet shown in Question 11, write a formula to automatically

determine

the total value of all GM cars. This formula should work even if the data is

later modified.

14. Explain the difference between a “what-if” analysis and Goal Seek by giving an

example

based on the worksheet shown in Question 11.

15. Using the worksheet shown in Question 11, write a formula to determine the value

of the third least expensive car.

16. If each car shown in Question 11 is marked up between $100 and $200 in dollar

increments, what function could be used to randomly assign the amount to be added

to the car price in this formula?: =B2+ ___________

17. The formula =RAND() gives what result?
18. What formula could you write to average the values in cells B2 through B10, excluding

blank cells, rounded to the nearest whole number?

19. Write a formula to automatically determine the average price of only GM vehicles

using the worksheet in Question 11. This formula should work even if the data is

later modified.

20. Write a formula to determine the total value of only vehicles priced below $20,000.

This formula should work even if the data is later modified.

Case Problems

Level 1 – Analyzing Sales for Crèmes Ice Cream

Judd Hemming is the eastern regional marketing manager for Crèmes Ice Cream. Each

quarter, he completes two separate analyses: an analysis comparing ice cream flavor sales

volumes from all regional locations with the same quarter sales volumes from the previous

year, and an analysis comparing total sales in dollars, including mean, median, mode, and

standard deviation, of sales by store.

Sales by Flavor in Gallons

The first analysis, sales by flavor, compares the total quantities sold in gallons. The

data collected provides for each flavor the number of pints, gallons, and 10-gallon tubs

sold for all stores. Pints and gallons are sold directly to the public, whereas 10-gallon

tubs are used for in-store sales of cones, cups, and specialty items such as sundaes

and banana splits. To eliminate any impact of pricing changes or special promotions,

Judd uses the ice cream volumes in gallons to compare sales by flavor. Judd has asked

for your help this quarter in completing this analysis. He created two workbook files:

, which contains the current quarter’s sales on a worksheet named Flavors,

and , which contains the corresponding historical quarterly data for the

previous year on a worksheet named HFlavors. For convenience, the flavors in both

data sets are in identical order except for two new flavors introduced this year, which

appear at the bottom of the current data set. Keep in mind the following conversions

when analyzing this data:

• There are 8 pints per gallon.
• Each tub holds 10 gallons.

Also, when calculating values for 10-gallon tubs, Judd has asked you to use the convention

of rounding down the values to the nearest whole tub.

Sales by Store in Dollars

The second analysis you need to complete is to summarize sales in dollars by store and

compare the result with the previous year’s sales. The Stores worksheet in the same

workbook contains sales by store for the current quarter in dollars rounded to the nearest

thousand dollars. You need to calculate some basic statistics for store sales. In the same

quarter of the previous year, these values were:

• Mean: $8,817
• Median: $8,000
• Mode: $5,500
• Standard Deviation: $2,920

When writing formulas, be certain to use the most efficient method, including the use of

functions as well as relative and absolute cell referencing.

Complete the following:

1. Open the workbook named ;located in the Chapter 2 folder, and then

save it as Creme Current Sales

2. On the Flavors worksheet, for the current quarter, calculate the total number of

gallons

sold for each favor. Place this calculation in the column adjacent to the data

provided. Above the list of flavors, in a separate area, list and clearly label the conversion

values. Remember to round down the tub quantities to the nearest whole tub

when completing the calculation.

3. Add another column in which to enter the total number of gallons from last year.

Copy the values for the total amount of ice cream sold for the corresponding flavor

for last year from the ;workbook in the Chapter 2 folder into the newly

added column of the Flavors worksheet.

4. Calculate the overall total and mean number of gallons sold for all flavors for this

quarter and for this quarter last year. (Note: Include all flavors in your calculation,

whether or not there are sales for them in a given year.)

5. Calculate for each flavor the percent of total gallons this flavor represents compared

with total sales for the current quarter of all flavors. Copy this formula

to the adjacent

column to calculate the percent of total gallons this flavor represents compared with

historical total sales.

6. In the two adjacent columns, calculate for each flavor and for the totals the difference

and percent difference in sales. Assume a positive value represents an increase in sales.

Flavors without sales in the previous year should be left blank. (These cells should be

completely empty.)

TROUBLESHOOTING: In order to complete this step successfully, pay attention

to the order of precedence rules when writing the formula. Percent difference is

defined as the difference between the new value minus the old value divided by the

old value, so specifying the order in which the operations must occur is critical.

7. Switch to the Stores worksheet. Calculate the total sales, mean, median, mode, and

standard deviation for this data set. Label the cells so that they can be easily identified.

8. On the same worksheet, set up a table to analyze the change and percent change of

each of these statistical values as compared with the historical values.

Based on the

changes, explain on the worksheet (just below your analysis) whether the stores are

doing better this year, and if sales in stores are more or less likely to vary from mean

sales than they did last year.

9. Based on sales to all stores and total gallons sold for the current quarter, what is the

price of a gallon of ice cream on average? Write a formula to determine this value and

place it below the statistical analysis on the Stores worksheet.

10. Add appropriate worksheet titles and formatting to make the worksheets easy to read.
11. Save and close the Creme Current Sales workbook.

Level 2 – Analyzing Demographic Data for La Rosa Restaurant

You have recently decided to open a restaurant that you plan to name La Rosa. In your

restaurant, you plan to feature specialty desserts, along with fine cuisine. One critical

decision you must make is where to locate the restaurant. Right now, you are considering

two different locations—one near a large retail area on the fringe of several affluent

suburbs (site X), and the other in the downtown district (site Y). Before making the

decision, you hired a local market research firm to provide you with some demographics

of the areas and the specific dining habits of the local population that frequent other

restaurants in these areas. You have entered the raw results of this research on several

worksheets in the workbook.

Each worksheet (SiteX and SiteY) contains the detailed responses of each of the participants

of the study, including questions about their age, their income, and the number of meals

and desserts they eat outside of their homes per month.

Table shows the breakdown of the age categories and corresponding income level

closest to the respondent’s own income.

Table : Age categories and income levels

Age Categories Income Levels

18 to 21 $5,000

22 to 25 $10,000

26 to 30 $20,000

31 to 35 $30,000

over 35 $40,000

$50,000

$75,000

$100,000

© 2014 Cengage Learning

Complete the following:

1. Open the workbook named ;located in the Chapter 2 folder, and then

save it as La Rosa Demographic

2. On the SiteX and SiteY worksheets, rank each respondent by the number of

meals they eat out per month and the number of desserts they eat out per month,

respectively—

ranking from most meals and desserts out to the least. Freeze the panes

of the window on each worksheet to make the category headings at the top visible at

all times.

3. On a separate worksheet named Compare, calculate the difference and percent

difference

of the mean and standard deviation for the data sets (X and Y) for the

number

of meals and number of desserts per month obtained. On the same worksheet,

set up a table to list the four highest number of desserts per month from each data set

(X and Y) and the ten lowest number of meals eaten out by respondents for each of

the data sets (X and Y). Below the data, discuss how these values differ between the

data sets, and recommend either X or Y for further analysis, highlighted in pink.

4. On a separate worksheet named Summary, determine the following for the location

you think should be selected:

• The total number of respondents
• The total number of respondents with incomes at or above $80,000
• The total number of respondents who eat fewer than three desserts out each month
• The total number of meals eaten out per month reported by respondents who earn

at least $60,000

• The total number of desserts per month reported by respondents who are in the

31 to 35 or over 35 age categories

5. Include appropriate titles, labels, and formatting so that the worksheets are easy

to read.

6. Save and close the La Rosa Demographic workbook.

Level 3 – Determining Inventory Levels for CKG Auto

Another profitable facet of CKG Auto’s business is supplying parts for auto repairs.

The most critical component of the parts supply business is having enough of the right

parts on hand so that repair shops can receive same-day delivery. The key to profits is

minimizing the number of parts that need to be warehoused while also ensuring that

sufficient parts are on hand to meet orders. Because each warehouse distribution center

serves a different set of customers with different needs, each center must be considered

separately. Distribution centers have a five-day lead time for ordering parts. The lead time

must be taken into consideration when determining target inventory levels.

The costs involved in warehousing the parts include the working capital tied up in the

inventory (the cost of each part) as well as the space to store the part. These costs can be

substantial. On the other hand, alternative “generic” parts are often available from rival

suppliers, and keeping the auto repair centers supplied and customers satisfied is critical.

One of the most problematic items to supply and store are bearings. This part is both high

volume and relatively large, taking up considerable warehousing space. A bearing is also

relatively expensive. Analyzing bearing needs is a good place for CKG to start, specifically

in one of its largest distribution centers such as Central New Jersey, which serves 10 major

customers. You have been asked to analyze the bearing inventory level requirements for

this center, including simulating demand based on 30-day historical extremes, calculating

a target inventory level based on this simulation, and then comparing simulated values to

actual values from the past five days from the targeted warehouse.

Complete the following:

1. Open the workbook named ;located in the Chapter 2 folder, and then save

it as CKG Parts

2. Modify the format of the data on the BearingData worksheet so that zero values are

displayed with a 0 instead of the default dash, aligned on the right side of the column.

3. On the BearingData worksheet, take the existing 30-day data for these bearings

for each customer to calculate the high and low limits of the bearing demand by

customer.

4. On a new worksheet named Simulation, use the high and low limits you just calculated

to simulate daily requirements for each customer to obtain a combined daily

requirement. Assume that the daily requirements will vary for each customer randomly

between the high and low limits you have calculated from the existing 30-day

data for that customer. Generate the data for approximately 100 instances (days),

and then copy the results as values to another new worksheet in the workbook. Keep

the original analysis intact on the Simulation worksheet so you can use it again later.

Name the new worksheet Simulation Data 1. Be sure that your worksheets have

titles and cell formatting.

5. On the Simulation Data 1 worksheet, in an adjacent column, calculate the total demand

by day for all 10 customers. Then, use this daily demand total data to calculate

the

daily mean, mode, median, and standard deviation for the combined requirements

of

all 10 customers. Use cell shading to clearly identify your calculation area.

6. Extend your analysis (on the same worksheet) to include a ranking of the data

(1 to 100) so that the day with the least total demand has a rank of 1. Again, use cell

shading to clearly identify columns with your calculations.

7. To the right of the data, create a listing of the top and bottom five daily combined

demands from the 100 simulated instances. Clearly identify this listing using borders

and shading.

8. Recommend a target inventory level needed for a five-day period based on the

following;

to be on the cautious side, assume each day’s supply will be equal to

the average

daily demand for all locations combined, plus three standard deviations:

• Because the mean and standard deviations might not already be integers, round

the daily demand up to the nearest whole number.

• Then, use this calculated daily demand (average plus 3 standard deviations) to

calculate

demand over a five-day period.

Place this recommendation just below the top/bottom analysis, again clearly identifying

1. Add the label Bearing Recommended to identify the cell containing the actual value.
2. The warehouse manager has tracked a total of five different parts over the past five

days, recording for each shipment the value of the part and the number of days it was

in storage. One of these tracked parts is the bearing you have just analyzed. The data

has been compiled in a workbook named Each line item represents

a single shipment of one item. Copy the data from the ;workbook,

located in the Chapter 2 folder, to your workbook and place it on a worksheet named

Actual Demand.

10. On the same worksheet, summarize the data to determine the number shipped by

part, the total values of those shipments by part, and the average number of days that

part was stored, using the format shown in Table

Table : Setup for parts data summary

Item Description # Items Shipped Total Dollar Value of

Items Shipped

Average #Days Held in

Inventory

Bearings

Timing Belts

Air Filters

Fan Belt

Electronic Board

© 2014 Cengage Learning

In the # Items Shipped column, write a formula to determine the number of bearings

shipped. Write the formula so that it can be copied down the column to automatically

determine the number of timing belts shipped, the number of air filters shipped,

and so on. In the Total Dollar Value of Items Shipped column, write a formula to

determine the value of all bearings shipped. Again, write the formula so that it can

be copied down the column to automatically determine the value of timing belts, air

filters, and so on. In the Average #Days Held in Inventory column, write a formula to

determine the average number of days bearings shipped were held in inventory; again,

write the formula so that it can be copied down the column. Be sure that these formulas

will work even if the data is updated in the future.

11. Based on the recommended inventory level you previously calculated for bearings,

would you have had enough bearings in the warehouse to cover these orders? Place

your answer in a cell below your analysis on the Actual Demand worksheet. Clearly

identify this answer and highlight it in yellow.

12. Double-check all values and formulas for correct implementation. Include sufficient

formatting and titles to clearly identify the worksheet elements.

13. Save and close the CKG Parts workbook.

 

 

Chapter 3

Determining Effective Data Display with Charts 154

 

Conceptual Review

1. List and describe the significance of each of Tufte’s five data graphics principles.
2. How do sparklines differ from charts?
3. What are the three steps involved in using the INSERT tab to create a chart?
4. Give an example of a low data-ink ratio in a chart.
5. How do you change the chart type of an existing chart?
6. What charting limits does Excel have in terms of data points and series?
7. How many standard chart types and sub-types are available in Excel?
8. What are the differences and similarities between a line chart and an X Y (Scatter)

chart? When should you use each one?

9. Explain the difference between the data points in a line chart and an X Y (Scatter) chart.
10. What are the differences between a bar chart and a column chart? Give an example of

when you would use each one.

11. What are the differences between a column chart and an area chart? Give an example

of when you would use each one.

12. How do pie charts differ from doughnut charts?
13. When should you use a stacked line, column, or area chart? How do the stacked

charts differ from regular charts?

14. When should you use a 100% stacked line, column, or area chart? How do the 100%

stacked charts differ from stacked charts?

15. What chart sub-types are available for the stock chart in Excel? Explain how you

interpret the data markers on each of the sub-types.

16. How does a radar chart differ from other charts? Give an example of when you would

consider using a radar chart.

Case Problems

Level 1 – Illustrating Travel Data for the Indiana Department of Tourism

As a travel industry consultant, you have been working for the state of Indiana Department

of Tourism. The department manager has asked you to present a report on where Indiana

residents travel to and where Indiana visitors come from. The modes of transportation must

also be presented. You will also include some information from a survey that your firm has

recently completed. The travel survey is located in the workbook named

Complete the following:

1. Open the workbook named ;in the Chapter 3 folder, and then save the

file as Indiana

2. Create two bar charts to illustrate the differences in Trips per Traveler and Miles

per Traveler of the average Indiana resident, the average resident, and the

average Indiana visitor. (Hint: Create the charts for Indiana and Indiana Visitors

first, and then add the data for United States using the Select Data Source dialog

box. (Hint: Delete the “={1}” value in the Series values box before selecting the

United States data.)

3. Create a bar chart to illustrate a state-by-state comparison of the number of residents

from the rest of the nation who visit Indiana, and the number of Indiana residents

who visit other states. Rank the information in this chart by the states with the most

visitors to Indiana.

4. The Indiana Department of Tourism is very interested in learning more about how

people travel in Indiana. They want to use this information to better target advertising

to specific demographic groups and methods of travel. Use the appropriate chart

or series of charts to illustrate how people travel; the purpose of their travel; and their

income levels, age, and gender. This information should be presented for people who

live within the state and those visiting the state with a comparison between the two

groups.

5. Save and close the Indiana workbook.

Level 2 – Analyzing Stock Performance for Universal Investments

As an analyst at the Universal Investments financial company, you regularly monitor the

performance of your clients’ investments. You are preparing for a meeting with one of your

investors to discuss how their stocks have performed over the last year and changes they

should make to their portfolio. Your client has asked for information on four companies:

Intel, AMD, Time Warner, and Wal-Mart. You will find daily stock price and volume

information contained in the workbook.

Complete the following:

1. Open the workbook named ;located in the Chapter 3 folder, and then

save the file as Investment

2. Create a chart for each company using the appropriate stock chart sub-type. Be

certain

to use each chart sub-type only once.

3. Place each chart on a new sheet in the workbook.
4. On the Stock Comparison worksheet use the appropriate chart to compare the

change in stock price for each company over the last year.

5. Using the information on the Portfolio worksheet, prepare the appropriate chart to

display the relative contribution of each stock to the value of the portfolio.

6. Create an appropriate Combo chart on the Intel Summary worksheet that shows the

daily closing price and volume data. Add a linear and a 30 day moving average trend

line to the chart.

7. Save and close the Investment workbook.

Level 3 – Illustrating Patterns in Gas Prices for CKG Auto

You have been assigned to CKG Auto’s sales department as an analyst. The marketing

team for the new Safari Wildebeest Sport Utility Vehicle has become concerned that the

rising cost of gas will negatively affect vehicle sales. They are considering introducing

limited rebates at times when gas prices are especially high. The company might also

increase its marketing of the Safari Meer Cat, the new compact, sporty SUV the company

introduced late last year. Your supervisor has asked you to put together a report and

presentation showing the price per gallon of gas by region and large metropolitan areas.

The company is interested to see if there are any patterns in the data that can be used

to better time the rebate promotions. The file contains data on gas prices for

various regions and metropolitan areas around the country.

1. Open the workbook named ;located in the Chapter 3 folder, and then save

the file as Safari

2. Create a series of charts that compare gas prices by time of the year and by location.

The time can be in quarters, months, or week of the month, as you deem appropriate.

The location should be charted separately by region, by state, and by city.

3. For each location—region, state, and city—determine if a particular location has

higher gas prices than the others. Add a comment near each chart to explain your

findings.

4. Determine if a particular time of year has higher gas prices than the other times of the

year. Add a comment near each chart to explain your findings.

5. Create a bubble chart to illustrate the number of weeks that each city’s gas price is

above the gas price for the entire United States; the number of weeks that it is below

the gas price for the entire United States; and the average gas price in dollars per

gallon

for the year. Add appropriate titles, labels, and a legend to the chart.

6. Save and close the Safari workbook.

 

 

Chapter 4

 Applying Logic in Decision Making 212

Conceptual Review

1. Evaluate the following expressions:
2. =OR(FALSE,TRUE,TRUE)
3. =AND(3>5,TRUE)
4. =NOT(OR(FALSE,FALSE,TRUE))
5. =AND(A2>6,NOT(FALSE)) where A2 contains the value 25
6. Describe how you would format a cell so that if its value is less than 20 the cell would

be automatically bolded.

Answer Questions 3–15 using the following worksheet shown. The worksheet lists

budgeted and actual expenses for a trip to Boston. Assume that you will place your

answers in cells on the same worksheet.

3. Write a formula in cell E3 that can be copied down the column to determine (TRUE

or FALSE) if this item is over budget.

4. Write a formula to determine if all of the items are over budget.
5. Write a formula to determine if any item is over budget.
6. Without using a relational operator, write a formula in cell F3 that can be copied

down the column to determine if the food item is within budget.

7. What formula would you write if you wanted to apply a conditional format to cell A3

using the Formula Is method—such that the item name would be shaded in yellow if

the Actual cost of the item is at least $200?

8. Write a formula to determine if none of the items are over budget.
9. Write a formula to determine if (TRUE or FALSE) only the required items (R) are

within budget. Note that this formula does not have to work if the optional/required

categories are later modified.

10. Are the following two Boolean logical expressions equivalent? Why or why not?

=NOT(OR(E3:E8))

=AND(NOT(E3),NOT(E4),NOT(E5),NOT(E6),NOT(E7),NOT(E8))

11. Is the following formula valid? Why or why not?

=NOT(E3:E8)

12. What value would the following formula return:

=IF(D4<=C4,”within budget”,”over budget”)

13. What value would the following formula return:

=IF(SUM(D7:D8)<100,”go to both”,IF(SUM(D7:D8)>200,”go to neither”,

“choose one”))

14. Write a formula in cell G3 that can be copied down the column to return the

following:

• If this item has an actual cost of less than $300, then return the text “Minor

Component Cost”.

• If this item has an actual cost of $300 or more, then return the text “Major

Component Cost”.

15. Write the formula in cell H3 that was copied down the column to calculate the cost

of this component for a larger sales meeting based on the following:

• If this item is optional as indicated in column B, then the cost will be equal to the

original budgeted amount.

• If this item is required as indicated in column B, then the cost will be two times

the original budgeted amount.

Case Problems

Level 1 – Evaluating Job Applicants for Winston, Winston & Coombs

You work in the Human Resources Department (or simply HR) for the accounting firm

Winston, Winston & Coombs. The firm has recently increased its client base and hired

several university graduates for entry-level positions. The HR manager has established a

formal process for evaluating job applicants. This process takes into account the applicant’s

academic performance and work experience, as well as the impression made during the

personal interview. In addition, all applicants are given a skills-based exam to determine

their proficiency in spreadsheet and database applications. Because some applicants are

not business majors but might be otherwise qualified for a position, the exam also covers

some basic business concepts in accounting, finance, and marketing. You have been asked

to evaluate the information on the candidates being considered.

Each job application provides the following information:

• College GPA (valid scores range from to )
• Major Code, universal standardized system that indicates the applicant’s undergraduate

major; for example, Engineering=1, Business=2, Economics=3, Physical Science=4,

and so on (valid codes for majors are 1 through 100)

• The total number of references submitted by the applicant
• A Personal Interview Rating
• If the applicant has previous work experience (TRUE or FALSE)
• The Employment Exam score (valid scores are between 300 and 800)
• The undergraduate school ranking (compared with all colleges across the country)

The HR manager has established criteria to determine if an applicant will be automatically

disqualified or automatically hired, or if no decision is made. The criteria, which are

applied in order, are described in the following list.

An applicant is automatically disqualified if any of the following criteria are TRUE:

• The applicant has submitted an invalid GPA score, Employment Exam score, or

Major Code.

• The applicant has a GPA less than
• The applicant provided fewer than two references.
• The applicant has an Employment Exam score below 600.
• The applicant has a Personal Interview Rating of less than 3.

An applicant is automatically hired if all of the following criteria are TRUE:

• The applicant has not been automatically disqualified.
• The applicant has a GPA score over
• The applicant has a Major Code between 1 and 25 (inclusive).
• The applicant graduated from one of the top 50 schools (ranking of 50 or less).
• The applicant has an Employment Exam score above 720.
• The applicant has a personal interview rating of 4 or higher.
• The applicant has prior work experience.

If an applicant is neither automatically disqualified nor automatically hired, the applicant’s

status is undecided.

Complete the following:

1. Open the workbook named ;located in the Chapter 4 folder, and then

save the file as WWC Hiring

2. Write a formula in cell I4 that can be copied down the column to determine if (TRUE

or FALSE) any of the following scores or codes listed for this applicant are invalid:

GPA, Major Code, Employment Exam. (Hint: Use the information provided in the

problem description to determine the appropriate criteria.)

3. Write a formula in cell J4 that can be copied down the column to determine if (TRUE or

FALSE) the applicant should be automatically disqualified based on the given criteria.

4. Write a formula in cell K4 that can be copied down the column to determine if this

candidate

is not automatically disqualified. (Hint: Use the results determined in

Step 3.)

5. In cell L4, write a formula that can be copied down the column to determine if

(TRUE or FALSE) the candidate should be automatically hired based on the given

criteria. (Hint: For criteria between two values, test that the value is both >= the

lower limit and <= the higher limit.)

6. Write a formula in cell M4 that can be copied down the column to determine if this

candidate is not automatically hired. (Hint: Use the results determined in Step 5.)

7. Write a formula in cell N4 that can be copied down the column to determine if no

decision is made on this applicant. Recall that no decision is made if the applicant is

both not automatically disqualified (K) and not automatically hired (M).

8. Write a formula in cell I14 that can be copied across the row (through column N) to

determine if all of the applicants have invalid scores.

9. Write a formula in cell I15 that can be copied across the row (through column N) to

determine if any of the applicants have invalid scores.

10. To summarize the results, write a formula in cell I16 that displays the total number

of applicants who have invalid scores. Copy this formula across the row (through

column

N). This formula should automatically update if any of the scores or criteria

are later modified.

11. Apply conditional formatting to highlight the important points, as follows:
12. Highlight all of the TRUE values in the Automatically Disqualified column

(J4:J12) using a dark green and bold text format.

1. Use gradient fill blue data bars to highlight the Personal Interview Rating scores

of the applicants.

1. Highlight the name of any applicant with an Employment Exam Score of more

than 720 using a light blue background.

12. Save and close the WWC Hiring workbook.

Level 2 – Estimating Painting Job Costs for NT Painting & Sons

For the past year, you have been working with a medium-sized painting contractor, NT

Painting & Sons, doing everything from running errands to cutting the weekly paychecks

and filing the appropriate quarterly employment withholding forms with the IRS. Given

your knowledge of spreadsheets, your boss has asked you to create an Excel worksheet for

estimating the price of individual painting jobs. Your boss wants the worksheet to contain

some basic input information and automatically calculate an estimated price so that a

customer can quickly know the cost of the proposed work. The variables to be considered

are as follows:

• The dimensions of each room—length, width, and height
• The condition of the wall surfaces, where 1 represents excellent, 2 represents reasonable

but has some peeling and/or old paint, and 3 represents poor condition with major

holes, peeling, and/or very old paint

• Whether or not the requested new color is lighter than the existing wall color (TRUE

or FALSE)

• Grade of paint being requested—premium, superior, or economy

Complete the following:

1. Create a new workbook, and save it as Paint ;in the Chapter 4 folder.

Set up the worksheet with the columns and data shown in Table Include a meaningful

title at the top of the worksheet. Ultimately, this worksheet will be used as a

template and filled out on site by the painter.

Table : Worksheet data for the paint calculator

Room Length in

Feet

Width in

Feet

Height in

Feet

Square

Feet (SF)

of Wall/

Ceiling

Wall

Condition

New

Color

Lighter

Paint

Quality

Great

Room

30 14 12 3 FALSE Premium

Bedroom1 18 11 8 1 FALSE Superior

Bedroom2 14 12 8 1 FALSE Economy

Bath 8 6 8 2 TRUE Premium

© 2014 Cengage Learning

To complete Steps 2–10, you need to calculate the individual component costs by

room, writing all formulas so that they can be copied down the column. List all other

inputs that are needed for your calculations on a separate worksheet

in the workbook—

named appropriately. Assume all wall surfaces, including the ceiling area, are

to be included when calculating repair and painting costs. Remember, your formulas

will need to work when new quantities are substituted

into the data-entry area.

2. In the column you’ve already listed, calculate the total square footage (sf) of walls

and ceiling. If a room is 10′ by 12′ with an 8′ ceiling height, it would have two walls

that are 10′ × 8′ (total of 160 sf) and two walls that are 12′ × 8′ (total of 192 sf),

and a ceiling of 10′ × 12′ (120 sf) for a total of 472 sf. Do not subtract any area for

windows, doors, and so on.

3. To the right of the Paint Quality column, calculate the cost of wall repairs and primer.

Only walls with a wall condition of poor (3) will require wall repair and primer. This

cost is estimated as $ per sf of wall/ceiling. If no primer is required, a value of 0

should be entered. Remember to list any additional inputs on a separate worksheet as

described above.

4. In an adjacent column, calculate the cost of the first coat of paint. If the condition of

the wall is 1, the cost of paint is $ per sf; if the condition of the wall is 2, the cost

of paint is $ per sf; otherwise, the cost is $ per sf.

5. In an adjacent column, calculate the cost of the second coat of paint based on the

following criteria:

• If the condition of the wall is 3, a second coat of paint will be required at

$ per sf.

• If the condition of the walls is not poor (3), but new wall color is lighter than the

existing color, a second coat of paint will be required at $ per sf.

• Otherwise, no second coat will be required, and a value of $0 should be entered.

6. In an adjacent column, calculate the cost adjustment for paint quality based on the

following criteria:

• If premium paint is used, add $ per sf.
• If economy paint is used, deduct $ per sf.

7. In an adjacent column, calculate the total cost to paint this room (primer, first coat,

second coat, and adjustments for paint quality).

8. In an adjacent column, determine if (TRUE or FALSE) this is a low-priced room.

A low-priced room is one that is estimated to cost less than $300.

9. Create a row below the data that totals the costs of each item (primer, first coat, and

so on) and then a grand total of all items for all rooms.

10. Because larger jobs have certain economies of scale in setup and cleanup, a discount is

given based on these estimated values to jobs based on their total size. Just below the

grand total, determine the total discounted price of the job based on the following:

• If the total cost of the painting job is less than $800, then there is no discount.
• If the total cost of the painting job is at least $800 but less than $2,000, then a

5% discount will be given (discount is calculated based on the grand total cost

for all items and all rooms).

• If the total cost of the painting job is at least $2,000 but less than $5,000, then

a 10% discount will be given.

• If the total cost of the painting job is $5,000 or more, then a 15% discount will

be given.

11. Format your worksheets so that they are easy to read and information is clearly

identifiable.

Highlight the result of Step 10 in yellow.

12. Save and close the Paint workbook.

Level 3 – Analyzing Dealership Promotions for CKG Auto

CKG Auto runs several promotions each year to reward dealerships for their sales efforts,

sometimes on specific car models and other times for overall sales. CKG Auto is running

three different promotions for large dealerships, based on performance over this past

calendar year. Small and medium-sized dealerships have similar promotions but based on

different expected volumes and rebate percentages. The promotions are as follows:

• A rebate on shipping expenses based on exceeding expected quarterly volumes: These

are savings CKG Auto realizes from its trucking carriers and has decided to pass along

as a reward to dealerships that have exceeded expectations. Rebates for each quarter

were set by management as follows: 1st quarter, $75 per car sold (actual volume);

2nd quarter, $80 per car sold; 3rd quarter, $85 per car sold; and 4th quarter, $80 per

car sold. Dealerships are awarded the rebate on a quarter-by-quarter basis, only for

quarters where their actual sales exceeded expected volumes for that quarter. Expected

sales volumes for large dealerships for each quarter are as follows:

– 1st Quarter: 400

– 2nd Quarter: 410

– 3rd Quarter: 415

– 4th Quarter: 390

• An overall sales volume bonus based on exceeding expected annual volumes:

Dealerships that exceeded the expected annual sales volume by more than 15% are

awarded a $10,000 bonus. Dealerships that exceeded the expected annual sales volume

by 15% or less are awarded a $5,000 bonus. Otherwise, no bonus is awarded ($0).

• A “Best in Class” bonus of $8,000 awarded to the one dealership with the highest

overall sales volume in its class

You have been asked to set up a worksheet to record the dealer information for the past year

and apply the appropriate promotions to each dealership. The actual dealership quarterly

sales volumes have already been entered in a worksheet. Now, you will finalize the analysis.

Complete the following:

1. Open the workbook named ;located in the Chapter 4 folder, and

then save the file as Promo Large This past year’s quarterly sales

volumes

and expected sales volumes for large dealerships have already been entered

into this workbook. Complete the analysis using any additional columns and/or rows

as you deem necessary. All formulas should work when copied either across or down,

as needed. Include titles in each column and/or row to identify the corresponding

data. Add any appropriate formatting to make the worksheet easy to read.

2. Insert rows at the top of the worksheet to create an input area where you can list the

inputs such as bonus amounts, shipping rebates, and so on. List the inputs explicitly

and use only one worksheet for this task, so that any inputs can be easily displayed

for management and then later copied and modified to calculate the promotions for

both the medium and small dealership classes. Insert rows as needed, and be sure to

clearly label each input so that the data can be interpreted and modified easily next

year. Wrap text and format the data as needed.

3. In a column adjacent to the quarterly sales data, calculate the corresponding annual

sales volume for each dealership.

4. Calculate the value of the shipping rebate for each dealer for each quarter (use four

new columns). This should require only one formula that can be copied down the

column and across the row. Be sure your inputs are set up so that this can be easily

accomplished. Remember, dealers will only receive rebates in quarters where their

actual quarterly sales volumes exceeded expected sales volumes. In an adjacent column,

determine the total value of the shipping rebate for all four quarters by dealership.

5. Analyze the quality of these volume estimates by categorizing the quality of the

annual volume estimate versus the actual annual volumes for each dealership into the

following categories:

• Display “Excellent” if the estimate is within 10% (higher or lower) of the actual

sales volume. (Hint: For example, if you wanted to determine if the estimated

value 26 is within +/– 25% of the sales value of 40, you would need to test this

value to make sure that both 26>=40–.25*40 and 26<=40+.25*40.)

• Display “Good” if the estimate is greater than 10% higher or lower, but within

20% higher or lower of the actual volume.

• Display “Poor” if the estimate is greater than 20% higher or lower.

6. In an adjacent column or columns, calculate the value of the annual sales volume

bonus for each dealership.

7. In an adjacent column, calculate the value of the “Best in Class” bonus for each

dealership.

(Only the dealership with the highest annual sales volume will receive

this; all others will receive $0.)

8. In a row below the data, calculate the total values for all dealers for sales volume,

shipping rebates, sales, and bonuses.

9. In an adjacent column, determine if (TRUE or FALSE) this dealership received

money during this year for both a shipping rebate and a sales volume bonus. Copy

the formula down the column to obtain the corresponding value for each dealership.

10. Skipping one row below the totals, in the column just used in Step 9, determine

(TRUE or FALSE) if none of the dealerships received both shipping rebates and

a volume bonus. Label the row accordingly.

11. Just below the result of Step 10, determine if only dealerships with Excellent estimate

qualities (determined in Step 5) received both shipping rebates and a sales volume

bonus. This formula need not work if any of the input data or formulas are later

updated. Label the row accordingly.

12. Again, skip a row below the data. Then, in the following rows, determine for

each rebate/bonus the number of dealerships receiving this rebate/bonus and

the average

value of the bonus (include dealerships that did not earn a bonus in the

average calculation).

13. Save and close the Promo Large workbook.

 

 

Chapter 5

 Retrieving Data for Computation, Analysis, and Reference 282

 

Conceptual Review

Answer Questions 1–10 as True or False.

1. _____ The lookup_value of a HLOOKUP function can be a contiguous cell range.
2. _____ In a VLOOKUP formula with a TRUE lookup type, the first column of the

lookup table referenced must be in ascending order to retrieve the correct value.

3. _____ The result_vector of a LOOKUP function must be sorted in ascending order.
4. _____ Reference and Lookup functions may not contain nested functions as

arguments.

5. _____ The default range_lookup type for the VLOOKUP and HLOOKUP functions

is TRUE.

6. _____ Excel matches the lookup_value “tom” with the entry “TOM” in a lookup

table.

7. _____ The row and column arguments in the INDEX function can be numeric values,

Boolean values, or text.

8. _____ The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the value

in cell D3.

9. _____ The formula =AVERAGE(CHOOSE(1,B12:D17,B22:D17)) averages the

value 1 with the values in cells B12 to D17 and B22 to D17.

10. _____ The formula =MATCH(40,{10,20,40,50},0) returns the value 3.

Answer the following questions.

11. What happens when Excel is solving a HLOOKUP formula with a FALSE

range_lookup type and does not find an exact match in the lookup table?

12. What is the difference between the LOOKUP function and the VLOOKUP or

HLOOKUP function?

13. Which Excel function should you use when you want to look up a value from a twodimensional

table, where both the columns and rows can be varied?

14. Write a formula to choose the name of the third day of the week from the list starting

with Sunday, Monday, Tuesday,…Saturday.

15. What function returns the relative position of an item from a list?

Base your answers for Questions 16–18 on the Pricing and Delivery worksheets shown

here. Cells Pricing!B2:C9 list the costs per copy based on the total number of copies

being made. The price for less than 10 copies corresponds to $ per copy, the price

for at least 10 copies but less than 25 copies corresponds to $ per copy, etc.

Pricing worksheet

Delivery worksheet

16. Write a formula in cell C13 in the Pricing worksheet to determine the total cost of

making copies for this order (275 copies). Write the formula so that it works when

copied into cells C14:C15.

17. The delivery charges used in the Delivery worksheet are as follows:

• For orders under $10, there is a $6 delivery fee.
• For orders at least $10 but less than $40, there is a $ delivery fee.
• For orders over $40, delivery is free of charge.

Create a lookup table in the Delivery worksheet, so that you can use a lookup function

to calculate the delivery cost for each order. Organize the table in a horizontal format,

as shown here:

18. Write a formula in cell D13 in the Pricing worksheet to look up the correct delivery

cost using the lookup table you created in Question 17. Write the formula so that

it can be copied down the column.

Base your answers for Questions 19–20 on the Scores and Grades worksheets shown here.

Scores worksheet Grades worksheet

19. As shown in the Grades worksheet, final grades are determined using the following

grading scheme:

• Students earning over 900 points receive an A.
• Students earning less than 900 points but at least 800 points receive a B.
• Students earning less than 800 points but at least 700 points receive a C.
• Students earning less than 700 points but at least 600 points receive a D.
• Students earning less than 600 points receive an F.

Write a formula in cell C3 in the Scores worksheet that determines the final grade for the

first student based on the grading scheme. Use the appropriate Reference and Lookup

function and write the formula so that it can be copied down the column.

20. Explain the difference between the lookup table in cells A3:E4 of the Grades worksheet

and the lookup table in cells A6:E7 in the same worksheet.

Case Problems

Level 1 – Evaluating Tax Rates for the Freedom Group

Note: The information in this Case Problem does not reflect any actual tax rates, tax rate

calculation methodologies, or IRS policies, and should not be constituted as tax advice.

The Freedom Group is a “think tank” in Washington, , that provides research

data to lobbyists and members of the federal government. As a tax analyst for the

Freedom Group, your job is to report how tax policies affect federal, state, and local

revenues. A lobbyist asks you to study how alternate flat tax rate proposals could affect

the total taxes owed and residual amount of taxes owed on April 15, based on a list of

sample individual tax return information. A flat tax rate is a rate applied to the total

income earned. For example, a flat tax rate of 10% on $20,000 in income is $2,000.

The current graduated system applies different percentages to ranges of income for

each taxpayer. For example, a graduated tax on a $20,000 income might be as follows:

0% of the first $5,000, 10% of the amounts between $5,001 and $10,000, and $15%

of the amounts over $10,000.

A worksheet in an Excel workbook named has been set up listing sample tax

return data and the actual amount of taxes owed based on the current tax rates (column F).

The worksheet also includes schedules for the proposed flat tax rates, penalties, and state

allowances.

To complete this study, your task is to analyze the total taxes owed based on two new

flat-rate tax alternatives. In addition, you will compare the residual amount owed and

penalties applied to that residual amount for each alternative. The residual amount owed

on April 15 is calculated as follows: actual total tax owed minus the sum of the actual

withholding taxes paid and the estimated taxes paid.

Withholding taxes are those amounts withheld from an employee’s paycheck each pay

period and remitted to the IRS by the employer. Estimated taxes are direct tax payments

made by taxpayers to the IRS each quarter. Each April 15, taxpayers calculate the amount

they owe in taxes and then subtract out any payments (withholding and estimated) to

determine the actual unpaid tax. Depending on the amount of this residual unpaid tax,

penalties might be applied. Complete the following:

1. Open the workbook named ;located in the Chapter 5 folder, and then

save the file as Freedom Tax

2. In cell G12, write a formula that uses the Alternate 1 flat tax rate to determine the

total dollar value of the tax for the income in cell B12. As detailed in the Flat Tax

Rate table (cells A1:F4), this tax scheme calculates taxes by multiplying the total

income by the corresponding rate. For example, incomes below $30,000 pay no tax;

incomes of at least $30,000 but less than $50,000 pay 5% of the income in taxes;

incomes of at least $50,000 but less than $80,000 pay 9% of the income in taxes;

incomes of at least $80,000 but less than $180,000 pay 14% of the income in taxes;

and incomes of $180,000 or more pay a 22% tax rate. Write the formula so that it can

be copied down the column, and then copy it to cells G13:G21.

3. In cell H12, write a formula that uses the Alternative 2 flat tax rate to determine the

total dollar value of the tax for the income in cell B12. As detailed in the Flat Tax

Rate table (cells A1:F4), this tax scheme also calculates taxes by multiplying the total

income by the corresponding rate. For example, incomes below $30,000 pay no tax;

incomes of at least $30,000 but less than $50,000 pay 6% of the income in taxes;

incomes of at least $50,000 but less than $80,000 pay 10% of the income in taxes;

incomes of at least $80,000 but less than $180,000 pay 16% of the income in taxes;

and incomes of $180,000 or more pay a 26% tax rate. Write the formula so that it can

be copied down the column, and then copy it to cells H13:H21.

4. In cell I12, write a formula that calculates the amount of unpaid taxes the first taxpayer

still owes on April 15. The unpaid taxes are based on the actual amount of taxes

owed, the actual withholding taxes paid, and estimated taxes paid. Write the formula

so that it can be copied down the column to calculate this amount for each taxpayer.

Also, write the formula so that it can be copied across the row to determine the

unpaid amount based on the taxes owed for the Alternative 1 and Alternative 2 flat

tax calculations.

(Hint : Assume the same withholding and estimated

taxes paid.)

Copy the formula to cells I13:I21 and to cells J12:K21.

5. In cell L12, write a formula that determines the actual penalty owed based on

the penalty

schedule (cells H1:I8). For example, unpaid tax balances of less than

$100 owe no penalty, and unpaid tax balances of at least $100 but less than

$1,000 are charged a penalty of 3% of the unpaid tax amount. The range H3:I8 is

named Penalty. (Hint: Use an IF function to determine if the unpaid tax amount

is negative,

indicating that the IRS owes the taxpayer a refund and, therefore, no

penalty applies.) Copy this formula

both down the column to calculate the penalty

for the corresponding ID# and across the row to determine the penalties based on

each alternative tax scheme.

6. In row 23, calculate the total values for each category (F23:N23) for all 10 tax returns.
7. As part of the flat-rate tax, one possible scheme would include a state allowance

to balance the high and low cost of living. The amount of the allowance is listed

by state in cells A7:B9. If the list does not contain the appropriate state, the error

message

#N/A should be displayed. In cell O12, write a formula that determines

the state allowance for this taxpayer. Write the formula so that it can be copied down

the column,

and then copy it to cells O13:O21.

8. Highlight in pink the column of the tax scheme that is most favorable to very highincome

taxpayers (Actual, Alternative 1, or Alternative 2).

9. Format the worksheet to make it easy to read and understand.
10. Save and close the Freedom Tax workbook.

Level 2 – Calculating Travel Costs at America Travels

As a new sales associate at America Travels travel agency, you assist travel agents in finding

the best fares for corporate customers. As a high-volume travel agency, America Travels

has negotiated several premium discounts with airlines, which you pass along to your

customers who do a large amount of business with you. Compared to standard business

fares, these discounts can amount to a substantial savings and do not have minimum stay

requirements.

Convertto, Inc. is a significant customer with a travel department that prices its own fares

online and then contacts America Travels to see if it can provide a better rate. Convertto

sends an Excel workbook containing a list of proposed trips to America Travels each

day by 10 via email, and requests price quotes for these trips by noon. Convertto

has corporate offices in New York, San Francisco, and London, which is where most

of its travel originates. When the Convertto workbook arrives, America Travels sales

associates must work hard to enter the information that Convertto requests and return

the workbook to the company on time.

For several months, America Travels has been working on a more automated method of

replying to these inquiries. Another sales associate compiled fare data by flight number

and researched airport fees. This information has been set up on separate worksheets in

an Excel workbook. Your task is to complete the last piece of the project, which is to use

the information on the customer’s fare request sheet and automatically calculate the best

fares. For this project, assume that all flight numbers are included on the flights list.

The following worksheets have already been created in an Excel workbook named

:

• Requests, which contains a sample request form from Convertto, including the

date, traveler’s name, flight number, and corporate-rate fare.

• Flights, which contains a list of flights sorted by flight number and the associated

departure city, arrival city, and base fare.

• Fees, which contains a table listing fare categories and airport fees associated

with ticket prices. These airport fees will be added to the price of each ticket.

For example,

fares of less than $ fall into fare category 1 and have a

$15 airport

fee, and fares from $ to $ also fall into fare category 1

but have a $25 airport fee.

• Discounts, which contains a two-dimensional table of discount categories based on

the fare category and the weekday of the ticket.

Your task is to include formulas in the Requests worksheet to provide fare information.

All formulas must work when copied down the column to determine the requested

information for each travel request. Complete the following:

1. Open the workbook named ;located in the Chapter 5 folder, and

then save the file as Convertto Travel

2. In cell E3 of the Requests worksheet, write a formula that retrieves the name of the

departure city for this flight. Copy the formula to cells E4:E6.

3. In cell F3, write a formula that retrieves the name of the arrival city for this flight.

Copy the formula to cells F4:F6.

4. In cell G3, write a formula that retrieves the base fare for this flight based on the data

given on the Flights worksheet. Copy the formula to cells G4:G6.

5. In cell H3, write a formula that determines the day of the week of this flight using the

WEEKDAY(date) function. Using 1 through 7, the WEEKDAY function returns 1

for Sunday, 2 for Monday, and so on. (Refer to Excel Help for more details on using

the WEEKDAY function.) Copy this formula to cells H4:H6.

4. In cell I12, write a formula that calculates the amount of unpaid taxes the first taxpayer

still owes on April 15. The unpaid taxes are based on the actual amount of taxes

owed, the actual withholding taxes paid, and estimated taxes paid. Write the formula

so that it can be copied down the column to calculate this amount for each taxpayer.

Also, write the formula so that it can be copied across the row to determine the

unpaid amount based on the taxes owed for the Alternative 1 and Alternative 2 flat

tax calculations.

(Hint : Assume the same withholding and estimated

taxes paid.)

Copy the formula to cells I13:I21 and to cells J12:K21.

5. In cell L12, write a formula that determines the actual penalty owed based on

the penalty

schedule (cells H1:I8). For example, unpaid tax balances of less than

$100 owe no penalty, and unpaid tax balances of at least $100 but less than

$1,000 are charged a penalty of 3% of the unpaid tax amount. The range H3:I8 is

named Penalty. (Hint: Use an IF function to determine if the unpaid tax amount

is negative,

indicating that the IRS owes the taxpayer a refund and, therefore, no

penalty applies.) Copy this formula

both down the column to calculate the penalty

for the corresponding ID# and across the row to determine the penalties based on

each alternative tax scheme.

6. In row 23, calculate the total values for each category (F23:N23) for all 10 tax returns.
7. As part of the flat-rate tax, one possible scheme would include a state allowance

to balance the high and low cost of living. The amount of the allowance is listed

by state in cells A7:B9. If the list does not contain the appropriate state, the error

message

#N/A should be displayed. In cell O12, write a formula that determines

the state allowance for this taxpayer. Write the formula so that it can be copied down

the column,

and then copy it to cells O13:O21.

8. Highlight in pink the column of the tax scheme that is most favorable to very highincome

taxpayers (Actual, Alternative 1, or Alternative 2).

9. Format the worksheet to make it easy to read and understand.
10. Save and close the Freedom Tax workbook.

Level 2 – Calculating Travel Costs at America Travels

As a new sales associate at America Travels travel agency, you assist travel agents in finding

the best fares for corporate customers. As a high-volume travel agency, America Travels

has negotiated several premium discounts with airlines, which you pass along to your

customers who do a large amount of business with you. Compared to standard business

fares, these discounts can amount to a substantial savings and do not have minimum stay

requirements.

Convertto, Inc. is a significant customer with a travel department that prices its own fares

online and then contacts America Travels to see if it can provide a better rate. Convertto

sends an Excel workbook containing a list of proposed trips to America Travels each

day by 10 via email, and requests price quotes for these trips by noon. Convertto

has corporate offices in New York, San Francisco, and London, which is where most

of its travel originates. When the Convertto workbook arrives, America Travels sales

associates must work hard to enter the information that Convertto requests and return

the workbook to the company on time.

For several months, America Travels has been working on a more automated method of

replying to these inquiries. Another sales associate compiled fare data by flight number

and researched airport fees. This information has been set up on separate worksheets in

an Excel workbook. Your task is to complete the last piece of the project, which is to use

the information on the customer’s fare request sheet and automatically calculate the best

fares. For this project, assume that all flight numbers are included on the flights list.

The following worksheets have already been created in an Excel workbook named

:

• Requests, which contains a sample request form from Convertto, including the

date, traveler’s name, flight number, and corporate-rate fare.

• Flights, which contains a list of flights sorted by flight number and the associated

departure city, arrival city, and base fare.

• Fees, which contains a table listing fare categories and airport fees associated

with ticket prices. These airport fees will be added to the price of each ticket.

For example,

fares of less than $ fall into fare category 1 and have a

$15 airport

fee, and fares from $ to $ also fall into fare category 1

but have a $25 airport fee.

• Discounts, which contains a two-dimensional table of discount categories based on

the fare category and the weekday of the ticket.

Your task is to include formulas in the Requests worksheet to provide fare information.

All formulas must work when copied down the column to determine the requested

information for each travel request. Complete the following:

1. Open the workbook named ;located in the Chapter 5 folder, and

then save the file as Convertto Travel

2. In cell E3 of the Requests worksheet, write a formula that retrieves the name of the

departure city for this flight. Copy the formula to cells E4:E6.

3. In cell F3, write a formula that retrieves the name of the arrival city for this flight.

Copy the formula to cells F4:F6.

4. In cell G3, write a formula that retrieves the base fare for this flight based on the data

given on the Flights worksheet. Copy the formula to cells G4:G6.

5. In cell H3, write a formula that determines the day of the week of this flight using the

WEEKDAY(date) function. Using 1 through 7, the WEEKDAY function returns 1

for Sunday, 2 for Monday, and so on. (Refer to Excel Help for more details on using

the WEEKDAY function.) Copy this formula to cells H4:H6.

4. In cell I12, write a formula that calculates the amount of unpaid taxes the first taxpayer

still owes on April 15. The unpaid taxes are based on the actual amount of taxes

owed, the actual withholding taxes paid, and estimated taxes paid. Write the formula

so that it can be copied down the column to calculate this amount for each taxpayer.

Also, write the formula so that it can be copied across the row to determine the

unpaid amount based on the taxes owed for the Alternative 1 and Alternative 2 flat

tax calculations.

(Hint : Assume the same withholding and estimated

taxes paid.)

Copy the formula to cells I13:I21 and to cells J12:K21.

5. In cell L12, write a formula that determines the actual penalty owed based on

the penalty

schedule (cells H1:I8). For example, unpaid tax balances of less than

$100 owe no penalty, and unpaid tax balances of at least $100 but less than

$1,000 are charged a penalty of 3% of the unpaid tax amount. The range H3:I8 is

named Penalty. (Hint: Use an IF function to determine if the unpaid tax amount

is negative,

indicating that the IRS owes the taxpayer a refund and, therefore, no

penalty applies.) Copy this formula

both down the column to calculate the penalty

for the corresponding ID# and across the row to determine the penalties based on

each alternative tax scheme.

6. In row 23, calculate the total values for each category (F23:N23) for all 10 tax returns.
7. As part of the flat-rate tax, one possible scheme would include a state allowance

to balance the high and low cost of living. The amount of the allowance is listed

by state in cells A7:B9. If the list does not contain the appropriate state, the error

message

#N/A should be displayed. In cell O12, write a formula that determines

the state allowance for this taxpayer. Write the formula so that it can be copied down

the column,

and then copy it to cells O13:O21.

8. Highlight in pink the column of the tax scheme that is most favorable to very highincome

taxpayers (Actual, Alternative 1, or Alternative 2).

9. Format the worksheet to make it easy to read and understand.
10. Save and close the Freedom Tax workbook.

Level 2 – Calculating Travel Costs at America Travels

As a new sales associate at America Travels travel agency, you assist travel agents in finding

the best fares for corporate customers. As a high-volume travel agency, America Travels

has negotiated several premium discounts with airlines, which you pass along to your

customers who do a large amount of business with you. Compared to standard business

fares, these discounts can amount to a substantial savings and do not have minimum stay

requirements.

Convertto, Inc. is a significant customer with a travel department that prices its own fares

online and then contacts America Travels to see if it can provide a better rate. Convertto

sends an Excel workbook containing a list of proposed trips to America Travels each

day by 10 via email, and requests price quotes for these trips by noon. Convertto

has corporate offices in New York, San Francisco, and London, which is where most

6. Airline tickets are assigned a fare category based on the base fare ticket price

(

column G) and the categories listed on the Fees worksheet. In cell I3, write a formula

that determines the fare category for this ticket. Copy the formula to cells I4:I6.

7. The Discounts worksheet contains a two-dimensional table that has been set up

to find the discount category of a ticket based on the weekday of travel and the

fare category.

In cell J3, write a formula that determines the discount category for

this ticket. Copy the formula to cells J4:J6.

8. In a separate area of the Discounts worksheet, create a horizontal lookup table based

on the following discount information:

• Fare discount category AA: 0% discount of the published base fare
• Fare discount category X: 25% discount of the published base fare
• Fare discount category Y: 50% discount of the published base fare
• Fare discount category Z: 65% discount of the published base fare

9. In cell K3 of the Requests worksheet, write a formula that determines the discounted

fare price (base fare minus discount) of this flight using the table you created in the

Discounts worksheet. Fares should be rounded to the nearest dollar. (Hint: Do not

use an IF function.) Copy this formula to cells K4:K6.

10. In cell L3, calculate the airport fee based on the fee schedule in the Fees worksheet.

Note that the airport fee is based on the discounted fare. Copy this formula to

cells L4:L6.

11. In cell M3, calculate the total ticket price that America Travels can obtain (discounted

fare plus airport fees). Copy this formula to cells M4:M6.

12. In cell N3, compare the America Travels total ticket price to the corporate fare that

Convertto found. Return a TRUE value if the America Travels price is less than the

corporate fare Convertto was offered. Copy this formula to cells N4:N6.

13. Format your worksheet so that it is easy to read and understand.
14. Save and close the Convertto Travel workbook.

Level 3 – Creating a Cost Estimate Form for CKG Auto

As part of its product line, CKG Auto has nine basic models, each with different options

and features. Although many car buyers are concerned about the initial cost of a car,

customers are also becoming increasingly concerned about the yearly operating expenses

they can expect. They frequently want to compare two or more purchase options to see

how much a car costs to run each year. Although they might be willing to spend an extra

$5,000 to purchase a sports utility vehicle or luxury car with many options, customers

might reconsider when they calculate the annual cost of gas and insurance.

Fuel economy information posted on each car’s window is usually stated within a range

of 5 to 10 miles per gallon. For example, CKG Auto’s compact car, the Voltage, lists

30-35 miles per gallon in the city and 37-42 miles per gallon on the highway. This

and other car sticker information is difficult to translate into annual costs for gasoline,

maintenance, insurance, and other operating costs. Many CKG Auto dealerships have

asked for an easy way to provide operating cost information to their customers, much the

same way they can give them base car costs and option prices.

To this end, CKG Auto has decided to develop an Excel workbook that can calculate

costs associated with the first three years of operating a new car, including gas expenses,

maintenance, and insurance premiums. Although these costs are estimates, they will give

customers a good understanding of what to expect.

Your task is to work with the ;workbook, which is located in the Chapter 5

folder. Open the workbook and save it as CKG Operating Develop a

worksheet named Estimate Form that salespeople can complete to help their customers

calculate annual operating costs for a selected vehicle. CKG Auto completed a preliminary

analysis summarizing the data required from the salespeople, the information needed for

the calculations, and the desired data outputs. The estimate form should compare the

operating costs of the selected vehicles. When this form is complete, it should display

the data inputs and outputs, and provide space to compare up to five vehicles. If less

than five vehicles are listed, blank cells should be displayed in lieu of error messages.

Customer Information – Input on Estimate Form

Salespeople will enter the following data about the customer and use the data provided

in the Test Customer Profile for purposes of calculations:

• Expected number of driving miles per year
• Type of driving: Highway (speeds of 55 mph and over), Mixed (balance of highway

and city driving), or City (speeds of 45 mph and below)

• State of residence
• Residential status: City, Suburban, or Rural
• Driving safety record: Excellent, Average, or Poor
• Gas price adjustment percentage, which is a multiplier that accounts for major changes

in gas prices (to calculate this multiplier, divide the regular gas price at your local gas

station by 4)

Test Customer Profile

• Expected number of driving miles per year: 22,500
• Type of driving: Mixed
• Purchaser’s state: CA
• Purchaser’s residential status: City
• Purchaser’s driving safety record: Excellent
• Gas price adjustment percentage (to calculate the percentage, divide the current gas

price in your neighborhood by ).

Vehicle Inputs – Input on Estimate Form

In the Estimate Form worksheet, salespeople will enter the following data about each

vehicle (design the worksheet for up to five vehicles):

• Vehicle model number
• Engine: number of cylinders (4, 6, 8, or turbo)

Test Vehicles Profile

• Model 2: 4 cylinders
• Model 3: 6 cylinders
• Model 6: 8 cylinders
• Model 9: turbo

Data Tables Provided in Workbook

Data for car models, mileage, regions, gas pricing, and insurance that will be required

to obtain the necessary output is available in the workbook on the following

worksheets:

• Models, which lists available car models including description, available engines,

weight class, and expected maintenance base costs for the first three years (excluding oil

changes). For some models these maintenance costs are zero, reflecting dealer incentives

to buyers that include free maintenance over a specified period. Also included is

the number of recommended miles per oil change and the cost per oil change.

• Mileage, which includes the gas mileage schedules for each driving usage type. Each

schedule lists the gas mileage in miles per gallon based on the weight class and engine.

• States, which lists states and their associated regions. (Use Region to determine gas

pricing.)

• Gas Prices, which includes regional yearly average gas prices based on $4 per gallon

in the Northeast. This value should be adjusted based on the multiplier supplied as

part of the user inputs (current gas price/$4), assuming prices change proportionally

throughout the country. A multiplier of greater than 1 increases the price, and a multiplier

of less than 1 reduces the price. The estimated $/gallon is equal to the $/gallon

from the Gas Prices table (which varies by region) times this multiplier.

• Insurance, which lists regional insurance rate estimates for new cars. These base prices

can then be adjusted based on driver safety records and residential status.

Create appropriate named ranges on the worksheets as needed.

Estimate Form Outputs

The Estimate Form worksheet should include the following outputs (for each car model)

for up to five selected cars:

• Car make and description based on model number input
• Estimated annual cost of gas based on the selected type of driving, weight class, annual

miles traveled, gas price, and engine type

• Estimated annual insurance premium based on the owner’s region of residence, driving

record, and residential status (Hint : Include an intermediate calculation in the form

that looks up the region number associated with the state of residence, and then refer

to the cell that contains this result in the insurance formula.)

• Estimated average annual maintenance cost for the first three years of operation,

based

on the selected car model and annual miles traveled (total 3-year maintenance

costs

divided by 3 plus the cost of 1 year of oil changes); to the base maintenance cost, add

$39 per oil change for each 3,500 miles driven

• Estimated total cost of operation per year

Make sure the formulas you enter can be copied to other cells in the same column as

needed for each model selected. Your formulas should also be flexible enough to easily

accommodate anticipated changes to this data. The form should work without displaying

error messages where there are no models. For example, if the model number is blank,

the formulas should leave the description field blank. Use named ranges to simplify your

formulas.

Create intermediate calculations (formulas) and insert worksheets, as needed, to develop

this form. Format your Estimate Form to make sure that it is easy to read and use.

Highlight the data inputs so the user can easily recognize what is needed to complete

the form. Be sure to test your data to verify that the Estimate Form works for different

combinations of buyer profiles and cars.

(Hint : Some formulas may require several levels of nesting and complex function

arguments. It is strongly recommended that you create additional intermediate calculations

for values you might use over and over again or to obtain values that might help simplify

longer calculations.)

Save and close the CKG Operating workbook.

 

 

Chapter 6

Evaluating the Financial Impact of Loans and Investments 358

 

Conceptual Review

Match the following lettered items with Questions 1–14.

1. Compound Interest E. IRR I. PMT M. ROI
2. CUMIPMT F. NPER J. PPMT N. Simple Interest
3. FV G. NPV K. PV O. SLN
4. IPMT H. Payback Period L. RATE P. Type
5. _____Function to calculate the value at the end of a financial transaction
6. _____Function to calculate the interest percentage per period of a financial transaction
7. _____Function to calculate the value at the beginning of a financial transaction
8. _____Function to calculate the number of compounding periods in a financial

transaction

5. _____Function to calculate periodic payments into or out of a financial transaction
6. _____Use a 0 for this argument to indicate that interest will be paid at the end of each

compounding period

7. _____This type of interest is calculated based on original principal regardless of the

previous interest earned

8. _____This type of interest is calculated based on principal and previous interest

earned

9. _____Function to calculate straight line depreciation based on the initial capital

investment, number of years to be depreciated, and salvage value

10. _____Function to calculate the cumulative interest paid between two periods
11. _____Function to calculate the amount of a periodic payment that is interest in a

given period

12. _____Function to calculate the amount of a specific periodic payment that is principal

in a given period

13. _____Function to determine the value of a variable set of cash flows discounted to its

present value

14. _____Function to determine the rate of return, where the net present value of the

cash flows is 0

15. Assume that you have been left an inheritance and want to save part of it toward

the purchase of a car upon graduation, which is three years from now. Write an

Excel formula

to determine the amount of money you need to invest now to have

$17,000 at the end of the three-year period. Assume that you will place this money

in a CD that pays 2% interest compounded quarterly and that you will be making no

additional

deposits into this account.

16. Write an Excel formula to determine the yearly interest rate being charged by the bank

on a $400,000, 20-year mortgage. You make a monthly mortgage payment of $3,400,

and the value of the loan at the end of 20 years is 0. Interest is compounded monthly.

17. Assume that you are buying a car for $25,500 with a $4,000 down payment, and you

are borrowing the rest from a bank at annual interest compounded monthly.

Your monthly payments are $400. Write an Excel formula to determine the number

of years it will take you to pay off this loan.

18. Consider a $150,000 mortgage at 5% annual interest compounded monthly, to be

paid back over the next 30 years. The loan will have a $5,000 balloon payment due at

the end of the loan. Write an Excel formula to determine the payment that must be

made each month on this loan.

19. Assume that you are investing $3,000 in a savings plan today and will make additional

contributions of $300 per quarter. The plan pays 3% interest per year compounded

quarterly at the beginning of each period. Write an Excel formula to determine how

much your savings will be worth in five years.

20. Write an Excel formula to determine the amount of money that can be depreciated

each year, using straight line depreciation, for a new packaging machine purchased by

your company. The machine originally cost $150,000 and has a useful life of 5 years

and an estimated salvage value of $5,000.

Case Problems

Level 1 – Evaluating Loan Options for Flowers By Diana

Diana Bullard currently rents space for her small florist business. As her business continues to

grow, Diana has decided to purchase her own building. She has selected a site and now requires

financing. After meeting with several banks to discuss financing options for a mortgage, Diana

has the data she needs to analyze her options. She lists the purchase price of the building and

the different values for each of the loan variables together with the other data inputs in an

Excel workbook named Her analysis must also take into account the following:

• Down payment—The amount of money Diana will pay at the time she purchases the

building. Provided is the percent of the building purchase price that will be required

for a down payment on each corresponding loan. The difference between the sale price

and the down payment is the loan value—the face value of the loan.

• Points—The additional charges banks sometimes require when lending a mortgage.

Banks usually offer mortgage loans in a variety of interest rate and point combinations.

Frequently, loans with higher points have lower interest rates. One point equals 1% of

the loan value, so one point on a $7,500 loan is $75.

• Fees—The additional amounts banks sometimes charge when lending a mortgage.

These amounts vary by bank and loan type. Typical charges include application fees,

appraisal fees, credit report fees, and so on.

Your task is to complete the Loan worksheet for Diana, using cell references whenever

possible. Write the formulas in cells G8 through K8 so that they can be copied down the

column to calculate values for each option. Write the formulas so they will automatically

update if the mortgage value changes. Loan options 1–7 are all compounded monthly.

Complete the following:

1. Open the workbook named ;in the Chapter 6 folder, and then save the file

as Loan

2. In the Loan Value column, calculate the face value of this mortgage. The purchase

price of the building is in cell E3.

3. In the Monthly Payment column, calculate the monthly mortgage payment for this loan

amount based on the loan value you just calculated. Use the corresponding loan duration,

and nominal interest rate indicated. Assume that the loan is completely

paid off at

the end of this duration. The number of compounding periods per year is in cell E4.

4. In the Actual Amount Borrowed column, calculate the actual amount Diana will

borrow

by subtracting the points and fees from the loan value.

5. To take these fees into account, the lender is required by law to disclose the

APR (the annual percentage rate of interest) of the loan being charged. However,

banks can calculate APR in different ways, including or excluding different fees.

To calculate an actual annual interest rate being charged on this loan (APR), use

the actual amount borrowed (Step 4) as the present value of the loan, the monthly

payment

(Step 3), and the corresponding loan duration.

6. In the Payment with Balloon column, use the nominal interest rate and loan value

(from column G) to determine the monthly loan payment if you altered the loan to

include a $20,000 balloon payment at the end of the loan.

7. The building seller has also offered Diana a private loan for 85% of the value of

the building. In return, Diana must pay $4,500 per month for the next 10 years.

Determine the annual interest rate being charged (cell E17). Inputs do not have to

be explicitly listed elsewhere.

8. Diana is negotiating with the seller and is willing to pay $10,000 per quarter at 6½%

interest per year compounded quarterly. She will borrow everything but a 10% down

payment. Determine how many years it will take to pay off the loan (cell E18). Inputs

do not have to be explicitly listed elsewhere.

9. Ten years ago, Diana invested $75,000 in a bank CD. The CD has earned

annual interest compounded yearly. Determine (TRUE/FALSE) if Diana has sufficient

funds from this CD for the down payment for Option #1 (cell E19).

10. Diana has decided that she prefers a bank loan and, given cash flow issues, wants

the loan with the smallest payment. Highlight in light blue the cell in column H

containing

the payment of the loan Diana should select.

Optional Challenge: Using Conditional Formatting, highlight the cell containing the

minimum payment value, so that if any of the values on any of the bank loans are later

modified, the correct value will be automatically highlighted.

11. Save and close the Loan workbook.

Level 2 – Creating a Mortgage Calculator for TriState Savings & Loan

You have been working as a loan officer at TriState Savings & Loan for over six months. Most

of the work you do involves dealing with mortgages for home buyers and small business

owners. Frequently, prospective buyers come in seeking information about payments

for a particular size mortgage and/or the maximum size mortgage they can obtain for a

particular payment. They also frequently require information on the tax implications of their

selected mortgages, including cumulative yearly interest and depreciation. The answers to

these questions vary based on the interest rates currently being offered and the terms the

potential buyer is seeking, such as loan duration, balloon payments, and so on.

Although you have found some excellent Web sites that perform the necessary calculations,

relying on the Web is sometimes problematic. You can just as easily construct this type of

mortgage calculator in Excel, which is what you will do in these steps.

Complete the following:

1. Create a new workbook and save it as Mortgage ;in the Chapter 6

folder. Rename Sheet1 as Calculator and include the following elements:

• First, construct a small mortgage calculator in which you can fill in the data inputs for

the value of the mortgage, the loan duration in years, the number of payment periods

per year, and the annual interest rate. Assume that at the end of the loan duration, no

balance will be owed. Then, using this data, calculate the payment for the mortgage.

Assume the payment is rounded to the nearest cent. Format the worksheet so the calculator

is easy to read and use with data inputs and outputs clearly defined (labeled).

• Below the mortgage calculator on the same worksheet, create an amortization

table for the loan, organized as follows:

Period Number Remaining Principal Interest Payment Principal Payment

Make sure the table can accommodate a maximum mortgage duration of 30 years,

assuming monthly payments. The remaining principal should start out by referencing

the calculator’s principal value, and thereafter reflect the previous remaining principal

value and principal payment. Write the interest and principal payment formulas so that

if any of the calculator elements change, these amounts will be automatically updated.

Write them so they can be copied down the column for each corresponding period.

Optional Challenge: To avoid #NUM! errors in periods past the end of the loan, nest

your principal and interest payment formulas inside an IF statement to return a 0 if

no further interest or principal payments are required.

2. To test the calculator, use the following customer inputs: determine the monthly payment

for Zach Jones, who wants a 25-year $300,000 mortgage. The current annual

interest rate is compounded monthly. The loan is completely paid off at the

end of 25 years. Assume no additional points or fees.

3. On a separate worksheet named Tax, create a table listing years 1–30 and calculate

the following:

• Cumulative interest payments for each year. Write a formula that automatically

calculates this value for the corresponding periods so that it can be copied down

for each year. Assume that the loans all begin in January so that no “partial” years

need to be calculated. Note that to accommodate variable periods (months, quarters,

and so on), the beginning and ending periods must be formulas that reference

the number of periods per year on your mortgage calculator. (Hint: To automatically

determine the starting period, multiply the year number by the number of

periods per year, and then subtract one less than the number of periods per year.)

• In three adjacent columns, calculate the value of the expected tax deduction for

tax rates of 15%, 28%, and 35% for the corresponding year (interest payments * tax

rate). Your formula should copy both down the column and across the row. Enter

the tax rate in a row above the corresponding column.

• For sample data, use the values from the loan for Zach Jones.
• Optional Challenge: Automatically substitute zeros instead of #NUM! errors in

periods past the end of the loan.

4. In some cases, small business owners who want to buy the properties for their business

endeavors are applying for mortgages. For these customers, it would also be

helpful to provide them with depreciation estimates. Create a separate worksheet

named Depreciation to calculate the depreciation. Include the following:

• At the top of the table, list the inputs that will be required: asset value (which will

differ from mortgage to mortgage, so it needs to be entered directly), salvage

value, and asset life (which will differ from the loan duration).

• Just below the input area, calculate the yearly depreciable value using straight line

depreciation.

• Next, create a table below the straight line depreciation to calculate the depreciation

for each year (1–20) based on the double-declining balance (DDB) method. For more

details on how to use the DDB function, refer to Excel Help. Assume the default factor

will be used and, therefore, can be omitted. Your table should include the year and

the depreciable amount, using Year and DDB as headings to identify the values.

• Enter the following test data: asset value of $200,000 with a 10-year life and a

salvage value of $10,000.

5. Format the workbook so it is easy to read. Save the changes to your Mortgage

workbook.

6. Use the Save As option to create a copy of the workbook named Mortgage

Then complete the following:

• Modify your inputs and formulas on the Calculator worksheet so that you can

enter a known monthly payment, duration, and interest rate to calculate the associated

mortgage value as output. Double check that all of your other formulas work:

amortization table, taxes, and depreciation.

• Use the following for your test data: Kelly Hamilton wants to buy a building she

plans to use as rental property. If she can make monthly payments of $1,250 per

month for the next 30 years, how large a mortgage can she take, assuming that the

current interest rate on a 30-year mortgage is 5% per year compounded monthly?

For depreciation, assume an asset value of 110% of the loan value, a salvage value

of $15,000, and a depreciable life of 20 years.

7. Save and close both the Mortgage workbook and the Mortgage

workbook.

Level 3 – Analyzing Purchasing vs. Leasing Options for CKG Auto

CKG Auto compact car manufacturing assembly plants rely on parts from multiple outside

vendors and internal subassembly plants. Currently, these parts are all transported via

independent trucking firms for negotiated fees based on actual tons shipped and miles. The

operations management group has been dissatisfied lately with the service levels provided

by these outside trucking companies, as well as with the rising costs of roughly

per year for the last two years. These costs are expected to rise in the foreseeable future

at similar rates, according to industry analysts. The operations management group is

beginning a study to determine if purchasing or leasing a fleet of trucks would be a more

cost-effective solution over the next seven years. To do so, the group has compiled some

of the costs for each transport option, as follows:

(1) Trucking by others—Using several different trucking carriers, the CKG Auto compact

car manufacturing group currently pays $12,000,000 annually in trucking fees.

Again, these costs are expected to rise at an annual rate of 6%. So in year 1, the cost is

expected to be $12 million plus an additional 6%. All costs are considered expenses,

which can be used to reduce income for purposes of calculating taxes.

(2) Buying trucks—If CKG Auto purchased a fleet of 20 trucks, the cost of such a

purchase

would be based on the following:

• The model of truck being considered with trailers is estimated to cost $125,000

per truck. This amount will be spent in year 0 (now).

• This purchase would be funded using a bank loan. The bank is willing to lend the

money at a annual interest rate compounded quarterly over the next four years.

A 10% down payment will be required, which can be funded from current assets.

• The operations management group has been directed to assume that if CKG Auto

purchases this fleet, it would be depreciated using straight line depreciation over the

full seven-year period, assuming a salvage value of 8% of the original purchase price.

• Operating costs for year 1 are estimated at $ per mile; this includes driver

wages, gas, insurance, maintenance, fees, and licenses. It is also assumed that each

truck will average 150,000 miles per year. For year 2 and all subsequent years,

assume a cost increase of per year above the previous year.

• For the calculation of taxes, CKG Auto can deduct from each year’s income the following:

operating costs, the interest portion of the loan payments, and depreciation.

(3) Leasing trucks—If CKG Auto leases a fleet of 20 trucks, the cost of such a lease

would be based on the following:

• There will be an upfront signing fee of $10,000 per truck due at signing (year 0).

These fees will be paid directly out of cash assets, and no additional financing will

be required. These fees can be used to reduce income in year 0 for tax purposes.

• Each year, the lease cost will be a flat fee of $35,000 per truck for each of

the next seven years. This fee is fixed for the duration of the lease based on a

150,000-per-

mile limit per year per truck.

• Operating costs for year 1 are estimated at $ per mile; this includes driver

wages, gas, insurance, fees, and licenses. Regular maintenance and repairs are the

responsibility of the truck leasing company. It is assumed that each truck will average

150,000 miles per year. For year 2 and all subsequent years, assume a cost

increase of per year above the previous year.

• Because this is an operating lease, there is no depreciation. The entire cost of the

lease is considered an expense and can be used to reduce income for purposes of

calculating taxes.

Your task is to analyze the various options for CKG Auto to determine which is the most

viable. Complete the following:

1. Create a new workbook and save it as ;in the Chapter 6 folder.

Begin by setting up three separate worksheets, one for each option, with appropriate

sheet names and titles.

2. For each option, calculate the net costs after taxes for each year, starting with year 0

through year 7, as follows:

• For year 0, list any capital expenditures (purchase option) and/or upfront fees

(lease option).

• For years 1–7, itemize the relevant costs for calculating taxable deductible

expenses, including any operating expenses, leasing or trucking fees paid in that

year, any associated

depreciation, and interest (purchase option). Remember that

the $12 million

for trucking costs will go up by 6% in year 1 for the first option,

and operating expenses for the purchase and lease options will increase by

each year, with year 1 operating expenses for purchase option at $ and

for lease option at $ per mile assuming 150,000 miles per year per vehicle.

• Calculate the total tax deductible costs for each year and each option.
• Based on the taxable costs, calculate the resulting tax savings for each year and

each option. Tax savings can be calculated by multiplying all tax deductible costs

by the marginal tax rate of 15%.

• Subtract this tax savings from the tax deductible costs to arrive at the net costs after

taxes. For year 0, only consider monies paid toward leases and/or purchases, and

keep in mind that these purchases are not tax deductible.

3. Insert a fourth worksheet named Comparison, and include the following on this

worksheet:

• List the net cost after taxes for each year for each option in three sequential rows,

referencing the original worksheets so that any subsequent changes will be automatically

reflected on this sheet.

• Using the net costs after taxes, calculate the cost savings between using the current

trucking method (by others) and purchasing a fleet; and then in the next row, the

cost savings between the current trucking method and leasing. Your Comparison

worksheet should have a format similar to the one in Table

Table : Comparison worksheet

Net Costs after

Taxes:

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Trucking by others

Purchasing

Leasing

Cost Savings

Comparisons

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7

Trucking by others vs.

Purchasing

Trucking by others vs.

Leasing

© 2014 Cengage Learning

• Regardless of which signs you’ve used in your analysis so far, express cost savings as

a positive number. (For example, if the costs for shipping by others for year 1 are

$10,000 after taxes, and the costs for shipping with purchased trucks in year 1 are

$6,000 after taxes, express the cost savings as a positive $4,000.)

4. Using NPV, determine the discounted value of the cost savings (if any) between

trucking by others versus purchasing trucks for years 0 through 7, for hurdle rates

between 8% and 18% (at 1% intervals). In a similar way, determine the net present

value of the cost savings between trucking by others versus leasing trucks.

5. Calculate the internal rate of return for the cost savings amounts (trucking by others

versus purchasing, and trucking by others versus leasing).

6. Calculate the return on investment and payback period on the investment versus the

cost savings of the purchasing option.

7. Make a recommendation of which method to use for trucking (by others, purchasing,

or leasing). Highlight in yellow the row containing the net cost savings of the option

you recommend. In a separate area on the worksheet, highlighted in yellow, explain

the reason for your choice.

8. Save and close the workbook.

 

 

Chapter 7

 Organizing Data for Effective Analysis 426

 

Conceptual Review

1. List and describe the steps you would take to create a structured list of data from a

text file that contains values stored on separate lines.

2. Why should you remove unnecessary spaces from data imported from another source?

How do you remove unnecessary spaces from a text string?

3. How do the FIND and SEARCH functions work, and how are they different?
4. What options are available for parsing data when you use the Convert Text to

Columns Wizard?

5. What are the advantages and disadvantages of using the Subtotal tool to analyze data?
6. List and describe the eight available options when using the AutoFilter feature in an

Excel table.

7. What is the primary advantage of storing data in a database and importing that data

into Excel?

8. Explain the steps you must take to import data stored in an Access database into Excel.
9. What is the Query Wizard, and when would you use it?
10. How does Excel store date and time values?
11. What are the arguments for the YEARFRAC function? What are the possible values

for calculating months and years using this function?

12. When should you use a PivotTable report to analyze data?
13. List and describe the four areas of a PivotTable report.
14. How is a PivotChart different from a normal chart in Excel?
15. List and describe the differences and similarities between HTML and XML.
16. Describe the steps you would take to import XML data as an XML table in Excel.
17. How do you import an XML map into a workbook and map its elements into the

worksheet?

Case Problems

Level 1 – Importing and Analyzing Data for Johnson Equipment

Johnson Equipment, the medium-sized laboratory equipment manufacturing company

where you work, is in the process of acquiring Sloan Manufacturing, a smaller equipment

manufacturer in the same industry. Because operations between your company and Sloan

overlap, you need to merge the data from the new company with similar data for your

company. The text file you received from Sloan contains categories, product numbers,

and product descriptions, in addition to the on-hand quantity for each product and the

number of products produced during each month of the past year. You need to add

prefixes to the category names so the data will match the existing data that your company

uses. Then, you will use Excel to organize and summarize the data.

Complete the following:

1. Use Notepad (or another text editor) to open the text file named ;from the

Chapter 7 folder to examine the data, and then close the file and Notepad.

2. Import the text from the Sloan text file into a new workbook. Name the workbook

;and save it in the Chapter 7 folder. Rename Sheet1 as

Imported Data.

3. Convert the information in the worksheet into columns, if necessary.
4. Sort the data by category and then by product number in ascending order.
5. Use the CONCATENATE function to add the appropriate prefix and a dash to each

category in a new column titled CategoryPrefix using the following list. For example,

the Analyzer category would begin with 600, followed by a dash and the category

name (600-Analyzer). (Hint: Try placing these values in your spreadsheet

in a column

named Prefix using a VLOOKUP function.) Delete the original Category column.

Category Prefix Category Prefix

Analyzer 600 Evaporators 619

Autoclave 601 Fermentors 621

Balances 603 Furnace 623

Bath 605 Gas Chromatographs 625

Biohood 607 Glove Boxes 627

Cell Disrupters 609 Microscopes 629

Cell Harvesters 611 Reactors 631

Centrifuges 613 Spectrophotometers 633

Chromatography 615 Ultrasonic Cleaners 635

Desiccators 617

6. Change the data set into an Excel table.
7. Use the Total row to calculate the number of units on hand and the number of units

produced in each month. Add a new column named Total to the right of the December

column that calculates the number of units produced for the year for each part.

8. Display the top 20 items based on the values in the Total column to show the parts

with the highest production by month.

9. Save and close the workbook.

Level 2 – Analyzing Manager Performance at Home Station

You are a regional manager for Home Station, a national chain of home renovation stores.

You are analyzing the weekly sales data for one of the retail stores located in Austin, Texas.

The sales data is reported by department and manager. The Austin store manager wants

to rotate the department managers in each of the store’s departments so each manager

becomes more familiar with the entire store’s operations. You have been assigned the task

of determining the impact of rotating the managers on store sales. You will import the

sales data from a database into Excel and then create a PivotTable report to summarize

the quarterly sales by department and by manager.

Complete the following:

1. Create a new Excel workbook and save it as Home ;in the

Chapter 7 folder.

2. Import the information from the Sales table in the ;database in the

Chapter 7 folder into a worksheet named Sales.

3. Create a PivotTable report using the data in the Sales worksheet and place the

PivotTable report in a new worksheet named Austin.

4. Use the PivotTable report to analyze the sales by department. Change the number

format of the sales data to currency and sort the sales so that the department with the

highest sales appears first. Print the worksheet.

5. Rearrange the fields in the PivotTable report to analyze department sales by quarter.

Which department had the highest quarterly sales, and in which quarter did it occur?

(Hint: Use the Date field to summarize the dates by quarter.)

6. Add the Manager field to the PivotTable report to analyze each department’s

quarterly

sales performance by manager. Which manager had the highest sales for

each department? In which quarter did the manager’s highest sales occur?

7. Rearrange the fields in the PivotTable report to analyze each manager’s quarterly

sales performance by department. Which department resulted in the highest sales for

each manager? In which quarter did the highest sales occur?

8. Based on the data collected, which manager would you choose to manage each

department on a long-term basis? Support your recommendations with data from the

PivotTable report.

9. Save and close the Home workbook.

Level 3 – Creating a Loan Application and Amortization Schedule

for CKG Auto

CKG Auto’s Financial Department has asked you to finish developing an Excel workbook

that it plans to use to analyze automotive credit application data stored in individual

XML documents. The workbook is based on an existing credit analysis spreadsheet.

The workbook will be used to calculate loan payment information and create a loan

amortization schedule for customers purchasing new and used vehicles from dealerships

that provide financing options through CKG Auto. The file contains sample

data that you will import into the spreadsheet to test the workbook.

Complete the following:

1. Open the ;workbook from the Chapter 7 folder, and then save the file as

Automotive Credit

2. Use the XML Source task pane to create an XML map using the ;file in

the Chapter 7 folder.

3. Map the elements in the XML Source task pane to the appropriate cells in the

worksheet

by dragging them from the XML Source task pane and dropping them

into the appropriate cells in the worksheet.

4. Use the DEVELOPER tab to map and import the appropriate elements from the

document to the blank cells for the Application Information, Loan Details,

and Vehicle Information sections in the Automotive Credit Application worksheet.

5. In cell B13, write a formula to calculate the monthly car payment.
6. Prepare an amortization schedule based on the information imported from the XML

document.

7. Save and close the Automotive Credit workbook.

 

 

Chapter 8

Using Data Tables and Excel Scenarios for What-If Analysis 490

 

Conceptual Review

1. Compare break-even analysis and sensitivity analysis.
2. How does a data table help you perform what-if analysis?
3. What is the difference between a one-variable data table and a two-variable data table?

When would you use each type of data table?

4. What are the two major steps involved in creating a data table in Excel?
5. In a two-variable data table, what do the first column and first row contain?
6. When should you create a scenario instead of a data table? Give an example of a

business

situation that could best be analyzed with scenarios.

7. Why are defined names important when you create scenarios?
8. What types of reports can you create for scenarios? Which type shows results only?
9. What type of what-if question can a simulation answer?
10. Explain how you must vary the setup of a two-variable data table to run a simulation

via the table.

Case Problems

Level 1 – Estimating Travel Expenses for Customers of Executive

Transport, Inc.

Executive Transport, Inc. is a company that rents cars, vans, and limos to a wide range

of customers. Customers frequently call Executive Transport’s sales staff to receive

quotes for rental expenses. As gas prices have been increasing the last few years,

customers have become more conscious of fuel efficiency and its impact on their total

cost of transportation. As a service to its customers, Executive Transport has modified

its Quotes workbook to include information on its cars’ gas mileage and an estimate

of the gas expense related to customers’ planned trips. All that remains to be added

is information specific to each of the car types, and a more detailed analysis of the

interaction between gas prices and total cost of transportation, and the gas mileage of

cars and its impact on the total cost of transportation. Complete the following:

1. Open the workbook named ;located in the Chapter 8 folder, and save

the file as Transport

2. Examine the Analysis worksheet, and apply appropriate names to cells D17:D20.
3. Set up the structure of a one-variable data table on the Analysis worksheet that shows

the car charge, mileage charge, gas expense, and total transportation expense given

changes in the average price of gas from $ to $ by increments of $

4. Instruct Excel to complete the one-variable data table.

TROUBLESHOOTING: In order to complete the one-variable data table successfully,

be sure to select the correct data table range. If you instruct Excel to complete an

incorrect

range, you cannot just delete any “extra” results that might have appeared at

the bottom or right of the intended data table area. When you try to do so, you will

receive the “Cannot change part of a data table” error message. You cannot delete only

some results from a filled-in data table; you must delete all results values from a onevariable

data table if you want to delete any results.

5. Add headings and basic formatting to the data table so it has a more professional

appearance.

6. A few rows below the one-variable data table, create the structure for a two-variable

data table that shows the total projected transportation expense given the interaction

between average gas prices from $ to $ by increments of $ and

miles per gallon from 18 to 28 by increments of 2 miles per gallon.

7. Instruct Excel to complete the two-variable data table.
8. Add headings and some basic formatting to the data table so it has a more professional

appearance.

9. Save and close the Transport workbook.

Level 2 – Evaluating Expansion Financing Options for Granite

City Books

Granite City Books is planning a $ million expansion of its facilities. It needs to evaluate

its options for financing the expansion. The company’s bank might not allow it to obtain

more long-term debt financing if its debt-to-equity ratio gets too high. Alternatively,

common stockholders might be displeased if their ownership rights become diluted by

issuing a substantial amount of preferred stock, or even additional common stock if it’s

not issued on a pro rata basis. A workbook has been started that contains a basic Balance

Sheet, solvency, and capital structure ratio data. Your task is to create scenarios for each

financing alternative and prepare reports that Granite City Books’ bank and management

can use to compare the various financing alternatives. Complete the following:

1. Open the workbook named ;located in the Chapter 8 folder, and save

the file as Granite City ;in the same location.

2. In the Options worksheet, apply appropriate names to the worksheet’s scenario

changing cells as described in Table

Table : Descriptions of the changing cells

Changing Cell Description

F9 Change in assets

F12 Change in long-term debt

F14 Change in the dollar amount of common stock issued

F15 Change in the dollar amount of preferred stock issued

© 2014 Cengage Learning

3. Create four scenarios in the Options worksheet using the scenario names and

changing

cell values shown in Table

Table : Data for the four scenarios

Changing Cell Long-Term Debt

Financing

Common Stock

Financing

Preferred Stock

Financing

Balanced

Financing

F9 2,500 2,500 2,500 2,500

F12 2,500 0 0 1,000

F14 0 2,500 0 750

F15 0 0 2,500 750

© 2014 Cengage Learning

4. Based on the information in Table , apply appropriate names to the worksheet’s

result cells.

Table : Descriptions of the result cells

Cell Description

F13 Change in total liabilities

F17 Change in total equity

G21 Debt-to-equity ratio

G24 Long-term-debt-to-common-equity ratio

G25 Preferred stock ratio

© 2014 Cengage Learning

5. Create a professional-looking scenario summary report that shows all the result cells

listed in Table

6. Create a professional-looking scenario PivotTable that shows the last three result cells

listed in Table Also generate a PivotChart based on the PivotTable.

7. Save and close the Granite City workbook.

Level 3 – Analyzing Health Insurance Plan Options for CKG Auto

CKG Auto has traditionally paid 75% of the premiums for its employees’ health insurance.

Like many companies, CKG is re-evaluating this employee benefit as health insurance

premiums have increased at a rate much higher than the general rate of inflation. CKG

is evaluating all of its options with regard to health insurance. The company has received

quotes from insurance companies for three different health plans: a co-pay plan, a lowdeductible

plan, and a high-deductible plan. In addition, it is considering instituting a

self-insurance health plan, under which the employees pay a quasi-premium and co-pay

directly to CKG, and in turn, CKG pays the employees’ health claims, just as an insurance

company would.

CKG’s human resources staff has started a workbook that contains data about the cash

effects of the options for purchasing health insurance plans and the proposed self-insurance

plan based on CKG’s employees’ past health insurance participation and claim history.

You have been asked to finish the worksheet, complete with scenarios under which CKG

pays different percentages of employees’ health insurance premiums, and a simulation

estimating the cash effects of the self-insurance plan. Complete the following:

1. Open the workbook named ;located in the Chapter 8 folder, and

save the file as CKG Health

2. In the Self-InsuranceSim worksheet, complete two cumulative probability distribution

tables: one for the co-pay events and one for the dollar value of claims.

3. Add a randomly generated value somewhere in the worksheet.
4. In cells D12 and D13, use the appropriate functions to return a number of co-pay

events and a dollar value of claims based on a random entry from the cumulative

distribution

tables.

5. Set up a data table that will run a simulation to calculate the net cash paid for employee

health coverage given various numbers of co-pay events, dollar value of claims, and

monthly premiums paid by employees of $50 to $130 by increments of $10. CKG

Auto’s management has decided that 1000 iterations of the simulation are sufficient.

6. Instruct Excel to complete the data table. Add informative headings and formatting

so the table has a more professional appearance.

7. Use the AVERAGE, , MAX, and MIN functions to create a range that

summarizes

the simulation’s net cash effects for each monthly premium

value.

8. Add informative headings and formatting to the summary range so it has a more

professional

appearance.

9. Save and close the CKG Health workbook.

 

Chapter 9

Enhancing Decision Making with Solver 552

 

 

Conceptual Review

1. What are the limitations of using Goal Seek?
2. How many variable cells can you use in a Solver model?
3. What are the three required parameters of a Solver model, and what do they represent?
4. What are two advantages of creating a constraints table in a worksheet that includes a

Solver model?

5. What is the advantage of linking the constraints in the Solver Parameters dialog box

to values in a constraints table in the worksheet?

6. What is the difference between a policy constraint and a physical constraint? Give one

example of each type of constraint.

7. What is an objective function?
8. What are the five comparison operators that you can use in Solver?
9. When should you include integer constraints in a Solver model? What is the disadvantage

of using an integer constraint?

10. What is a scenario in Solver?
11. What are the types of Solver reports? What information is described in an answer

report? What is the difference between a binding status and a not binding status?

What is slack?

12. What is an infeasible solution? What steps can you take to attempt to change an

infeasible

solution into a feasible solution?

13. What is an unbounded solution?
14. What is the difference between a linear function and a nonlinear function?
15. What requirements exist for cells in arrays when you use the SUMPRODUCT

function?

16. Describe how the SUMPRODUCT function works.
17. Describe the steps for saving a Solver model. What is the advantage of saving a Solver

model?

18. What is an assignment problem?
19. How does the binary comparison operator work?

Case Problems

Level 1 – Creating a Production Plan for ATC Inc.

ATC Inc. is a manufacturing company that produces industrial valves. As the operations

manager, you need to determine the production schedule for manufacturing three valves

in the new 102 Series, which is used in oil refineries. Producing the 102 Series valves

is a three-part process. During the casting phase, the body of the valve and some of

the attaching pieces are created. During the machining phase, the casting surfaces are

finished, and the inner valve flange is created. Finally, during the last phase, the valves

are assembled and inspected.

The plant manager provided you with a workbook that contains the production times

for each production phase, and the unit cost and list price for each 102 Series valve.

The plant manager also told you that your time is limited to 500 hours in the Casting

Department, 500 hours in the Machining Department, and 100 hours in the Final

Assembly Department. No more time is available in these departments. The workbook

also includes the setup costs associated with a production run for each valve. You must

account for these costs in the profit amounts for each valve.

Based on historical demand for these valves, the company has a policy that any single

valve in the 102 Series should constitute at least 10% of the production run and that no

single valve should constitute more than 50% of the total production time. Your goal is to

maximize the total profit of producing these valves while not exceeding the time available

in each department.

Complete the following:

1. Open the ;workbook from the Chapter 9 folder, and then save the file

as ATCValve

2. Write the formulas that calculate the total production time used for casting, machining,

and final assembly. Enter a mathematical formula in the objective cell, and then

enter formulas to link the objective cell with the variable cells. Add sample values to

the variable cells to ensure that your worksheet is set up correctly and produces the

expected results.

3. Create a constraints table in the worksheet that identifies the constraints in this

problem.

4. Use Solver to determine the optimal combination of valves to build to maximize

profit.

5. Produce an answer report of your solution. Which constraints could you modify to

maximize the total profit? Place your recommendations in a text box on the answer

report worksheet.

6. Save your solution as Scenario 1.
7. Modify the constraints in your constraints table and in Solver that you determined

would further improve the solution, and then run Solver again. Save this scenario as

Scenario 2. Add a comment to the scenario describing the constraints you modified

and why you chose to do so.

8. Produce an answer report of your revised solution. Did your solution accomplish the

goals you identified in Step 5? What other changes can you recommend to maximize

the total profit? Place your recommendations in a text box on the answer report

worksheet.

9. Save and close the ATCValve workbook.

Level 2 – Managing Purchases for Brightstar Toy Company

Brightstar Toy Company, a national toy store, is planning a huge promotion for Power

Blocks action figures during the upcoming holiday season. As the company’s purchasing

manager, your job is to determine the best way to purchase these toys from the

manufacturer at the lowest price.

After contacting the Power Blocks manufacturer, you learned that volume discounts

and discounts for preseason orders are available on the Power Blocks product line. You

need to determine the best product mix based on the following information. The Power

Blocks Urban Adventure set is the first item that most people will buy. Customers can

also purchase the optional Turbo Action kit, which can be attached to the Power Blocks

Urban Adventure set. Another companion set, the Outdoor set, has an optional Camping

kit that customers can purchase separately.

The marketing director for Brightstar, Betty Wright, wants to allocate the purchasing

budget of $100,000 among the four toys. Because the Turbo Action kit and the Camping

kit are accessories, Betty wants to buy at least half as many kits as sets, but the total

number of kits cannot exceed the number of sets. Betty also wants to purchase at least

5,000 units of each set.

Because you are purchasing the Power Blocks toys early, you must consider the cost of

storing the toys in the Brightstar warehouse until the holiday sales season begins. Your

workbook includes the retail price for each toy, the unit cost from Power Blocks, and the

storage cost for storing the toys until you can send them to retailers. Your objective is to

maximize the profit on all four toys.

1. Open the ;workbook from the Chapter 9 folder, and then save the file as

Power

2. Write the formulas that calculate the gross revenue (number purchased multiplied

by

the retail price), the total unit cost (unit cost multiplied by the number purchased),

the

total storage cost (storage cost per unit multiplied by the number

purchased),

the

total cost (total unit cost plus total storage cost), and the profit (gross revenue minus

the total cost).

3. Enter a mathematical formula in the objective cell, and then check that formulas

entered in Step 2 link the objective cell with the variable cells. Add sample values to

the variable cells to ensure that your worksheet is set up correctly and produces the

expected results.

4. Create a constraints table in the worksheet that identifies the constraints in this

problem.

5. Use Solver to determine the best way to purchase the Power Blocks sets and kits, and

maximize the profit.

6. Produce a Feasibility Report to troubleshoot the solution to determine how the cost

and quantity constraints caused the solution to be infeasible.

TROUBLESHOOTING: In order to complete this step to produce a Feasibility

Report, you must run the Solver model again without the integer constraints.

7. Save your Solver model as a scenario named Purchase Plan 1.
8. Betty tells you that she cannot increase the available budget and asks you to change

the constraints to maximize the profit. She also asks you to keep the constraint

to

purchase

more sets than kits and to purchase at least half as many Power Block

Outdoor sets as Power Blocks Urban Adventure sets. She also wants you to purchase

at least 7,250 total items.

Update your constraints table and the Solver model with your changes, and then run

Solver again. If necessary, adjust your constraints until you find a feasible solution.

Save your Solver model as a scenario named Purchase Plan 2.

9. Produce an answer report of your final solution. Use the answer report to

recommend

any additional changes that might maximize the total profit. Place your

recommendations

in a text box on the answer report worksheet.

10. Save and close the Power workbook.

Level 3 – Assigning Specialists to Teams at CKG Auto

In the CKG Auto Racing Division, your group builds specialized versions of CKG’s Puma

sports car for the amateur racing circuit. The operations manager for the racing division,

Bob Mather, wants you to identify the most effective way to assign automotive specialists

to the three major assembly areas for the Puma: engine and transmission, suspension,

and body and frame. Each assembly area requires a two-person team, and there are six

people whom Bob can use on the three teams. After evaluating each team member, Bob

assigned a skill level for each of the three assembly areas. The skill levels normally range

from 1 to 100 and represent qualifications, training, and experience. Because the cars in

the racing division are high-performance vehicles, the skill of the team members is very

high, with no team member scoring less than 71 in any area. Bob asks you to assign two

team members each to the three assembly areas so that each team member’s skills are

maximized. The skill levels are maximized by computing the sum of the skill levels of the

individual team members in each area.

Complete the following:

1. Open the ;workbook from the Chapter 9 folder, and then save the file as

Puma

2. Write the formulas that calculate the number of people assigned to an assembly area

and the number of assignments for each person. Complete the worksheet to allow

Solver to select the top two team members for each team based on the skill levels of

the team member.

3. Enter a mathematical formula in the objective cell, and then enter formulas to link

the objective cell with the variable cells. Add sample values to the variable cells to

ensure that your worksheet is set up correctly and produces the expected results.

4. Create a constraints table in the worksheet that identifies the constraints in this

problem.

5. Use Solver to determine the best way to assign team members to the three assembly

area teams while maximizing the skill levels of each team member and team. When

you find a feasible solution, create an answer report.

6. Bob wants to see if adding another of his top team members to the mix will increase

the skill level of any of the teams in the racing division. Save your Solver model in

a blank area of the worksheet, and then create a copy of the Assembly Area Teams

worksheet and name it Assembly Area Teams — Revised. Add Ned Hall to your

worksheet with the following skill levels: Engine/Transmission: 97, Suspension: 94,

and Body/Frame: 92.

7. Use Solver to determine whether Ned should replace one of the existing team members;

and if so, identify which team member Ned should replace. When you find a

feasible solution, create an answer report.

8. Save and close the Puma workbook.

 

Chapter 10

Troubleshooting Workbooks and Automating Excel Applications

Conceptual Review

1. What is a decision support system?
2. Data validation can be used to minimize this type of mistake when creating Excel

applications.

3. What is a data validation rule? When would you use the Circle Invalid Data feature?
4. What is the difference between an input message and an error alert in the context of

Excel data validation?

5. What style of error alert prevents a user from storing an invalid entry in a worksheet?
6. Explain the two basic steps you must perform to protect the contents of a worksheet.
7. What feature can be used to prevent users from inserting, deleting, or renaming

worksheets in a workbook?

8. Describe two ways to document information in a workbook.
9. Explain the difference between precedent and dependent cells.
10. What are false positives and false negatives, and which are harder to detect in worksheets?
11. What is a macro? How could you use one in an Excel workbook?
12. Identify three ways to run a macro in Excel.
13. What is the difference between saving a macro to the current workbook versus a

personal

macro workbook?

14. What is VBA?
15. What is the first line of VBA code in a macro?
16. What is the major difference between an Excel workbook with an .xlsx filename

extension and an .xlsm extension?

Case Problems

Level 1 – Troubleshooting Formulas and Data Entry in a Payroll Data

Workbook for Irene’s Scrapbooking World

Similar to other small businesses, Irene’s Scrapbooking World outsources the processing

of its payroll to its accounting firm. Twice per month, Irene Watson, the owner of

Irene’s Scrapbooking World, creates a workbook that contains her employees’ payroll

information to send to the accounting firm of Wipson & Lynn, LLP. Wipson & Lynn

then uses that information to prepare the paychecks for the employees and calculate

Irene’s Scrapbooking World’s payroll liabilities to the government and other entities, and

to prepare its payroll-related reports, such as the Federal 941 forms.

Wipson & Lynn charges Irene based on the number of paychecks it processes for her

company. A number of Irene’s payroll-related workbooks have contained data-entry

errors that the firm’s personnel had to fix before it could process the payroll. Wipson &

Lynn also charges Irene for the time it takes to track down and fix those errors.

Irene has asked you to use the Excel data validation and protection tools to troubleshoot

the current payroll workbook and set up the workbook to help prevent errors from

occurring in the future.

1. Open the workbook named ;located in the Chapter 10 folder, and then

save the file as Scrapbooking

2. On the Documentation worksheet, enter your name and the current date in the

appropriate cells. Review the documentation information.

3. Switch to the Payroll Register worksheet. Set up an appropriate validation rule, input

message, and error alert for the Tax Status data in cells C7:C15.

4. Set up an appropriate validation rule, input message, and error alert for the Allowances

data in cells D7:D15. (Hint: Employees can take 0 or more withholding allowances.)

5. Set up an appropriate validation rule, input message, and error alert for the Insurance

Plan data in cells E7:E15. (Hint: The valid health plan options are stored in cells

L22:L24.)

6. Identify and correct all data that does not meet the validation settings you created in

Steps 3 through 5.

7. Correct any other flagged or unflagged errors.
8. Change the properties of the cells so that users can edit only the hours worked

information

for each employee when the worksheet is protected.

9. Hide the column containing the SSN numbers.
10. Insert a comment in cell D6, stating that “Allowances are the number of dependents

claimed for income tax withholding ;

11. Enable worksheet protection. Do not specify a password.
12. Enable the workbook structure protection.
13. Save and close the Scrapbooking workbook.

Level 2 – Troubleshooting Formulas in a Job Invoicing Workbook for

David’s Computer Repair

As extra income, David Green has been operating a small computer repair business for

a number of years. He has decided to use an Excel workbook to create a template for a

sales invoice. David’s idea is that he will enter the data for each job in a few cells on a

worksheet. This data will be inserted in an invoice in another portion of the worksheet,

which he can then print and send to the customer as a bill.

As a new user of Excel, David has asked for your help in identifying and correcting errors

in the invoice area of his worksheet. In addition, he asks for your help in creating some

basic documentation for the workbook so that others can use it.

Complete the following steps:

1. Open the workbook named ;located in the Chapter 10 folder, and then

save the file as Repair

2. On the Documentation worksheet, insert your name and the current date in the

appropriate cells.

3. On the Job Invoicing worksheet, use the Formula Auditing tools to identify and fix

errors in the formulas of the Customer Invoice section. Evaluate whether all flagged

errors indicate true errors or false positives. Fix the flagged errors that are truly errors.

Also, look for any false negative errors in the invoice’s formulas.

Use the following explanations for the calculations as you troubleshoot the worksheet:

• The Invoice Date should be a formula that displays the current date.
• The total labor charge is computed by multiplying the number of hours spent on

the job by the labor charge per hour.

• Sales tax is computed by multiplying a sales amount by the sales tax rates. Sales

tax is computed separately for labor and parts as they have different sales tax rates.

• The total invoice amount due from the customer is the sum of the total charges for

parts, labor, and the sales tax items.

4. Enter the following job data and view the resulting invoice:

• Job date: 2/17/16
• Customer name: Mason McDonald
• Total hours spent on job: 
• Charge per hour: $55
• Total parts for the job: $
• Sales tax on labor is 5% and Sales tax on Parts is 

5. On the Documentation worksheet, explain the purpose of the workbook, assumptions

in the workbook, and instructions for using the workbook.

6. Display the Job Invoicing worksheet formulas, and adjust column widths so they are

only as wide as necessary. Set the page layout orientation to Landscape. Show Trace

Precedent lines for cells F18 and F14.

7. Print the worksheet to a Microsoft XPS Document Writer file named Job Invoicing.

Be sure to execute the print command directly after displaying the Trace Precedent

lines or they will not be visible.

8. Reset the workbook to display values, reset to portrait orientation, readjust column

widths, and turn off Trace Precedent lines.

9. Save and close the workbook.

Complete the following steps:

1. Open the workbook named ;located in the Chapter 10 folder, and then

save the file as Scrapbooking

2. On the Documentation worksheet, enter your name and the current date in the

appropriate cells. Review the documentation information.

3. Switch to the Payroll Register worksheet. Set up an appropriate validation rule, input

message, and error alert for the Tax Status data in cells C7:C15.

4. Set up an appropriate validation rule, input message, and error alert for the Allowances

data in cells D7:D15. (Hint: Employees can take 0 or more withholding allowances.)

5. Set up an appropriate validation rule, input message, and error alert for the Insurance

Plan data in cells E7:E15. (Hint: The valid health plan options are stored in cells

L22:L24.)

6. Identify and correct all data that does not meet the validation settings you created in

Steps 3 through 5.

7. Correct any other flagged or unflagged errors.
8. Change the properties of the cells so that users can edit only the hours worked

information

for each employee when the worksheet is protected.

9. Hide the column containing the SSN numbers.
10. Insert a comment in cell D6, stating that “Allowances are the number of dependents

claimed for income tax withholding ;

11. Enable worksheet protection. Do not specify a password.
12. Enable the workbook structure protection.
13. Save and close the Scrapbooking workbook.

Level 2 – Troubleshooting Formulas in a Job Invoicing Workbook for

David’s Computer Repair

As extra income, David Green has been operating a small computer repair business for

a number of years. He has decided to use an Excel workbook to create a template for a

sales invoice. David’s idea is that he will enter the data for each job in a few cells on a

worksheet. This data will be inserted in an invoice in another portion of the worksheet,

which he can then print and send to the customer as a bill.

As a new user of Excel, David has asked for your help in identifying and correcting errors

in the invoice area of his worksheet. In addition, he asks for your help in creating some

basic documentation for the workbook so that others can use it.

1. Open the workbook named ;located in the Chapter 10 folder, and then

save the file as Repair

2. On the Documentation worksheet, insert your name and the current date in the

appropriate cells.

3. On the Job Invoicing worksheet, use the Formula Auditing tools to identify and fix

errors in the formulas of the Customer Invoice section. Evaluate whether all flagged

errors indicate true errors or false positives. Fix the flagged errors that are truly errors.

Also, look for any false negative errors in the invoice’s formulas.

Use the following explanations for the calculations as you troubleshoot the worksheet:

• The Invoice Date should be a formula that displays the current date.
• The total labor charge is computed by multiplying the number of hours spent on

the job by the labor charge per hour.

• Sales tax is computed by multiplying a sales amount by the sales tax rates. Sales

tax is computed separately for labor and parts as they have different sales tax rates.

• The total invoice amount due from the customer is the sum of the total charges for

parts, labor, and the sales tax items.

4. Enter the following job data and view the resulting invoice:

• Job date: 2/17/16
• Customer name: Mason McDonald
• Total hours spent on job: 
• Charge per hour: $55
• Total parts for the job: $
• Sales tax on labor is 5% and Sales tax on Parts is 

5. On the Documentation worksheet, explain the purpose of the workbook, assumptions

in the workbook, and instructions for using the workbook.

6. Display the Job Invoicing worksheet formulas, and adjust column widths so they are

only as wide as necessary. Set the page layout orientation to Landscape. Show Trace

Precedent lines for cells F18 and F14.

7. Print the worksheet to a Microsoft XPS Document Writer file named Job Invoicing.

Be sure to execute the print command directly after displaying the Trace Precedent

lines or they will not be visible.

8. Reset the workbook to display values, reset to portrait orientation, readjust column

widths, and turn off Trace Precedent lines.

9. Save and close the workbook.

Level 3 – Projected Sales and Commissions for CKG Auto

You are working in CKG Auto’s Sales Department as an analyst. The sales manager, Leroy

Mason, wants to automate the worksheet he uses to calculate monthly sales commissions

so that others can use it. CKG Auto pays each salesperson a base salary plus a commission.

This commission value has changed so this value for all new entries should be 7% of the

sales amount. Leroy asks for your help in ensuring users enter valid data and do not

change or delete the sales data the workbook already contains. He also wants to include a

chart comparing the total sales of each salesperson without revealing their names.

Complete the following:

1. Open the workbook named ;located in the Chapter 10 folder, and then

save the file as the macro-enabled workbook July

2. Create a new worksheet named SalesRepSummary. On this new worksheet, list

the name of each salesperson and summarize each salesperson’s total sales and total

commissions

based on the data listed on the SalesList worksheet. (Hint: Use a

PivotTable or the Remove Duplicates button in the Data Tools group on the DATA

tab to remove the duplicate names quickly.)

3. Examine the SalesEntryForm worksheet and correct its formula error. Create

validation

rules so sales rep names appear in a drop-down list using the data on the

SalesRepSummary worksheet, sales dates are limited to valid dates in 2016, and sales

amounts must be positive values. Annotate the validation rules so users entering the

data will know what data to enter and what is allowed.

4. Create a macro named SubmitSales with shortcut key Ctrl+Shift+S, assigned to

this workbook. The purpose of the macro is to automate the moving of the data

entered in the sales entry form to the SalesList worksheet and then to clear the data in

the sales entry form. Create a custom button on the SalesEntryForm worksheet with

the label Record Sale that automatically launches this macro. For best results, use the

following procedure:

• Before recording the macro, assign range names to the following cells:

SalesEntryForm!B5 Sales_Rep_Name

SalesEntryForm!B6 Sales_Date

SalesEntryForm!B7 Sales_Amount

SalesEntryForm!B8 Commission

SalesList!A5 top

• Apply the Use Relative References option to make sure that when recording your

macro, the insertion point is relative to the active cell. (Hint: The Use Relative

References button in the Code group on the DEVELOPER tab toggles this

feature

on and off. Be sure the button is highlighted in green to indicate that the

feature is on before you begin recording the macro.)

• Enter some sample data into the sales entry form.
• Start the macro, assigning the appropriate name and macro shortcut key. Enter a

brief description.

• Open the Go To box by pressing the F5 function key, and then select top in the list

of range names to move the starting point on the SalesList worksheet to cell A5.

• Press Ctrl+↓ to move the active cell to the bottom of the list. Then, using the

arrow keys, move the active cell down one row to the first empty cell. Type

= Sales_Rep_Name. Move to the next column where the user will enter the sales

date, and then type =Sales_Date. Repeat this procedure to enter the sales amount

and commission using their corresponding range names.

• Copy the cells with the formulas you just entered, and then use the Paste Special –

Values option to paste the cells in the same location. This past action removes the

formulas, but keeps the values that appear in the data entry form.

• Go to the Sales_Rep_Name range, and delete the entry. Move down one cell, and

delete the sales date and sales amount. You must clear the input data to leave the

cells ready for the next user to enter new data. Press the Esc key.

• Stop recording.
• Create a custom macro button called Record Sale on the SalesEntryForm worksheet

that automatically launches this macro.

• Remove the sample data from the SalesList worksheet that you used when writing

the macro.

5. Create a column chart on a separate worksheet named SalesChart that shows the

sales by sales rep, but do not show the sales rep name. Excel will assign numbers to

each column.

6. Test the workbook formulas, macro, and chart to ensure they all work correctly. Be

sure to try invalid values to be sure data validation is working correctly. Return the

SalesList to its original state when testing is complete. Note the following:

• Does the data on the SalesRepSummary worksheet update correctly?
• Modify the formula ranges to accommodate up to 100 entries.
• Place a comment in cell SalesList!A102 warning that to include this entry you will

need to update formulas on the SalesRepSummary worksheet.

7. Insert a worksheet named Documentation. Enter your name and date as the user

who created the workbook and the one who last updated it. Identify the purpose

of the workbook, explain how the workbook should be used, identify and explain

worksheets,

and identify and explain the workbook macro.

8. Protect the workbook so that users cannot add or delete worksheets. Protect the

SalesEntryForm worksheet, allowing data entry for only the sales rep name, sales

date, and sales amount. Do not use passwords.

9. Add the following sales using the entry form, each time running the macro to copy

the information to the sales list:

Brady Anderson 7/25/2016 $4500

Richard Taylor 7/25/2016 $750

Richard Taylor 7/27/2016 $1825

Tasha Johnson 7/28/2016 $1750

10. Save and close the July workbook.