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

<< Intermediate Accounting 14th edition By Kieso Test Bank Integrated Principles of Zoology 16th Edition Hickman-Keen-Larson-Roberts-Test Bank >>
Product Code: 222
Availability: In Stock
Price: $24.99
Qty:     - OR -   Add to Wish List
Add to Compare

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

Description

COMPLETE TEXT BOOK SOLUTION WITH ANSWERS

 

 

 

 

SAMPLE QUESTIONS

 

 

Succeeding in Business with

Microsoft Excel 2013:

A Problem-Solving Approach

 

With knowledge comes opportunity,

with opportunity comes success.

 

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!
  1. 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.

  1. If you enter =121*10 in a cell exactly as shown, what value would result?
  2. 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
  1. When you write a formula, why should you use cell references rather than typing in

values

directly into the formula?

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

likely reason the value calculated appears to be incorrect.

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

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

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

contain

text.

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

resulting

formula?

  1. 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?

  1. 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)?

  1. 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 1.8 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 1.8: 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; 2.2GHz

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 (2.10GHz) including 8

GB of RAM, 300 GB hard drive, 15.5

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, (2.2 GHz), 4GB

of RAM, 250 GB hard drive,

15.4 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 $79.95 Hard drive upgrade to 600 GB $59.99 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 $249.99 Bluetooth $30

Shipping cost $46 DVD R/W drive $115

Long-life battery $70

3-year warranty $195.30

Carrying case $65

2014 Cengage Learning

Complete the following:

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

the Chapter 1 folder.

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

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

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

the system component costs.

  1. Highlight the cell containing the name of the least expensive computer system in yellow.
  2. 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.

  1. Just below the calculation for Step 6, calculate the cost of purchasing the 40 machines

with this new pricing structure for each option.

  1. Highlight the cell containing the lowest final cost for 40 machines in a shade of light green.
  2. Save and close the Computer Purchase.xlsx 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 companys 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 companys 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 Jobs.xlsx located in the Chapter 1 folder, and then save

the file as Job Analysis.xlsx.

  1. 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).

  1. In cell F6, write a formula to calculate the average adjusted salary of the three positions.
  2. 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.

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

  1. In cell G9, write a formula to determine the number of positions that include a bonus.
  2. 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 2.5% raise after the

first year of employment in the new position.

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

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

position,

a senior consultant position, is located in Washington D.C., 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 1.4. 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 youve calculated

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

formatting

of the existing data.

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

package,

and bold the text in this row.

  1. Save and close the Job Analysis.xlsx 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 models 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 CKG.xlsx located in the Chapter 1 folder, and then save

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

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

  1. 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 Sales.xlsx workbook, identical

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

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

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

shown in Table 1.9 on this new worksheet.

Table 1.9: Column headings for Summary worksheet

Model Annual

Volume

JanJune

Sales to

Dealers

JulyDec

Sales to

Dealers

Total Sales

to Dealers

%Total

Sales to

Dealers

JanJune

%Total

Sales to

Dealers

JulyDec

%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 modelthe 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 JanJune sales to dealers, again ensuring that these values will

automatically

update if any of the input data changes.

  1. Insert the JulyDec 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.

  1. Format all three worksheets so that they have a professional appearance.
  2. Save and close the CKG Sales.xlsx 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.)

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

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

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

  1. 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.)

  1. 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?

  1. The formula =ROUND(710.52,-1) results in what precise value?
  2. Write a formula to round down 63.34% to the nearest percent.
  3. What is the symbol for the less than or equal to relational operator in Excel?
  4. What is the symbol for the not equal to relational operator in Excel?
  5. Review the following worksheet, and then use the COUNTIF function to write a

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

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

of cars that cost more than $20,000.

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

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

example

based on the worksheet shown in Question 11.

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

of the third least expensive car.

  1. 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+ ___________

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

blank cells, rounded to the nearest whole number?

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

  1. 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 Crmes Ice Cream

Judd Hemming is the eastern regional marketing manager for Crmes 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:

Creme.xlsx, which contains the current quarters sales on a worksheet named Flavors,

and HCreme.xlsx, 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 years 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 Creme.xlsx located in the Chapter 2 folder, and then

save it as Creme Current Sales Analysis.xlsx.

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

  1. 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 HCreme.xlsx workbook in the Chapter 2 folder into the newly

