Understanding and Using Computers
Microsoft Excel Tutorial #4

You must design your own spreadsheets to solve two of the four the problems given below. Your spreadsheets will be judged in terms of content (does it do what you were asked to do?), style (is it presented in an aesthetically pleasing way?) and design (are the formulas used and layout done in such a way that you have taken advantage of the power of the spreadsheet?).

For each problem you must place your name in the "Home" box (A1) on the spreadsheet. Note: be certain to save your work often to avoid frustration. Also, make backup copies at the end of every session so that your work is safeguarded in the event that something damages the original disk.

Problem 4.A
You are the manager of a small department store. Each of your employees is paid an hourly wage and a commission based upon their sales. The pay is determined as follows:

  1. Hourly wage
  2. Type 1 sales: no commission goes to the employee
  3. Type 2 sales: 5% commission goes to the employee
  4. Type 3 sales: 15% commission goes to the employee
  5. Bonus:
    Total sales < $1000
    no bonus

    Total sales = $1000 to 2000
    bonus = 7.5% of total sales

    Total sales > $2000
    bonus = 10% of total sales
Create a spreadsheet to determine the amount of pay each employee will receive and to determine the amount of total sales for the store. Also display the percentage of the store's total sales that goes into payroll (i.e. total payroll divided by total sales).

Please print the spreadsheet twice, first print as it is normally displayed then again as cell formulas. Use the following data when printing the spreadsheet.

Employee    wage    hours  type1  type2  type3
Kathy       9.00     20     800      0    150
Timmy       7.25     12     700    250    300
Steph       7.00     25     900    300    500
Matt        7.00     40     900    800    900
Kristen     8.00     30     500    800    100
Heidi       7.50     25     900     95      0
Carol       8.75     10     350    100      0
Kaitlin     7.25     15     500    600      0
Taylor      7.50     12     300    300    350
Ray         6.75     10     100    250      0
Elaine      7.00     15     350    700    300

Problem 4.B
This year's tax time has come and gone, but let's create a spreadsheet that can estimate tax refunds for the future. Implement a line oriented spreadsheet to handle entry of the data from IRS Form 1040EZ (see attachment). On Sheet1, the A column of the spreadsheet should have labels identifying each line of the return (as in the actual form but probably not a word for word duplication). Sheet1's B column is for all data entries by the user and all calculations which may be made by the spreadsheet. Sheet2 must be used to handle the calculation found on the back of the form for those people whose parents can claim them on their form. The 1040EZ form can be downloaded from the IRS web site at this link: http://www.irs.gov/pub/irs-pdf/f1040ez.pdf. [Note that it is in Portable Document Format (.pdf) and requires Adobe Acrobat Reader to view or print.]

All cells should be protected except the cells which are needed for a user entry.

Note: to calculate the tax due for the filer(s) on line 10, use the following formula as discussed in class:

   	IF single THEN
        IF taxable income <= 27,050 THEN 
             	tax is 15% of taxable income
                 	tax is $4,057.50 plus 27.5% of the amount over 27,050
        IF taxable income <= 45,200 THEN 
                 	tax is 15% of taxable income
                 	tax is $6,780.00 + 27.5% of the amount over 45,200
Please note that the form of the tax calls for the use of an IF( ) function which has two IF( ) functions in its actions. When developing your spreadsheet, you may wish to test it by putting in the data you have from your actual tax form this year. If the answer is very different from the one you filled out, that's a sign of a problem somewhere!

Please print the spreadsheet twice, first print as it is normally displayed then again as cell formulas. Use the following data when printing the spreadsheet.

Single Wages, salaries, tips: $54,321.00 (you must have a GREAT part time job!)
Taxable interest: $175.25
Unemployment: $0.00
Can parents claim you?: Yes
Income tax withheld: $10,607.24
Earned Income Credit: $0.00
Now print a third time, (as-displayed, not cell formulas this time) with the following data:
Married Wages, salaries, tips: $52,525.25
Taxable interest: $300.00
Unemployment: $0.00
Can parents claim you?: No
Can parents claim spouse?: Yes
Income tax withheld: $6,708.29
Earned Income Credit: $0.00
Thus, you will submit three printouts for this problem: single filer (as displayed and as cell-formulas) and married filer (as displayed).

Problem 4.C
Warning: if you dislike mathematics, simply reading this problem description may make you nauseous. This is for students who enjoy playing with mathematics.

The natural logarithm of a value x (greater than or equal to 0.5) can be approximated by the following series:

Test this method by designing a spreadsheet as follows. In the first colum simply keep track of the number of terms which have been computed for the series. As shown above, the first three terms are written out, the remaining terms follow the same pattern. In the second column compute the value for that particular term of the series. In the third column keep a running total of the sum of all terms in the series. In the fourth column show the deviation from the "actual" result by referring to the built-in LN( ) function of Excel. Your spreadsheet should be fast and easy to change to test the results for other values of x.

You must use your spreadsheet to answer the following "what-if?" questions:

How many terms must be computed to obtain no more than 0.01% error for the following values of x:

a. x = 3, include printouts of as-displayed and cell-formulas
b. x = 4, include printout of as-displayed only
c. x = 0.5, simply answer the question
You will notice that question c would be essentially unanswerable without the use of a spreadsheet.


Problem 4.D
Propose a spreadsheet which will be useful for you -- for example, to help solve a problem in another class, for an employer, for a group to which you belong, etc. Your spreadsheet must be "reasonably" complex to be acceptable. The proposal must be submitted via email to the instructor no later than Monday, December 2.

Once approved in writing by the instructor, create the actual spreadsheet for submission.