Before beginning this assignment you should save this file as HW1_
All answers should be marked in the appropriate place inside this file.
As you work, save your work frequently.
When complete, upload the file to Canvas in the HW 1
u should save this file as HW1_yournetID.xlsx where you replace yournetID with your actual one (e.g., nso6).
appropriate place inside this file.
anvas in the HW 1 upload.
Start of Sale:
Cinnamon Sticky Bun
Pecan Sticky Bun
Caramel Sticky Bun
Nutella Sticky Bun
Ultimate Sticky Bun
Ordered Price Per Box Amount Collected School Total
JW Marriott Old Age Home
Beautiful Places and Smiling Faces
3/15 - 8/31 10/2-11/29
JW Marriott Old Age Home
Beautiful Places and Smiling Faces
Panama City, FL
Shenandoah Valley, VA
Pigeon Forge, TN
Los Angeles, CA
San Diego, CA
OPIM 170 - Homework 1 –Fall 2018
This homework must be submitted on September 28th HW1 files have to be uploaded to
Canvas no later than 10:00 pm. Be sure to download the provided Excel template
(HW1_F2017.xlsx), rename the file HW1_yournetID.xlsx (e.g., HW1_nso6.xlsx), and
complete the cover Sheet tab (very important for grading purposes) and all your
answers in the renamed Excel file.
Georgetown High School Fundraiser
Your daughter attends Washington Lee High School in Arlington, VA. You chair the annual Jack
Bulldog fundraiser. Your job is to track the boxes of sticky buns sold per grade, the amount of
money to be sent to the distributor, and the amount of money the school earned. Your co-chair
created a spreadsheet with the initial order information from the two-year-old classes. You need
to insert formulas to calculate missing values and format the worksheet. In addition, you need to
insert another worksheet for the three-year-old classes. You will be submitting this report to the
Vice Principal at the end of the fundraiser.
1. Open the LC2 data tab.
2. Type “Jack Bulldog Annual Fundraiser” in cell A1. Merge and center the title from cells A1
to F1. Apply Blue Accent 1, Darker 50% font color, bold the title and select Times New
Roman font, 16 pt size.
3. Delete rows 5 and 6 and insert a new row between “Start of Sale:” and “Grade:” Enter “End
of Sale:” in cell A4. Type “8/31/2018” in cell B4, then, if necessary, left-align the date.
Change date format to DD-MMM-YY.
4. From row 2 to row 20, adjust the row height to 20.
5. Select row 7, increase row height to 30.
6. Insert a new column between “Amount Collected” and “School Total.” Enter “Distributor
Total” in cell E7.
7. Enter “Totals:” in cell A13.
8. Enter the Amount Collected formula for the Cinnamon Bun row. The amount collected is the
number of boxes ordered for that type of bun multiplied by the price for that box. Copy that
formula down the column for the other four bun types using the Fill Handle.
9. Enter the Distributor Total formula for the Cinnamon Bun row. The distributor earns 70%
from the amount collected. Copy that formula down the column for the other four bun types.
10. Enter the School Total formula for the Cinnamon Sticky Bun row. The school gets to keep
whatever is left from the amount collected after taking out the distributor total. Copy that
formula down the column for the other four bun types.
11. Enter the Totals formula from the Amount Collected column in cell D13 by adding the
amount collected for all five bun types. Copy that formula across the rows for the
Distributor Total and School Total columns using the Fill Handle.
12. Select cells A7:F7. Apply the following formats: Wrap Text, Bold, Center-alignment both
vertical and horizontal, use Green, Accent 6, Darker 50% font color, and Green, Accent 6,
darker 40% fill color.
13. Select cells A8:A12 and apply Bold format.
14. Apply Comma Style with no decimal places to the values in the Ordered column.
15. Apply Currency Number Format with two decimal places to all monetary values.
16. Select the range A7:F7 and apply the Top and Bottom Border style.
17. Select the range A13:F13 and apply the Top and Double Bottom Border style.
18. Copy the LC2 worksheet and place it to the right of the worksheet. Rename the copied
19. Change the LC2 sheet tab to Light Green and the LC3 sheet tab to Green.
20. Change the following data in the LC3 worksheet: change Grade to LC3, change Cinnamon
Bun Ordered value to 2100, the Pecan Bun Ordered value to 2400, the Caramel Bun
Ordered value to 1777, the Nutella Bun Ordered value to 1950, and the Ultimate Bun
Ordered value to 1299.
21. Copy the LC3 worksheet and move to the right. Rename the copied worksheet Formulas.
Change the Formulas sheet tab to Blue.
22. Display the cell formulas for the Formulas worksheet.
23. In tabs LC2 and LC3, select all cells you will share with the Vice Principal and select print
area. Print preview your report to make sure it includes all info you would like to share.
Old Age Home
You are working with JW Marriott Old Age Home, which is a points-based time-sharing
corporation. Old Age Home owners can use available points to book a vacation at any of a
number of resorts throughout the nation. The number of points required for a booking is
dependent on the season in which the reservation occurs (red is the most popular, followed by
blue, and then green), as well as the type of accommodation requested. To simplify the task of
determining required points, you will develop a worksheet that calculates the number of
points required, given the accommodation type, season, and number of nights. For statistical
purposes, you will also modify a worksheet to calculate the total number of reservations that
were made for each of the resorts during the past year. In addition, you will calculate other
statistical data, including the average number of reservations, the least number, and the
greatest number of reservations. Open the points tab and:
1. Click cell G8, the cell to display the number of points required for a one-night cabin stay during
the red season. Create a formula that multiplies the number of nights (cell F8) by the points
required for a cabin in the red season (cell B9). Be sure to include cell B9 as an absolute
reference so that as you copy the formula, the cell reference does not adjust. Copy the formula
down the column.
2. Click cell H8, the cell to display the number of points required for a one-night cabin stay during
the blue season. Create a formula that multiplies the number of nights (cell F8) by the points
required for a cabin in the blue season (cell C9). Cell C9 should be included as an absolute
reference. Copy the formula down the column.
3. Create and copy formulas to determine the points required for all accommodations and nights
shown on the Points worksheet. Be sure to include absolute references where appropriate.
4. Select range A6:D6 and merge and center the cells and apply bold formatting.
5. Select ranges F7:I7 and F16:I16 and align those cells to the right and apply bold formatting.
6. Select the “Unit Type”, “Season”, “Cabin” and “Deluxe Townhomes” titles and apply bold
formatting and change the font color to Gray-50%, Accent 5, Darker 50%.
7. Click the Reservations sheet tab. Format the range B5:D19 as Comma Style. Ensure that
the number of decimal places is 0.
8. Click cell B16. Use a SUM function to determine the total number of reservations made in the
Red season. Copy the formula across the row to show totals for other seasons.
9. Click cell B17. Use a MIN function to determine the least number of reservations made in the
Red season. Copy the formula across the row to show the least number in other seasons.
10. Click cell B18. Use a MAX function to determine the greatest number of reservations made
in the Red season. Copy the formula across the row to show the greatest number in other
11. Click cell B19. Use an AVERAGE function to determine the average number of reservations
made in the Red season. Copy the formula across the row to show the average number in
12. Click cell E5. If a resort exceeds 3,000 reservations in the Red season, it is designated a “Star
Home.” Use an IF function to determine whether a resort is a Star Home. If reservations in
the Red season are greater than 3000, the words Star Home should be placed in cell E5.
Otherwise, the word “Premier” should be displayed. Copy the IF function down the column
through cell E15.
13. Adjust the width of column E to accommodate all entries. Change the font color of all text in
column E to Blue, Accent 1, Darker 50% and apply bold
14. Click the Points worksheet tab. Create a footer with your name and last name on the left
side, the sheet name code in the center, and the file name code on the right.
15. Select A1:N22 and set is as the print area and change the orientation to landscape.
16. Print selected area as PDF and make sure you upload this pdf file as part of your assignment.
17. Save the workbook and submit based on your instructor’s directions.
Purchase answer to see full