INTEGRATING EXCEL INTO BUSINESS CALCULUS Mike May, S.J. Saint Louis University Department of Mathematics and Computer Science 220 N Grand Blvd Saint Louis, MO 63108 [email protected]

Acknowledgement – This work was inspired by a previous ICTCM award winner (2001 Baltimore), Networked Business Mathematics, by Bob Richardson and Brian Felkel, at Appalachian State University. That work is currently available from Kendall and Hunt Publishers This is a report of a work in progress, developing course materials for teaching a course in business calculus with the assumption that the students have daily access to Excel. The plan is to develop an "electronic text", probably in the form of a CD. Currently I would assume that the students bring their laptops to class. It is useful to start by exploring several premises I had in mind as I organized the course and designed material. After looking at the premises I will give an overview that focuses on how this project compares both to the inspiring text and to traditional texts to put this project in perspective. We will then look at an example of an accompanying workbook and give guiding principles for the project. We will look at major places where the choice of technology changes the materials covered. We finish by giving initial reactions to the project and the current status of the project. Premises behind the design of the text: The first set of premises lead to the decision to bring Excel into a business calculus class. A standard issue in mathematics classes aimed at non-majors is to convince students that the mathematics they are learning is connected to their primary field of study. To address that issue, there is a significant advantage to using "industry standard" software, hardware, and to use a selection of problems common in the home discipline of the students. It is clear that, in the business community, Excel is the computational tool of choice. At SLU, the advisory panel to the business school has advocated "Excel across the curriculum."

175

The second set of premises concern the appropriate way to bring a technology into a course. A first premise here is that any technology used in a classroom will require instructional time teaching the students to use the technology. Additionally, since math courses should be evaluated for teaching mathematics, the incorporation of a technology in a course should be undertaken only if it increases the amount of math content that can be covered. We also note that the curriculum of a course is generally organized by looking at subjects that are accessible to the students. This means that the choice of technology for a course has a subtle but pervasive impact on the content of a course. This means that if a technology is to be seriously used in a course, the model textbook for the course would not work well if the technology was not used in the course. A third set of premises concern the particulars of teaching business calculus with Excel. Given the economic realities of math departments at large universities, business calculus is often taught by adjunct instructors and teaching assistants. The text should be complete enough to be used with the level of background one typically finds in adjunct instructors and teaching assistants. In particular the book must include enough Excel instruction to be used when neither the teacher nor the student is a master of Excel. The focus of the book however should clearly be to teach mathematics rather than to tech Excel. The project in perspective: It is worthwhile to compare this project both with a standard business calculus text and with the inspiring text, Networked Business Math (NBM) by Richardson and Felkel. The standard business calculus text is technology agnostic. While there may be some technology sections or problems, these books are written so that they can reasonably be used in a classroom even if all technology is forbidden in that classroom. When technology is used there is an assumption that it will be a graphing calculator. This means that Excel usage is an add-on and typically restricted to projects. This sends the implicit message that there is no fundamental connection between the mathematics in the course and the business world where the students plan to function in the future. Of equal importance, the topics and examples selected can all be done easily without the use of technology. It reinforces the idea that we expect functions to be defined by easy algebraic formulae, rather that by data, and that important functions are all defined over a domain that is composed of intervals of the real line. Little attention is given to the issue of modeling real world phenomena with formulas. The definition of marginal functions, defined in the business world in terms of a difference quotient with a delta of 1, is modified into another name for the derivative. NBM makes a number of changes in approach from a traditional text. Since it is delivered on a CD rather than as a paper book, it easily includes Excel files that the students can open and manipulate. It also contains a generous collection of video clips that address the issue of training for technology usage. The mechanics of running software are more easily handled with such a presentation rather than with a static text. Perhaps more important are the shifts in content. NBM seriously rethinks the material of calculus and makes shifts appropriate to the use of both kinds of examples used for business students and to the kinds of functions used. In particular there is a deeper focus

176

