MGT 20600 SPRING 2011 / University of Notre Dame / Assignment 1
Due Date / Check Concourse for the assignment due date.
Total Points / 10

Objectives

/ In this Assignment you will demonstrate proficiency in the following:
  • Formatting cells, borders, columns, and text
  • Functions (DATEDIF, COUNT, COUNTA,SUM, MIN, MAX, AVERAGE, INT, TODAY, and ROUND)
  • Arithmetic formulas and cell addressing
  • Copying, deleting, moving, and pasting
  • Page setup and print area

To Complete The Assignment:

1)Copy the MGT20600_A1_S11.xlsm and worms_turn.jpg files from your section’s Assignment folder on the Courseware I: drive to your computer’s desktop.

1)NEVER open or work on any files directly through WebFile.

2)Always enable macros.

3)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.

4)If you want to view your progress at any time while you are working on your file, click on the Grade My Work button on the Grading Feedback! worksheet. It will run the grading module and show you your progress. You may do this step as often as you wish. This feature will be available to you ONLY on Assignment 1.

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.

Worm’s Turn Nurseries and Landscaping Incorporated is the brainchild of two free spirits who love nature but still wanted to be in business. The company was incorporated five years ago and sells a variety of live plants, seeds, and supplies. Sales are pretty good, especially in the spring and summer.

Worm’s Turn has begun managing information about their corporate sales in an Excel workbook. Because of your understanding of Excel, you’ve been asked to clean up the workbook and perform necessary calculations for some financial reports. Open the file and let’s get to work!

A. Formatting a Documentation Worksheet

  1. Before opening your file, rename your MGT20600_A1_S11.xlsm file to
    netID-MGT20600_A1_S11.xlsm where the prefix is your NetID.
    (Example: littlefi-MGT20600_A1_S11.xlsm.)
  1. Open your netID-MGT20600_A1_S11.xlsmworkbook file. Be sure to enter your correct NetID (eg.., littlefi). Enter your section number when prompted. You will not be asked to enter your NetID and section number when you open the file in the future.
  1. Select the Documentation!worksheet tab. Merge and center the title in cell Documentation!A1across the range A1:J1.
  1. Format the title in cell Documentation!A1:

a)Enlarge the font to 22 pt, and make the title bold.

b)Change the font color to ‘Green’.

c)Using the Borders dropdown arrow in the Font Group on the Home tab, place a thick box border around the title.

d)Select ‘Olive Green, Accent 3, Lighter 80%’ for the background fill color.

  1. Clear the formatting of cell Documentation!D2. (Hint: Use the Clear...Formats command from the Editing Group to complete this step.)
  1. In cell Documentation!C4, enter your full name. Adjust the column width to accommodate your entire name.
  1. Enter your NetID (e.g..,littlefi) in Documentation!C5.
  1. Type the date you’re starting your assignment in Documentation!C6 and format the date so that it appears in thisformat: March 14, 2001. Widen the column to display the full date if necessary.
  1. Enter your instructor’s name in Documentation!G4. Adjust the column width to accommodate the instructor’s name.
  1. Enter your section number in Documentation!G5.
  1. Delete row 7 and all its contents. This row is not necessary.
  1. Use Page Setupin Print Preview to create a custom footer. The footer will display your email address (eg..,) in the lower left corner, the page number in the center, and the current date in the lower right corner. You do not have to print out this page.
  1. Use Print Area under the Page Layout ribbon tab menu to designate the print area to be cells A1 to J16.
  1. After you set your print area, use Print Preview to check your selected cells (and the page footer.) Change the page orientation to Landscape so that all of the title can be seen. Once again you are not required to print out this page.
  1. Insert the Worm’s Turn picture graphic (worms_turn.jpg) and resize it to fit in cell A1 of Documentation! Worksheet. Copy the graphic to all other worksheets in the workbook except for the grading feedback sheet. The graphic should appear in the upper left-hand corner (cell A1) of these worksheets.
  1. As a final formatting change, the Worm’s Turn owners want you to make all the sheets look consistent. Using the Format Painter feature, take the formatting that you did for the heading on the Documentation! worksheet and apply the format to all the headings in the workbook. Make column width adjustments as necessary so that all text can be read, clean up any stray formatting that may be there, and restore any borders that have disappeared.

