Help with Excel Sheet Functions

Anonymous
timer Asked: Oct 1st, 2018
account_balance_wallet $35

Question Description

Looking for help with the below Excel, OPIM course, basics Part 1. Please follow the instructions and please leave the equations viewable so that I can review and practice them.

Unformatted Attachment Preview

Name: NetID: Course Section: 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: Grade 1-Aug-18 LC2 Cinnamon Sticky Bun Pecan Sticky Bun Caramel Sticky Bun Nutella Sticky Bun Ultimate Sticky Bun Ordered Price Per Box Amount Collected School Total 1920 4.5 1830 5 1540 3.75 1941 3.5 1914 4 JW Marriott Old Age Home Beautiful Places and Smiling Faces INPUT AREA Unit Type Cabin Studio Townhome Deluxe Townhome Standard Townhome Red Blue Green Season Red Blue 950 660 850 700 800 680 780 525 3/15 - 8/31 10/2-11/29 9/1-10/1 11/30-12/18 12/19-3/14 Nights Green 700 600 750 650 Red Cabin Blue 1 2 3 4 5 Deluxe Townhome Red Blue Nights 1 2 3 4 5 ge Home ing Faces abin Green Studio Townhome Red Blue Green Nights 1 2 3 4 5 ownhome Green Standard Townhome Red Blue Green Nights 1 2 3 4 5 JW Marriott Old Age Home Beautiful Places and Smiling Faces Panama City, FL Reno, NV Shenandoah Valley, VA Orlando, FL Gatlinburg, TN Pigeon Forge, TN Los Angeles, CA San Diego, CA Chicago, IL Honolulu, HI Miami, FL Total Reservations Least Reservations Most Reservations Average Reservations Reservations Red Blue Green 2300 3590 3487 3266 2864 3187 809 843 760 5720 6112 5608 2185 2633 2113 2444 2695 2503 1829 1530 1411 1900 2184 1832 1700 1273 1170 6222 6106 6632 3187 3299 3000 ! 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. 1 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 worksheet LC3. 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%. 2 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 seasons. 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 other seasons. 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. 3 ...
Purchase answer to see full attachment

Tutor Answer

uoscar
School: Boston College

The solution so far is attached.You would h...

flag Report DMCA
Review

Anonymous
Thanks, good work

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors