Due Date / Check Concourse for the assignment due date.
Total Points / 15
Objectives
/ In this assignment you will demonstrate proficiency in using the following· Data Validation
· Conditional Formatting
· Formulas and Functions: IF, SUMIF, COUNTIF, SUMIFS, COUNTIFS, PMT, FV, LOOKUP, and others from previous assignments)
· Working with multiple worksheets
· Calculations and formatting across multiple worksheets
· Creating an Amortization table
· Charts
· Concepts from previous Assignments
To Complete The Assignment:
1) Copy the MGT20600_A2_S11.xlsm file from your section’s Assignment folder on the Courseware I: drive to your computer’s desktop.
2) NEVER open or work on any files directly through WebFile.
3) Always enable macros. If not enabled, you will not be able to complete this assignment.
4) When you are finished, save the file and copy it to the location where you keep your assignment backups on the N: Drive. If you’re working on the desktop of a campus computer, make sure that you make another backup copy of the file you just finished onto a USB Flash drive or in another directory on your N: drive. Any data saved on a computer’s desktop in a lab is lost when you log out.
5) Very Important: NEVER open or work on any files directly through WebFile. Work this and all other assignment files from the desktop in a cluster, a personal USB flash drive, or from a designated drive on your personal computer, and make sure that you BACKUP your completed work on your N: Drive.
A. Complete the Workbook Documentation
Your superiors were so pleased with the spreadsheets that you created in the previous assignment that they have requested your assistance with some new ones.
1. Before opening your file, rename your MGT20600_A2_S11.xlsx file to netID-MGT20600_A2_S11.xlsm where the prefix is your netID.
(Example: littlefi-MGT20600_A2_S11.xlsm.)
2. Open your netID-MGT20600_A2_S11.xlsm workbook file, and select the Documentation! worksheet tab.
3. Enter your full name in Documentation!C5.
4. Enter your netID (e.g.,littlefi) in Documentation!C6.
5. Enter a function in Documentation!C7 that returns the current date. Format this cell so that it looks like this: 14-Mar-2001.
6. Enter your lab instructor’s name in Documentation!F5.
7. Enter your lab section number in Documentation!F6.
8. Adjust column widths so that all entries can be seen, if necessary.
B. Defining Data Validation and Conditional Formatting
Nurseries and Landscaping companies in states that have a winter season go dormant during this time, much like the perennial plants they sell. This is the time for the Worm’s Turn employees to have their ‘fun in the sun’ by taking incentive cruises provided by the company at a large discount.
Worm’s Turn management created the Cruises! worksheet to keep track of the incentive cruises that they are planning for this coming winter season. However, the employees are having difficulties; they are accidentally entering incorrect values into the worksheet. For example, an employee might enter a negative number in the Number of Nights column, or respond with an answer other than “Yes” or “No” to the Gratuities Included column. You have been asked to modify the worksheet by adding the appropriate data validation that will control what can be entered in rows 7 through 23.
1. Open the Cruises! worksheet and select cells D7 through D23. Using Excel’s Data Validation options, define the settings to allow only whole numbers between 7 and 20 to be entered into these cells.
2. For these same cells:
· Enter Valid Data in the Input Message Title and enter Whole numbers between 7 and 20 as the Input Message. Verify that this message will be displayed when any of the cells in this data range are selected.
· Select the Stop style of Error Alert message. Enter Invalid Data as the Error Alert Message Title and enter Value must be a whole number between 7 and 20 as the Error message. Verify that this message will be displayed if an incorrect value is entered in any of the cells in this data range. You can restore the original value that was in the cell by clicking on the Cancel button in the error alert window.
3. Use the following table to enter the data validation rules for the remaining columns in the worksheet.
Cells / Validation Criteria / Input Message Title / InputMessage / Type of Alert / Error Alert Message Title / Error
Message
E7 to E23 / Date after 8/30/2010 / Valid Date / Date after Aug 30, 2010 / Warning / Invalid Date / You did not enter a date after
Aug 30, 2010
H7 to H23 / Decimal value between .05 and .12 / Valid Savings / Savings between .05 and .12 / Information / Invalid Value / You did not enter a savings between .05 and .12
I7 to I23 / Dropdown list only containing Yes, No / Valid Gratuities / Gratuities Included should be Yes or No / Stop / Invalid Selection / You did not select Yes or No
4. Using the “Highlight Cells Rules” selection, conditionally format cells I7:I23 as follows:
a) Cells with Yes should have green fill with dark green text.
b) Cells with No should have light red fill with dark red text.
5. Select the cell range Cruises!C6:I23 and name the range CRUISE_RATES (all caps).
C. Completing a Worksheet by Retrieving Data and Using Conditions
Employees are beginning to sign up for Worm’s Incentive Cruises. You’ve been asked to complete the Cruise_Signup! worksheet that will determine how much to charge each customer for the cruise.
There may be more than one way to do some of the following problems. If multiple functions are needed for any problem, all the functions must be nested in the specified cell. Do not create an extra column to solve these problems.
1. Enter a function in Cruise_Signup!H9 that determines whether each passenger on the roster is an adult or a child. A passenger is considered to be an adult if (s)he is at least ten years of age on the date the FIRST cruise departs from port. This departure date is displayed in Cruise_Signup!D3. Display either Adult or Child in the cell as a result of your calculations. Copy the function down the column.
2. Enter a function in Cruise_Signup!I9 that calculates the Base Cost for the passenger. Find the cost based on the codes provided in the named range on the Cruises! worksheet. If the passenger is an adult, then the base cost is the same as the value stored in the named range. If the passenger is a child, then the base cost is 60% of the adult price. Copy the function down the column.
3. Enter a function in Cruise_Signup!J9 that returns the Early Booking Discount Amount. The discount is assigned as follows: If the individual has an early booking, then the early booking savings (found in the CRUISE_RATES named range) should be multiplied by the Base Cost. Otherwise, zero (0) should appear. Copy the function down the column. (Note: Because of the Accounting format, all 0 values will be displayed as a dash. This is perfectly OK.)
4. Enter the most efficient function in ‘Cruise_Signup’!K9 to calculate the Cost of the Land Package by multiplying the number of days each passenger has requested by the cost per land day. There is no price break for children; each person pays the same amount. Copy the function down the column.
5. In Cruise_Signup!L9, enter a formula that calculates the Total Due. Total Due can be calculated by subtracting the Early Booking Discount from the Base Cost, and then adding the Land Package Cost. From this, subtract the Deposit.
6. The travel agents need to be informed of certain numbers in the signup sheet so that they can make plans. Return the following answers:
a) In Cruise_Signup!D29, enter a function that will return the total number of customers who have not signed up for a land package. This information can be found in column G.
b) In Cruise_Signup!D30, enter a function that will return the total dollar amount of deposits for all customers who have signed up for land packages.
c) In Cruise_Signup!D31, enter a function that will return the percent of the total Amount Due that came from adult customers purchasing land packages.
d) In Cruise_Signup!D32, enter a function that will return the percent of all early bookings that were made for adult customers.
D. Copying Information Across Worksheets
Last year, Worm’s Turn started sending out sale circulars by mail in an attempt to lure people to their new website. They have guaranteed prompt delivery of on-line orders and are excited about seeing what the profits for the past year were based on this new endeavor.
1. Group the worksheets Fall_Sales! and Total_Web_Profits!.
2. Select the entire Fall_Sales! worksheet and use the appropriate commands to fill the formats and contents of this worksheet across to Total_Web_Profits! worksheet.
3. Ungroup the worksheets. View the Total_Web_Profits! worksheet and adjust column widths as necessary so that all cell contents are visible.
E. Summarizing Data Using 3D Cell Addressing
1. Clear the contents of Total_Web_Profits! F5:F20.
2. Using 3D cell addressing, enter a function in Total_Web_Profits F5 that sums the profits for Perennial Shade Plants from the Spring, Summer, and Fall_Sales figures. Copy this function down the range Total_Web_Profits! F6:F20. Cells that contain no dollar values should have their contents cleared and their background changed to light green using the Format Painter. Adjust column widths so that all entries are fully visible (no ####s).
3. Enter the text Average Profit (K) Per Category in cell Total_Web_Profits!G3. Adjust column widths, if necessary. Extend the formatting of column F to this new column range by using the format painter.
4. Using 3D cell addressing and the values on the other sheets, enter a function in Total_Web_Profits! G5 that calculates the average profit for the Spring, Summer, and Fall_Sales figures for Perennial Shade Plants. Copy this function down the range Total_Web_Profits! G6:G20. Enter a function to calculate the sum in G21. Clear the contents of the cells in column G that do not contain a valid number and change the background to light green using the Format Painter. Adjust column widths if necessary.
F. Financial Calculations for Worm’s Turn
In addition to their online presence, Worm’s Turn has decided to expand their brick and mortar presence by opening a new retail location in another city. The new retail store will cost the company $1 million. That type of money is not presently available, so management is looking for other sources to finance the company’s expansion.
Worm’s Turn has $400,000 in cash reserves. Because this amount is not large enough to finance the proposed expansion, management is proposing two alternatives: (1) Continue to save money until the company can afford to build a new facility, or (2) borrow the money and start building immediately.
1. Select the Investment_Loan! Worksheet. For the first alternative, the company would like you to determine the future value of an investment of $30,000 per month for 18 months at the current money market annual interest rate of 5.3% compounded monthly. Remember, Worm’s Turn already has $400,000 in cash reserves and will be building on this amount. Enter a function to calculate the future value of this investment in Investment_Loan!C9. Display the result as a positive number.
2. As an alternate option, calculate how much it will cost Worm’s Turn to borrow the entire $1 million rather than dipping into its cash reserves. A local bank is willing to lend the entire amount at 9.5% annual interest compounded quarterly. The company wants to pay the loan back in 5 years with quarterly payments. Enter a function in Investment_Loan!G8 to determine how much each quarterly payment would be to repay the loan within the specified period. Display the result as a positive number.
3. Your supervisor would also like you to create an amortization table that will show how much of each payment goes toward the principal and the interest. An amortization table has been started for you in Investment_Loan!E11. Complete the amortization table in Investment_Loan!E11:J31 to calculate the payments over the life of the loan.
G. Charts
Worm’s Turn wants you to create a 2-D pie chart based on the results of the total profit you calculated on the Total_Web_Profit! worksheet. Select the appropriate range of data on Total_Web_Profits! to show the total profit for all perennial and annual categories.
1. Store the chart in the Chart! Worksheet. Make sure the chart matches the one below. Additionally, do the following:
2. Title the chart as shown. The font should be light green.
3. If necessary, move and size the legend to the position shown. Make the font bold and 8 pt.
4. Show percentages for each pie slice and format the percentages to appear as close to the ones in the example as possible regarding size and location.
5. Make sure the angle of the first pie slice is 90°.
(Note: Not all changes may appear in this list! Examine the graphic carefully!)
Submitting the Assignment
All assignments are due in your personal CourseWare Dropbox folder on the I: drive on the designated due date. Be sure to create a BACKUP copy of your completed assignment in your personal N: drive just in case there is a problem with the file you have submitted. Keep the following in mind when submitting the assignment:
1. Make sure that the file that you are uploading is named yournetID-MGT20600_A2_S11.xlsm. The file MUST have this name in order to be graded.
2. Make sure that you have SAVED and CLOSED your assignment file before uploading the completed file to your personal CourseWare Dropbox folder. IMPORTANT: Make sure that you double-click on your personal folder within the section’s Dropbox folder to open it before placing your completed assignment in the folder.
3. If after uploading your file, you find that you want to make changes to any of your answers, simply upload the revised file and overwrite the previous file. Again, make sure that the filename of the final submitted assignment is yournetID-MGT20600_A2_S11.xlsm.
1/31/11V 1.2 - MGT20600_A2_S11 Instructions.docxPage 1