In this activity you will use a spreadsheet to create graphs associated with tables of numbers, then use the graphs to solve problems.

A salesman’s earning depend on the volume of business he can do. He is offered a choice by his employer: a salary consisting of a fixed sum of $250 plus a commission of 1% of the dollar value of his sales, or a fixed sum of $200 plus a 2.3% commission. To study these choices the salesman would like to graph his total take-home pay as a function of total weekly sales-for each of the two choices.

Spreadsheet (Excel):
1. In Column A enter the total weekly sales (the inputs) of the salesman. Start with $0 in A1 and increase it by $500 for each successive cell of the column. Highlight cell A1 and A2 and drag to cell A20 (this is a shortcut to filling each cell out separately).
Next, in column B, you want to enter the corresponding outputs for the first salary option: $250 plus commission of 1%. In B1 type: =250+.01*A1. Then hit enter. Did 250 appear in B1? Great, let’s move on. Highlight B1 and drag to cell B20. Are all your corresponding values in column B there? (Double-check: B2=255 and B20=345).
We are now ready to graph option 1. From the ‘Insert’ menu select ‘Chart…’. Select ‘Line’ from the Chart type options. Select the first one. Next> Click on the 'Series' tab and place your cursor in the ‘Category (X) axis labels’ and highlight your inputs in column A (A1-A20). Place your cursor in ‘Values’, and highlight your outputs in column B (B1-B20). Remove Series 2 from the ‘Series’ box. Type ‘Option1’ in the ‘Name’ box. Next>  Click on the ‘Titles’ tab and type ‘Sales’ for the x-axis and ‘Salary’ for the y-axis. You can select any other options of you’d like. Next> and then Finish.

2. It’s time to consider the salesman’s second option and construct the graph to it. In column C enter the weekly salary for the second option: a fixed amount of $200 plus a commission of 2.3%. In C1 type: =200+.023*A1. Then hit enter. Did 200 appear in C1? Great, let’s move on. Highlight C1 and drag to cell C20. Are all your corresponding values in column C there? (Double-check: C2=211.5 and C20=418.5).
We’re ready to graph again. This time we want to graph the salary corresponding to the second option as a function of total sales. From the ‘Insert’ menu select ‘Chart…’. Select ‘Line’ from the Chart type options. Next> Click on the series tab and place your cursor in the ‘Category (X) axis labels’ and highlight your inputs in column A (A1-A20). Place your cursor in ‘Values’, and highlight your outputs in column C (C1-C20). Remove Series 2 from the ‘Series’ box. Type ‘Option2’ in the ‘Name’ box. Next>  Click on the ‘Titles’ tab and type ‘Sales’ for the x-axis and ‘Salary’ for the y-axis. You can select any other options of you’d like. Next> and then Finish.

3. At this point we are ready to compare the two options. We will do this by graphing the salaries for the two options on the same coordinate system. From the ‘Insert’ menu select ‘Chart…’. Select ‘Line’ from the Chart type options. Select the first one. Next> Click on the series tab and place your cursor in the ‘Category (X) axis labels’ and highlight your inputs in column A (A1-A20). Place your cursor in ‘Values’, and highlight your outputs in column B (B1-B20). Remove Series 2 from the ‘Series’ box. Type ‘Option1’ in the ‘Name’ box. Add a series and place your cursor in the ‘Category (X) axis labels’ and highlight your inputs in column A (A1-A20). Place your cursor in ‘Values’, and highlight your outputs in column C (C1-C20). Remove Series 2 from the ‘Series’ box. Type ‘Option2’ in the ‘Name’ box. Next>  Click on the ‘Titles’ tab and type ‘Sales’ for the x-axis and ‘Salary’ for the y-axis. You can select any other options of you’d like. Next> and then Finish.

Turn in one print-out of both options on the same coordinate system.

4. Now that you can see the two salary options displayed in graphical form, which option would you choose? Discuss briefly.
 

You have $10,000 to invest for 20 years and are considering two investment schemes. The first scheme is a one-time offer and has a fantastic interest rate of 15%, but the interest is simple interest. In other words 15% of your initial investment of 10,000 is added to your account each year. You can keep your money in the account as long as you like but once you withdraw your money the offer will no longer be available to you. The second scheme has a lower interest rate of 9% but the interest is compounded annually. You can put your money in this account anytime you want. You know that for the first few years the first scheme is better. Your plan is to put the money initially in the first account, leave it there for a few years, then switch it over to a second account. You want to know when would be the best time to make the switch. To find out you decide to graph, using a spreadsheet, the value of each account as a function of the number of years the money is in the account.

Spreadsheet (Excel):
1. In column A type 0 through 20 (# of years). In column B place the values of the 15% simple interest account for each year. For each year, you want the cell in its row to show the value of the account at the end of the year. In B1 type 10000. In B2 type: =B1+1500. Click on B2 and drag to fill in column B. In the third column C, place the values of the 9% compound interest account for each year. In C1 type 10000. In C2 type: =C1+.09*C1. Click on C2 and drag to fill in column C. Finally, you want to graph the values of the two accounts as a function of the number of years the money is left in each account. Follow the same procedures as in # 3 in THE SALESMAN’S PROBLEM.
Turn in a print-out of both investment schemes on the same coordinate system.

2. How do the shapes of the two graphs compare? Why should this be?

3. Based on what you see in the graphs, after how many years should you switch your money from the 15% simple interest account over to the 9% compound interest account? Discuss briefly.
 

REFLECTIONS
1. What do you think about a spreadsheet as a tool for helping you solve a problem where you might need to create a graph?

2. As a future teacher, what do you think about introducing spreadsheets to your future students as a tool to help them with graphing?

Turn these responses in on a separate sheet of paper typed.
 

Updated: 08/21/2003
Back to K-8 Mathematics Technology Projects