 |
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:
Total sales < $1000
no bonus
Total sales = $1000 to 2000
bonus = 7.5% of total sales
Total sales > $2000
bonus = 10% of 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
OTHERWISE
tax is $4,057.50 plus 27.5% of the amount over 27,050
OTHERWISE
IF taxable income <= 45,200 THEN
tax is 15% of taxable income
OTHERWISE
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!)Now print a third time, (as-displayed, not cell formulas this time) with the following data:
Taxable interest: $175.25
Unemployment: $0.00
Can parents claim you?: Yes
Income tax withheld: $10,607.24
Earned Income Credit: $0.00
Married Wages, salaries, tips: $52,525.25Thus, you will submit three printouts for this problem: single filer (as displayed and as cell-formulas) and married filer (as displayed).
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
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-formulasYou will notice that question c would be essentially unanswerable without the use of a spreadsheet.
b. x = 4, include printout of as-displayed only
c. x = 0.5, simply answer the question
Example:
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.