CMSC 121-01
Fall 2017
09/13/2017
Spreadsheets
Due:  Wed, September 20
beginning of class (11am)

Assignment:    (worth 15 exercise points)

Create a document (Microsoft Word is fine) with your answers to each of the following questions.  You are expected to use a spreadsheet to help you answer the questions.   For each question, you should give an answer AND a prose description of what you typed into the spreadsheet to get help.  You will hand in a printout of this document at the beginning of class on Friday.

For instance,
  • I typed:   = 4+17*(9^2)   into  cell B1
  • I then dragged in order to copy that into all cells   B2  through B13.
  • I then typed in cell C15 the following:    = 9 * B13
  • The end answer is in cell C15 and is: 12429

1) A population of animals is increasing at the rate of 3% per year. That is, each year 3% more animals are present than the year before.
Questions:
  • For a population of 1000 animals, how many animals will there be in 20 years?
  • In how many years will the initial population double?
  • In how many years will the initial population triple?
  • How long will it take the initial population to double if the initial population is only 500?
  • How long will it take the initial population to double if the initial population is 2000?
  • With 2000 animals initially, did it take longer, shorter, or the same amount of time to double than if there were only 500 to start with?

2)  Joanne Katupsin is an up and coming harp player.   The Joanne Katupsin Fan Club gets 25 new members each month.   Additionally, each time they put a table in the lobby at one of her concerts, they get 6 more members.   For each church bazaar they visit, they will get 2 more members. 

For example:
  • If they visit 0 church bazaars and put a table at 4 concerts each month, they will now get 49 new members each month:   25 each month, plus 6 for each concert (24 total)
  • If they visit 3 church bazaars and put a table at 0 concerts each month, they will now get 31 new members each month:   25 each month, plus 2 for each bazaar (6 total)
  • If they visit 3 church bazaars and put a table at 4 concerts each month, they will now get 55 new members each month:  25 each month plus 6 for each concert (24 total) and 2 for each bazaar (6 total) 
Download the spreadsheet that was created to get them started.

The club currently has 350 members, how many months will it take for them to reach 1000 members in each of the following scenarios:
  • they visit 3 church bazaars and put a table at 4 concerts each month
  • they visit 1 church bazaar and put a table at 6 concerts each month
  • they visit 5 church bazaars and put a table at 10 concerts each month
For full credit, you should create the spreadsheet such that after answering the first bullet above, only the values in cells B3 and B4 should need to change to answer the second and third bullets.
3)  Download the spreadsheet on State Smoking Data.   Each row indicates a state/district/province and the number of packs of cigarettes smoked per capita in that location.  (Not all jurisdictions are included.)

Questions:
  • For the locations given, what was the average number of packs smoked per capita.
  • For the locations given, what was the maximum number of packs smoked per capita.
  • For the locations given, what was the minimum number of packs smoked per capita.
  • What was the average number of packs smoked per capita if you ignore the maximum number and the minimum number?  (In other words, the average of the rest.)
  • EXTRA CREDIT:  How many states had a higher per capita than Maryland?  (Remember, you must have the software determine the answer.)

4)  Download the Shipping Info spreadsheet.  Each row indicates one order.   Write your steps as to how to achieve each item listed below.   Note that I should be able to change the information on any given order and have columns G-J automatically update appropriately.   Thus, the spreadsheet needs to do the calculations.

a)   Customers in VA are to be charged a 5.3% sales tax, while customers elsewhere are not charged sales tax.    Thus, we want column G to indicate how much tax is to be charged on this order.

b)   Gold Customers get free shipping.  Orders over $50 also qualify for free shipping.     All other shipping is $10 (no matter the price or the weight).  We want column H to indicate the amount of shipping on the order.

c)  Column I should indicate the TOTAL cost of the order, including tax and shipping.     For instance, if a customer from Maryland purchases something for $19.99, the total cost would be $29.99 (including $10 shipping and no sales tax).    If a customer from Virginia purchases $60, the total cost would be $63.18 (including $3.18 sales tax and free shipping).

d)   We want column J to indicate how each order is being shipped.    Orders over 5 pounds are sent via UPS.  Orders between 1 and 5 pounds are sent via FedEx.  Orders under 1 pound are sent by USmail.

Handin

Your answers to the above, on paper, at the beginning of class.