on functions that are discrete as well as on the process of modeling where formulas are obtained as the functions in a class that provide the best fit to a data set. This matches the needs of the business world. The most obvious example is the treatment of the marginal functions. In contrast to traditional texts, marginal functions are correctly defined using a difference quotient of the base function. More attention is explicitly given to the finding optimal solutions when the function in question is only defined over the integers. Another shift of note is that difference quotients and Reimann sums, the discrete constructions that lead up to the derivative and integral, remain important in their own right, rather than simply being stepping stones to the more important continuous constructions. While NBM is to be lauded for the improvements that it makes in designing an Excel based calculus text for business students, the premises that I mentioned above lead to a number of issues where this project makes different design decisions. In comparison to NBM, this project avoids the use of macros or advanced functions that the students cannot do by hand. This project uses Wolfram Alpha rather than Maple, and it uses it in a much more limited role. This project encourages a stricter Excel style usage to produce workbooks that would be acceptable in a business class or in a job setting. As mentioned above, part of the motivation of using Excel in a business calculus class is to be able to tell the students that we will be doing mathematics using tools and practices that are consistent with what they will use in the their other business courses and in their subsequent careers. These considerations lead me to avoid the use of macros in Excel in my project. Teaching the programming skills needed to make the macros would transform the course from calculus course that uses Excel into a course on Excel. Using macros that the students cannot recreate works against the model of teaching transferable skills. For similar reasons I avoid the use of advanced commands until after the students have worked through the problem using only standard arithmetic and algebraic functions. To give a specific example, future value problems are standard examples when considering discrete accumulation. From first principles it involves adding a series of payments and accruing interest over many periods of time. This is such a standard problem that Excel has a command for computing future value with an initial value and with regular payments. In this project that command is presented after the general case, which is done using only basic arithmetic operations. The use of predefined macros and advanced commands will have a tendency to be learned for the test and only be used in the course. A more basic understanding can be applied more broadly. Given the importance of modeling in the course, we also give a fuller explanation of best fit functions. For similar reasons my project shifts from an integration of Maple in the text, as is done by NBM, to some usage of Wolfram Alpha. The easiest rational for the choice of technology is that Excel is "business standard" and Maple is not. Most business students will not have Maple available on their office computers in the near future. Not using Maple reduces the "technology teaching overhead" of teaching two major programs in a single course. Given the previous comments a reader may ask why Wolfram Alpha is used in the project. While we do not expect many business students to have a full CAS package on their desktops in their next job, we do expect them to have a browser and internet access. Wolfram Alpha will this be available to them in the future. We also note

177

the Alpha is much more forgiving in the syntax that it accepts in comparison with Maple. This is particularly true for the simple problems we use it for in the course. The shift from Maple to Alpha reduces the amount of technical instruction needed. We also limit the use of Alpha to two basic situations. It is a useful tool for checking symbolic computations, particularly differentiation and integration. The use of Alpha also allows a shift in the application problems in integration, where few techniques are covered in the course. The use of Alpha allow us to look at application problems where the student should be able to set up an integral, but where the techniques needed to evaluate the integral are beyond the scope of the course. To give an example, an appropriate problem for this course looks at the current value of a revenue stream. A student should be able to set up the integral where the integrand is the product of an exponential function and the revenue function. However, if the revenue function is a polynomial, the integral requires integration by parts, which is not covered in the course. It is useful to be able to tell the student that they are expected to set up integrals from application problems, even if they need CAS to evaluate the integral. A subtler shift in this project from NBM concerns style in use of Excel. In keeping with the premises given above, workbooks should be written in a style that would be acceptable in a business class or in a workplace. This is a shift from standard mathematical conventions where work is designed to be very concise and abstract. We cover this in greater detail in the next section. An example from an accompanying workbook: Following NBM, It is assumed that an Excel workbook will accompany each section of the text. These workbooks will allow students to manipulate examples and to check formulas. It is worthwhile to look at two views of a workbook, with values and formulas showing. We will look at the first example in the section on future value.

178

To build connection with the student's home discipline, the workbooks should demonstrate good Excel programming practices. The screen shot above illustrates some of those practices. At a general level, the "good Excel practices" could be summarized as asking the students to write in a way that is easy for someone else to read and understand the student's work. Some features are worth noting. • The information defining the question and the information in the answer are both in a summary that can be viewed in one screen at the top of the workbook. • The variable names are descriptive, typically made by running words together. (We used AnnualRate for the annual rate of interest rather than a typical math variable name like r.) • The problem is documented with textboxes. We can compare the screen shot above with a similar screen shot that shows formulas.