added column of the Flavors worksheet.

  1. 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.)

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

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

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

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

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

  1. Add appropriate worksheet titles and formatting to make the worksheets easy to read.
  2. Save and close the Creme Current Sales Analysis.xlsx 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 locationsone 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 LaRosa.xlsx 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 2.15 shows the breakdown of the age categories and corresponding income level

closest to the respondents own income.

Table 2.15: 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 LaRosa.xlsx located in the Chapter 2 folder, and then

save it as La Rosa Demographic Analysis.xlsx.

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

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

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

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

to read.

  1. Save and close the La Rosa Demographic Analysis.xlsx workbook.

Level 3 Determining Inventory Levels for CKG Auto

Another profitable facet of CKG Autos 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 Parts.xlsx located in the Chapter 2 folder, and then save

it as CKG Parts Analysis.xlsx.

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

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

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

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

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

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

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

following;

to be on the cautious side, assume each days 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 Demand.xlsx. Each line item represents

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

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

Actual Demand.

  1. 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 2.16.

Table 2.16: 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.

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

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

formatting and titles to clearly identify the worksheet elements.

  1. Save and close the CKG Parts Analysis.xlsx workbook.

 

 

Chapter 3

Determining Effective Data Display with Charts 154

 

Conceptual Review

  1. List and describe the significance of each of Tuftes 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?

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

when you would use each one.

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

of when you would use each one.

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

charts differ from regular charts?

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

stacked charts differ from stacked charts?

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

  1. 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 Indiana.xlsx.

Complete the following:

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

file as Indiana Travel.xlsx.

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

per Traveler of the average Indiana resident, the average U.S. 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.)

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

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

  1. Save and close the Indiana Travel.xlsx 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 Invest.xlsx workbook.

Complete the following:

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

save the file as Investment Performance.xlsx.

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

certain

to use each chart sub-type only once.

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

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

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

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

  1. Save and close the Investment Performance.xlsx workbook.

Level 3 Illustrating Patterns in Gas Prices for CKG Auto

You have been assigned to CKG Autos 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 Safari.xlsx file contains data on gas prices for

various regions and metropolitan areas around the country.

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

the file as Safari Analysis.xlsx.

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

  1. For each locationregion, state, and citydetermine if a particular location has

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

findings.

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

  1. Create a bubble chart to illustrate the number of weeks that each citys 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.

  1. Save and close the Safari Analysis.xlsx 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 315 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.

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

or FALSE) if this item is over budget.

  1. Write a formula to determine if all of the items are over budget.
  2. Write a formula to determine if any item is over budget.
  3. 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.

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

using the Formula Is methodsuch that the item name would be shaded in yellow if

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

  1. Write a formula to determine if none of the items are over budget.
  2. 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.

  1. 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))

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

=NOT(E3:E8)

  1. What value would the following formula return:

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

  1. 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))

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

  1. 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 applicants

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 1.5 to 4.0)
  • Major Code, universal standardized system that indicates the applicants 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 3.0.
  • 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 3.8.
  • 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 applicants

status is undecided.

Complete the following:

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

save the file as WWC Hiring Analysis.xlsx.

  1. 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.)

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

  1. 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.)

  1. 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.)

  1. 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.)

  1. 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).

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

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

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

  1. Apply conditional formatting to highlight the important points, as follows:
  2. 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.

  1. Save and close the WWC Hiring Analysis.xlsx 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 roomlength, 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 requestedpremium, superior, or economy

Complete the following:

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

Set up the worksheet with the columns and data shown in Table 4.7. 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 4.7: 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 210, 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.

  1. In the column youve 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.

  1. 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 $0.50 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.

  1. 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 $0.65 per sf; if the condition of the wall is 2, the cost

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

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

$0.45 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 $0.50 per sf.

  • Otherwise, no second coat will be required, and a value of $0 should be entered.
  1. In an adjacent column, calculate the cost adjustment for paint quality based on the

following criteria:

  • If premium paint is used, add $0.25 per sf.
  • If economy paint is used, deduct $0.15 per sf.
  1. In an adjacent column, calculate the total cost to paint this room (primer, first coat,

second coat, and adjustments for paint quality).

Write a review

Your Name:


Your Review: Note: HTML is not translated!

Rating: Bad           Good

Enter the code in the box below:



 

Once the order is placed, the order will be delivered to your email less than 24 hours, mostly within 4 hours. 

If you have questions, you can contact us here