Using Spreadsheets to Solve Problems
1.5 Working with Units Problems
Thus far the workbooks we’ve used have contained only a single worksheet. A more complex set of problems are introduced in this chapter which require calculations with multiple unit conversions and workbook designs that span multiple worksheets. As the sets of data inputs and calculations become more complex, we will need to understand how to use and ultimately design worksheet solutions that are easy to use and maintain.
working with unit conversions:
The problem
First consider a simple problem that analyzes the compensation received at two different places of employment.
Alex works for a fast food restaurant 20 hours a week for 50 weeks per year. He makes $9.00 an hour. The chain across the street is offering him an annual wage of $8500 for the same number of hours per week and a two week paid vacation. He is willing to switch jobs only if the new job pays more than his old one. Should he switch jobs?
The first step in solving this problem is recognizing that not all of the information is in comparable units. To compare the compensation received at Alex’s current job versus the compensation he would receive at the other, the salary amounts must be in equivalent units (dollars per hour, dollars per year, etc.). The current employment wage data is in dollars per hour, while the other offer is in dollars per year.
Approach #1 – converting all units to dollars per year
One approach is converting Alex’s current wages to dollars per year. The following technique can be used given the wage information provided: dollars per hour, hours per week, and weeks per year.
· First convert dollars per hour to dollars per week using the number of hours per week Alex works. Notice how hours in the denominator cancel out hours in the numerator resulting in the unit dollars per week.
9 dollars * 20 hours = 180 dollars
hours week week
· Then take dollars per week just calculated and convert it to dollars per year. Alex works 50 weeks per year. The weeks in the denominator will cancel out the weeks in the numerator resulting in the unit dollars per year.
180 dollars * 50 weeks = 9000 dollars
week year year
Those with good mathematical skills should be able to convert dollars per hour directly into dollars per year as follows:
9 dollars * 20 hours * 50 weeks = 9000 dollars
hour week year year
anaylzing the data
The current job’s wage per year has now been calculated and can be compared with the wage offered from this new job. The job at the other restaurant pays $8500 per year. Now that the values are in “like units,” in this case dollars per year, the two values can be compared in several different ways:
· Use a relational expression to see if the proposed job pays more than the current job. By doing this analysis, it becomes clear that the remuneration at this proposed job is less than what Alex receives at his current job. An IF function can also be used if a different result is desired other than a TRUE or FALSE value.
$8500>9000 à FALSE
· Use subtraction to quantify the cost difference of the current job vs. the proposed job.
$8500 dollars/year - $9000 dollars/year à - $500 dollars difference
· Compare the percent difference by calculating the ratio of the difference in wages vs. the current wage.
(new wage - current wage)/current wage à (8500-9000)/9000 à -5.6%
Approach #2 – converting all units to dollars hour
A second approach is to convert $8500 per year into dollars per hour. First take the dollars per year and divide it by weeks per year to arrive at dollars per week. Notice that only the weeks worked are used (50) so the two salaries are for the same amount of work. The paid vacation is already taken into account by including it in the wage. Finally divide dollars per week by hours per week to obtain dollars per hour.
8500 dollars 50 weeks 170 dollars 20 hours 8.5 dollars
year year week week hour
These conversions can become quite complex. The easiest way to deal with this complexity is to write out the units and perform each of the term cancellations. Engineers have always been taught that if you solve the “units” conversion problem the rest is easy. A couple of rules of thumb from math that you should remember:
· A unit in the numerator can be canceled out by a like unit in the denominator.
· When dividing two fractions, the denominator of the denominator becomes the numerator. Alternatively, invert the 2nd fraction and multiply. In the example below you can cancel out weeks to get dollars per year.
dollars weeks dollars * year dollars
year year year weeks week
· Only add and subtract like units.
· Always make sure your conversions make sense. For example: You convert dollars per year into dollars per week and find your result is more than what you started out with, is this correct? Logic would tell you that weeks are smaller units than years. Thus, you would be expecting dollars per week would be less than dollars per year.
What if you were told that the first job pays $9 per hour in Canadian dollars and the second job pays $8.50 in US dollars? How can $8.50 US be compared with $9.00 Canadian? The conversion rate on a website gives the following info: There are 0.95451 US Dollars per Canadian Dollar. How can these values be compared?
· To convert $9 Canadian to US dollars multiply Canadian dollars by the conversion of US Dollars per Canadian dollar
9 Canadian$ * .95451 US $ 8.59 US$
Canandian$
It would be incorrect to divide 9 by .95451, from a units standpoint that would result in the unit Candadian$2 per US$. Logically, if you would divide 9 by .95451 the resulting value will be larger. As the US dollar is worth more than the Canadian dollar (at this point in time), one would expect $9 Canadian dollars to result in fewer US dollars.
Alternatively, $8.50 US could be converted to Canadian dollars by dividing by this conversion factor.
Using a spreadsheet to execute the solution
Now that the first step, understanding the problem, of our problem solving approach is completed, the next step is to execute it within an Excel workbook.
Figure 1
The spreadsheet in Figure 1 contains the input information given in this problem, with each input value explicitly listed. For a simple problem such as this, it may be easier to just plug the values into a calculator. But, what if later on one or more of the input values change? The advantage of listing each variable explicitly becomes quickly apparent.
Using the spreadsheet in Figure 1, write the necessary formulas to determine whether or not to switch jobs.
Using the first approach, calculate the annual income earned for Alex’s current job in dollars per year. This requires multiplying the dollars per hour by hours per week and weeks per year to arrive at dollars per year. Translated into Excel syntax we have the formula =C2*B3*B4 in cell D2. Then compare the result with the $8500 proposed salary for the new job. Previously this was shown using a simple relational expression. Here, in cell D11, use an IF function to explicitly list whether or not to switch. =IF(D7>D2, “switch”, “don’t switch”). This can also be done in one step by substituting C2*B3*B4 for D2 in the IF statement as follows:
=IF(D7> C2*B3*B4, “switch”, “don’t switch”)
Using the second approach to compare the dollar per hour rate for both jobs by writing the formula =D7/B8/B7 in cell C7. Cell C7 will then contain the hourly rate for this new job. A formula can then be written to compare these two rates.
Exercise 1.5 –1 Units & Spreadsheet Design: Water Problem
sheet: units
A / B1 / Unit / Conversion
2 / Cups per person per practice/meet / 4
3 / cups/gallon / 16
4 / pounds/gallon / 8.22
5 / $/pound to ship < 2000 pounds / $1.00
6 / $/pound to ship >=2000 pounds / $0.75
7 / $/gallon of water / $0.25
sheet: Price
A / B / C / D / E / F / G / H / I1 / Group# / Number of people at Practice or meet / # gallons / Purchase cost of Water in $ / Unit cost of shipping in $/pound / Total shipping cost in $ / Total Cost of Water in $ / % ship costs / $/cup of water
2 / 1 / 10 / C2 / D2 / E2 / F2 / G2 / H2 / I2
3 / 2 / 50
4 / 3 / 100
5 / 4 / 250
6 / 5 / 1000
7
You are an assistant manager for a sports team and are charged with the job of ordering water for your practices and meets. You need to calculate the cost of water for a series of group sizes that range from 10 people to 1000 people. The cost of water includes two components; the cost of purchasing the water and the cost of shipping the water. Water shipment costs vary based on the total weight of a shipment. Assume the water for each practice/meet is shipped separately. You have developed a workbook with two worksheets: Units and Price. You will use the Price worksheet to summarize the costs for each group size. You have put the following information on the Units worksheets:
· Each person drinks an average of 4 cups of water per practice/meet - units!B2
· There are 16 cups per gallon – units!B3
· A gallon of water weighs 8.22 pounds - units!B4
· Shipping costs of shipments weighing less than 2000 pounds are $1.00 per pound. – units!B5
· Shipping costs of shipments weighing 2000 pounds or more are $0.75 per pound – units!B6
· A gallon of water costs $0.25 – units!B7
1. Write a formula in Price!C2 to calculate the number of gallons that will be drank by group #1. Assume you will copy this formula down the column.
______
2. Write a formula in Price!D2 to calculate the cost of purchasing the water in dollars that will be drank by group #1. This formula will be copied down the column.
______
3. Write a formula in Price!E2 to determine the unit cost in dollars per pound to ship the water that will be drank by group #1. Assume you will copy this formula down the column.
______
4. Write a formula in Price!F2 to calculate the total cost of shipping the water in dollars that will be drank by group #1. Assume you will copy this formula down the column
______
5. Write a formula in Price!G2 to calculate the total cost of water in dollars that will be drank by group #1. Assume you will copy this formula down the column.
______
6. Write a formula in Price!H2 to calculate the shipping cost percentage of the total cost for group#1. Assume you will copy this formula down the column.
______
7. Write a formula in Price!I2 to calculate the Total cost of one cup of water in the ten person group. Assume you will copy this formula down the column.
______
Units Problems 1.5 CSE1111 Page 67
Exercise 1.5 –2 Units & Spreadsheet Design:Chapter Review
Refer to the attached Excel worksheets for Questions 1-10:
The Quarter is over and its time for the students to celebrate their success with a party. You are giving a party and serving “Mr. T’s famous chili”. You have created an Excel workbook with three worksheets. The recipe is given on the recipe sheet. On the units sheet are some unit equivalencies you will need. On the shopping sheet you will create the shopping list you will need in order to make chili for 20 adults. Each recipe serves 10 adults. Sheet names appear below each table. When referring to cell addresses the sheetname!cell address format was used (e.g., recipe!A1).
- Write an Excel formula in cell units!B7 to calculate the number of multiples of the recipe needed to feed all the adults at the party.
- Write a formula in cell shopping!C2 to calculate the quantity of meat in pounds you will need to purchase for the party. Assume you will copying the formula into cell shopping!C3.
- Write an Excel formula in cell shopping!C4 to calculate the ounces of chili powder you will need to purchase for the party.
- Write an Excel formula in cell shopping!C5 to calculate the pounds of beans you will need to purchase for the party.
- Write an Excel formula in cell shopping!F2 to calculate the total cost of the meat. Assume you will copy this formula into F3:F5.
- Write an Excel formula in cell shopping!F6 to calculate the total cost of your shopping list. Assume you have copied down the formula in question 5 into F3:F5.
- Cell shopping!G2 is formatted as a percent. Write an Excel formula in cell shopping!G2 to calculate what is the cost of meat as a percentage of the total cost. Assume you need to copy this formula into G3,G4 and G5.
- What is “goal seek” and how can it be used in this problem?
- A friend has told you about a local deli that sells two types of prepared chili in tubs (regular and extra meat). Each tub feeds approximately 4 people. The regular chili from the deli costs $12.95 per tub and the extra meat chili costs $15.95 per tub. You want to decide between cooking your own chili and purchasing the prepared chilis based on the following criteria:
· If the prepared extra meat chili costs less than cooking then you will buy it.
· If the extra meat chili is more expensive than your cooked chili but the regular meat chili is cheaper than your cooked chili buy the regular chili.
· If both of these prepared chilis are more expensive than your cooking then you will cook your own.