B. Calculating Annual Sales Figures

Worm’s Turn carries over 2000 different bug and weed killers for their customers. The annual sales for their best-selling poisons are listed on the Pests Worksheet. Complete this Pest Control Analysis worksheet by performing the following steps:

  1. Enter a function in cell Pests!G2 that returns today’s date.
  1. Enter a function in Pests!B19 that returns the Total Sales Revenue for Quarter 1. Copy this function across the row to display sales for all remaining Quarters.
  1. In Pests!F5, enter a function that returns the Total Annual Revenue by Product. Copy this function down the range Pests!F5:F18.
  1. In Pests!F19, enter a function that will return the grand Total Annual Revenue by Product.
  1. In Pests!G5, enter a formula that calculates the percentage of total annual revenue byProduct. Format as percent with two decimal places. Copy this formula down the column and format Pest!G18 so that there is bottomdouble border at the bottom of the cell.
  1. Format all revenue values, as follows: currency, two decimal places, negative numbers in red and parentheses ($1,234.10). Adjust column widths so that all entries are visible (no ####).
  1. In Pests!B22, enter a function that calculates the Average Quarterly Revenue for Quarter 1. Copy this function across the row for all Quarters.
  1. Enter a function in Pests!B23 that returns the Minimum Quarterly Revenue for Quarter 1. Copy this function across the row for all Quarters.
  1. Enter a function in Pests!B24 that returns the Maximum Quarterly Revenue for Quarter 1. Copy this formula across the row for all Quarters.
  1. Enter a function in Pests!D26 that returns the total number of pest and weed control items on this worksheet.

C. Completing a Forecast Analysis

Worm’s Turn marked the prices down on many of their plants last year with the expectations of generating some extra profits before all the plants died. The Plant_Sale!worksheet contains actual sales and forecasted sales for each type of plant. Complete the worksheet to determine if Worm’s Turn generated more or less sales dollars than they projected.

  1. In Plant_Sale!G5, enter appropriate functions that will return Number of Weeks on Sale. Use the appropriate function so that whole weeks (no rounding) are displayed. Copy the function down the column.
  1. In Plant_Sale!J5, enter a formula that calculates the Profit Per Plant by dividing the Total Sales Profits by the Plants Sold. Copy this formula down the column. Format your results as currency with two decimal places with negative numbers shown as -$1,234.10.
  1. In Plant_Sale!L5, enter a function and a formula that calculates Amount Over or Under Forecast by subtracting Forecasted Profits from Total Sales Profits.Round the difference to the nearest dollar and copy the formula down the column. Format your results as currency with two decimal places where negative numbers are displayed in red within parentheses.
  1. In Plant_Sale!C28, enter a function to determine the number of different plant types that were sold in containers (not flats) last year.
  1. In Plant_Sale!C29, enter a function to determine the total number of different plant types that were placed on sale.
  1. In Plant_Sale!C30, enter a function or formula to determine the total number of seedling flats that were placed on sale last year.

D. Mixed Cell Addressing

The owners of Worm’s Turn want you to create a Sales Markup Reference Guide for their top ten selling plants. This Guide will provide a quick reference to lookup the markup dollars generated for each plant and for sales on one or more of these plants.

  1. Select the Markup!worksheet. Enter a formula in Markup!D7 that will calculate the sales markup amount on one 1-Gallon Hosta. This will be the product of the standard markup rate, the container price, and the number of plants. Write this formula so that it can be copied down the columns and across the rows for all cells in the table. Widen all columns so that all contents can be seen and all ### symbols are removed. You may also want to zoom to 90% so that all the cells can be seen on one screen.
  2. Save and close your assignment file. Click OK when you are prompted that you will only receive feedback on Assignment 1. Reply ”Yes” when prompted if you want to save the changes that you’ve made.

Getting your feedback
When you have finished your assignment, or if you just want to view your progress so far, click on the Grade My Work button on the Grading Feedback! worksheet. It will run the grading module and show you your progress. You may do this step as often as you wish.
Submitting the Assignment

All assignments are due in your personal CourseWareDropbox 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_A1_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 CourseWareDropbox 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_A1_S11.xlsm.

1/17/11V 1.2 - MGT20600_A1_S11.docxPage 1