Once again there are some features that are worth noting: • The formulas in the cells are quite simple, with only a single step performed in each cells. This aids readability. • Absolute and relative cell reference are used in such a way that the user only needs to type the first to or three rows the computational area and then can quick fill the rest of the page. • Answers are brought back to the top with the offset command. One issue that shows up with Excel is that a typical problem involves more typing than a typical problem from a traditional text. This is one of the reasons for providing workbook files to the student to use. The standard conventions of mathematics writing

179

that create very dense abstract notation are being shifted to emphasize readability and communication. Guiding principles and observations: At this point it is worthwhile to summarize some of the guiding principles of course that would be taught out of the book produced for this project. The heart of the syllabus should match the bulk of the syllabus for a standard business calculus class. However the shift is assumed technology provides an opportunity to rethink some of the choices of material for the course. In particular, the capabilities of Excel in handling data will lead to a shift in the balance of emphasis between discreet and continuous functions. The easy of producing best fitting functions will also lead to more emphasis on topics related to modeling and choice of function used to represent data. It is also assumed that there will be greater attention to notational and presentation standards, as this is typically a last math course for students who will them apply what they have learned in settings other than a mathematics course. Some points where the material changes with the technology: While most of the material is the same it is worthwhile to highlight some places where I see shifts in the course as it is rethought to use Excel as a computational engine. The most obvious changes are connected with integration, or more precisely with the discrete sums that in the limit become integrals. In a traditional discrete accumulation is handled as a point along the way without much intrinsic interest. With a spreadsheet, accumulation is a natural operation and becomes a full chapter. In the sections above we looked at future value using a "bank statement method" where the change in value in a month is straightforward using arithmetic operations. The catch is that we repeat the same operation 82 times, something that would be mind numbingly tedious without a spreadsheet. We use a rule of three in approaching accumulation problems. Each problem is first solved using the tedious approach that works from first principles. This approach will be easy to remember and is very flexible. A second approach uses mathematical formulas that reduce a series to a single sum.

The third approach uses the advanced Excel function, in this case FV.

180

The same triple approach is also used with present value and amortization. When we move on to integration, evaluation of the definite integral by right hand rule is fairly straightforward.

It is also straightforward to use the summed area function and use the best-fit curve capability of Excel to establish integral rules for basic functions, like monomials or exponentials. This allows the fundamental theorem of calculus to be introduced as a generalization of experimentally found special cases. The approach that starts with the definite integral allows an emphasis on applications where the solution is seen as the limit of Riemann sums. As mentioned above, the Wolfram Alpha is used so that the choice of application problems can be separated from the list of integration techniques covered in the course. Looping to the front of the course, the first chapter of a calculus text is typically a review of material from pre-calculus. As this project makes that review there are a couple of additional features to include in the material. The students need to use this material to work through a simple tutorial process on basic Excel usage. In particular, experience has shown that they will have trouble with some of the new syntax used by Excel and with the difference between absolute and relative cell references. Another issue will be graphing. Simply put, easy graphing is not one of Excel's strengths. The students will need to be taught how to make a graph from a table. It will be useful to

181

teach them to graph a function with parameters so that they can look at families of functions.

As students are working through the material they also need to explicitly refresh the notion of using a function to create a model that matches a given data set. This will look at best fitting curves. When we get to derivatives we can easily use Excel to look at the intuitive limit as a notion for the derivative.

182

However, we will generally find it more useful to use the approximation for the derivative that is used by graphing calculators, where the derivative is approximated as a balanced difference quotient with delta set at 0.001.

This approximation is fairly accurate. It will also let us graph a function and its derivative in the same window. Initial Reactions: While developing the material for this project I have been using the approach described above for a section of business calculus each year. I have been keeping tabs on the reaction of various stakeholder groups. The first group of stakeholders is the faculty and administration of the business school. They will have to decide if this is how they want their students to be taught the material. As a group, they have been wildly enthusiastic about the project. It fits well with their general plans for what their students need to know. A second group of stakeholders is the local business community. He general response I have gotten from them is that this approach is so obvious that they do not understand why we have not been using it for years. The third stakeholder group is the students who have taken the course. They are positive since they find the course clearly connects with what they are learning in their business courses. Status of Project: I have been using NBM as a base and replacing sections as I teach the course giving me a chance to class test material as I go. At this point about 40% of the sections have been replaced. With a sabbatical for the 2011-2012 academic year, it is anticipated that a complete first draft with be done by the end of spring 2012. People interested in class testing some or all of the material should contact the author at [email protected].

183