CISB11 - Excel Spreadsheet Assignment Solution - Grade One Essays

CISB11 – Excel Spreadsheet Assignment Solution

PURPOSE:

This assignment will continue to build your knowledge of spreadsheet operations by introducing you to some of Excel’s more sophisticated features.

Don't use plagiarized sources. Get Your Custom Essay on
CISB11 – Excel Spreadsheet Assignment Solution
Just from $13/Page
Order Essay

The objectives of this assignment are to:

  • Group worksheets in order to format multiple worksheets at the same time
  • Copy a worksheet
  • Use 3-D references to display the contents of cells on multiple worksheets
  • Use 3-D references to consolidate (summarize) data from multiple worksheets
  • Perform What-If Analysis using Goal Seek
  • Use Help to learn about features available in Excel

Files you will need to download the following files from the Canvas assignment link:

  1. Excel P3 Fun Zone Amusements.xlsx (.xlsx will probably not be visible)
  2. Excel P3 Instructions.pdf (.pdf will probably not be visible)

Files you will submit:

  • You will submit three files through Canvas:
  1. YourLastName Excel P3 Fun Zone Amusements.xlsx –  Excel workbook
    (with Goal Seek values at end of step 16)
  2. YourLastName Excel P3 Summary Formulas  PDF (step 19)
  3. YourLastName Excel P3 Answers  Word document (step 20)
    with the answers to the ten questions on the 2    nd to last page of this document
  • Your score will be based on the quality and completeness of your submission
  • Spelling, grammar, and not following the instructions may result in potential deductions

INSTRUCTIONS: (10 points maximum)

  1. If you are not doing this assignment at Mt. SAC, make sure you are using Excel 2019. Although you can use previous versions of Excel to complete this assignment, some of the assignment instructions will not match your older versions—which may make it difficult to complete the assignment. If you are using Office 365, some instructions may not match because Office 365 is updated frequently.
  • You cannot use versions of Excel before 2007 to do this assignment.
  • If you are doing this assignment in the lab, remember that you need your student ID to be assigned a computer.
  1. If you have not downloaded Excel P3 Fun Zone Amusements.xlsx from Canvas, return to the assignment in Canvas and click the link to download the file. By default, it will be placed in the Downloads folder. Refer to previous assignments for the instructions to download a file from Canvas.
  1. Open Microsoft Excel (Windows 10):

 Use one of the following to locate and open Excel:

  • Check the Taskbar, Start Menu, or Desktop for a Excel icon and then click it.
  • Use windows search: Click the Search icon (hour glass) on the left side of the Taskbar. When you see “Type here to search”, type “excel.” Click on the selection Word Desktop app to open Excel.
  • Use the Start menu: Click the Start button (bottom left corner of the Taskbar). Click the letter A or any visible letter in the list on the Start menu. Click the letter R and then click Excel.

 

  1. Open the Excel P3 Fun Zone Amusements.xlsx file.
  • To open an existing Workbook:
  • If you just opened Excel: Click Open Other Workbooks (green pane, might be near the bottom). Select Open (green pane). Under the large Open heading in the white pane click This PC and then click Browse (or double-click This PC).

OR

  • If Excel was opened previously: Click the File tab. Select Open (green pane). Under the large Open heading in the white pane click This PC and then click Browse (or double-click This PC).
  • On the left side of the Open dialog box, click Downloads (or navigate to the folder you placed the workbook). The Downloads folder is the default location for downloaded files.
  • Click Excel P3 Fun Zone Amusements.xlsx and then click the Open button.
  1. If there is a yellow Protected View message at the top of the window, click Enable Editing.
  2. Use the Save-As command to rename the file and save it to your flash drive.
  • Save the file with the name: YourLastName Excel P3 Fun Zone Amusements–
    replace the words     YourLastName with your own last name
  • Click the File tab in the upper-left corner of the window (see figure above)
  • Click on Save AS
  • Click on Browser, navigate to the folder you wish to save the renamed file.
  • Once the Save As dialog box opens, add your last name to the front of the text in the File name box: YourLastName Excel P3 Fun Zone Amusements
    replace the words YourLastName with your own last name
  • Click the Save button.
  1. Explore the YourLastName Excel P3 Fun Zone Amusements workbook.
  • Fun Zone Amusements is a company that provides amusement rides for traveling fairs and carnivals. Last weekend, there were carnivals in the cities of Chino, Fullerton, and Rosemead. Management collected the information from these carnivals and they would like you to assist them in developing the data so that it provides more robust information for managerial decision making.
  • Click on each of the worksheet tabs at the bottom: Chino, Fullerton, and Rosemead.
  • Each worksheet is may also be called a sheet. An Excel file can contain multiple worksheets. A collection of worksheets is called a workbook. Click on each of each worksheet tab to see the data contained in the sheet.
  • Each worksheet tab may also be called a tab.
  • Notice that each of the worksheets contains the total number of tickets sold and the ticket sales.
  • Sheet and worksheet will be used interchangeably throughout this document
  • Click in cell D8 in any worksheet. With D8 as the active cell, look in the formula bar (directly under the Ribbon) to see the contents of D8. It contains the formula: =$C$5*C8
  • This formula multiplies the value in cell C5 by the value in cell C8. The $ signs in front of C and $ in front of the 5 indicates that this is an absolute cell reference. (You may recall that you learned about absolute cell references in Excel Part 2.) When the formula was copied from cell D8 down to D14, Excel needed to ALWAYS use the value in cell C5 in the formula. Reminder: you only need to be concerned about using absolute cell references if you are going to copy a formula or function and you want to ALWAYS use the same cell reference in the formula.
  • Click in cell C15 or D15.
  • Notice that the SUM function was used to total the values in the column above. The other worksheets are set up using the same information in the same columns and rows.
  1. Group multiple worksheets. (also, see page 11)
  • The workbook you received is very simple. It has the data for each city, but management would like for you to make it look more professional. You will begin by adding the company name to each of the Sales worksheets. You can save a lot of time by grouping the worksheets.
  • Group all of the worksheets in the workbook
  • Click the Chino worksheet tab. Press and hold the Ctrl key and then click the Fullerton worksheet tab followed by the Rosemead worksheet tab. Release the Ctrl key. Each tab should be underlined with a same colored line, indicating that it is part of the group. (Ctrl can be used to select non-adjacent worksheets as well.)
    Alternative method using the     Shift key: Click the Chino tab (the first tab you want in the group). Press and hold the Shift key and then click the Rosemead tab (the last tab you want in the group). This works when you want to group a series of worksheet tabs next to each other.
    Also, a method for grouping all worksheets in a workbook is to right-click any sheet tab and then click     Select All Sheets.)
  • The Excel title bar indicates when worksheets have been grouped by displaying the word [Group].

 

  1. Format multiple worksheets at the same time and ungroup the worksheets.
  • When worksheets are grouped, you can perform calculations or make formatting changes that will affect all of the grouped worksheets.
  • With your worksheets still grouped, click in cell A1 and type, Fun Zone Amusements. Press Enter.
  • Format the title in A1: Click in cell A1. Change the font size to 18 and bold the title.
  • Ungroup the worksheets by right-clicking on any worksheet tab in the group and selecting Ungroup Sheets.
  • Another option for ungrouping worksheets is to click any tab in the workbook that is not a part of the group or clicking on any tab other than the first tab you clicked in the group.
  • Look at each of your worksheets and verify that cell A1 contains the text Fun Zone Amusements.
  1. Group the worksheets and add more formatting.
  • Group all of the worksheets (refer to step 5 if you need help)
  • Format ride names: Select the range B8 through B14. Apply bold and italic (click the B (bold) and the I (Italic) buttons).
  • Ungroup the worksheets.
  1. Copy a worksheet and use it to consolidate (summarize) the values from the city worksheets.
  • The summary worksheet that you are about to create will look identical to the other worksheets. The only difference is that you will create formulas that will consolidate (summarize) the data from the other worksheets into one page.
  • Copy the Chino worksheet tab (sheet). Name the new worksheet Summary.
    The new worksheet must be the 1    st worksheet in the workbook.
  • Right-click on the Chino tab and select the option, Move or copy….
  • Click the Create a copy option. Because Chino is selected, the copy will appear before the Chino worksheet. Review the dialog box shown to the right. Press the OK button.
  • The worksheet that you just created appears as the first worksheet in your workbook and the tab displays,
    Chino (2).
  • NOTE: If your new worksheet does not appear first in your workbook, you can easily move a worksheet by using a drag and drop technique. As you drag a worksheet tab, a small black arrow appears above the tabs and indicates where the worksheet will be relocated.
  • Change the worksheet tab’s name to Summary.
    Right-click on the     Chino (2) worksheet tab and select Rename. Once Chino (2) is highlighted, type the new name: Summary.
    Alternative method for renaming a worksheet tab is to double-click the worksheet tab and type a new name.
  1. Edit the contents of the Summary sheet
  • Save the workbook.
  • Change the word, Chino, in cell B3 to Summary.
  • Clear the contents of cells C8:D14 on the Summary sheet.
  • Select C8:D14. Right-click in the selected range and select Clear Contents in the short-cut menu. Note: If you click the left mouse button, the range will no longer be selected and you must repeat this step. Your summary worksheet should match the example shown to the right.
  1. Use a 3-D reference to display a value from one worksheet on other worksheets.
  • The person who originally entered the data into the Fun Zone Amusements workbook thought that it was a good idea to put the ticket price on each workbook. You can find it in cell C5 of each city worksheet. Management now feels that this is very inefficient. If the ticket price changes, it should be changed in one cell and all of the other cells that use it should recalculate automatically. They would like you to display the ticket price on the summary sheet so that when the ticket price changes, only the summary sheet will need to be changed. The following instructions will guide you in the use of 3-D references.
  • FOLLOW THESE INSTRUCTIONS TO USE 3-D REFERENCES TO DISPLAY THE TICKET PRICE ON THE CITY WORKSHEETS. DO NOT TYPE THE TICKET PRICE IN EACH WORKSHEET.
  • To save time, group the city worksheets Chino, Fullerton, and Rosemead.
    THE     SUMMARY SHEET IS NOT PART OF THE GROUP! (Review step 5)
  • Delete the ticket price displayed in cell C5 in all three city worksheets.
  • Click in cell C5, in any of the city worksheets. Press the Delete key.
    The ticket prices disappear from the Chino, Rosemead, and Fullerton Worksheets and the Ticket Sales values become zero.
  • Use a 3-D reference to display (reference) the ticket price in cell C5 of the Summary worksheet in each city worksheet. The city worksheets won’t contain the actual number – they will only refer to cell C5 on the Summary sheet. That way if the ticket price changes on the Summary sheet the amount displayed in C5 in all of the city worksheets will update automatically.
  • If C5 is no longer selected, select cell C5 in a city worksheet (the city sheets must be grouped). Type an equal sign (=) to start the formula. Select the Summary tab and then click in cell C5. A green marquee starts moving around the edge of cell C5 on the Summary worksheet. Press Enter.

 

  • Ungroup the worksheets (refer to step 6 if you need help)
  • Click in C5 in one of the city worksheets. The ticket price is displayed in the cell and the formula bar contains =Summary!C5.
  • Examine the other two city worksheets. You will notice that C5 in each city worksheet is referencing the value of C5 in the Summary sheet.
  • Save the workbook.
  1. Use 3-D references to calculate the total sales for all of the cities.
  • Add the values from cell C8 in the Chino, Fullerton, and Rosemead worksheets and display the total in this cell. We will use the Sum function to add these values together.
  • The following instructions guide you in selecting all of the city worksheets and choosing the cell that contains each city’s # Tickets Sold for the Aerial Tram.
    Suggestion: Read through the next three bullets before you begin.
  • Click in cell C8 in the Summary worksheet. Begin a SUM function as you normally would by typing =SUM( NOTE: You need to type the left parenthesis after the word SUM.
  • Click on the Chino tab, hold the Shift key and click on the Rosemead tab. Click in cell C8 and press Enter.
  • You are now back in the Summary worksheet and cell C8 is the active cell. The formula bar should display: =SUM(Chino:Rosemead!C8)
    • NOTE: Do not be concerned if your formula displays =SUM(‘Chino:Rosemead’!C8).
      This formula adds the values in cell C8 from the Chino worksheet to the Fullerton and to the Rosemead worksheets.
  • Use AutoFill to copy the formula in cell C8 down to cell C14.
  • (You may recall that you learned about AutoFill in Excel Part 1).
    To use AutoFill, make sure that C8 is the active cell. Point the cursor at the small square (called the AutoFill handle) in the lower right-hand corner of the cell. You will know when the cursor is positioned correctly because the cursor indicator will turn into a black thin-lined plus sign. Click and Hold the mouse button down and move the mouse straight down to cell C14. Release the button. The     Total for the # Tickets Sold cell, C15, will display 13,903.
  • Using the skills that you just completed, repeat the steps above and calculate the total ticket sales in cell D8 on the Summary worksheet.
    ** Use 3D references to calculate the total ticket sales for each ride. **
  • Copy the formula down to D14. The Total for the Total Sales cell, D15, will display $48,660.50.
  • Select the range C14 and D14 and then fix the bottom border.
  • In the Font group on the Home tab, click the drop-down arrow in the Borders button and select Bottom Border.
  • Save the workbook.
  1. Perform What-If Analysis
  • Management has agreed to contribute 10% of ticket sales to charity when total ticket sales reach $50,000. With the current ticket price at $3.50, last week’s ticket sales totaled $48,660.50.
  • What did the ticket price need to be in order to reach $50,000 for last week’s ticket sales?
  • It would be easy to change the ticket price in C5 by typing in a new number and then watching the amount in D15 to see how it gets closer to the goal.
  • On the Summary sheet, change the ticket price in C5 to $4.00. Notice that D15 is recalculated automatically and the total sales now show as $55,612.00. That amount is too high.
  • Try entering $3.75 in cell C5. The result is $52,136.25.
  • That’s still too high. You could keep using trial and error to find the ticket price that achieves the goal of $50,000, but there is a better way. It is a feature in Excel called Goal Seek.
  1. Perform What-If Analysis using Goal Seek (see page 13)
  • We will use Excel’s Goal Seek feature to find the exact ticket price needed in order to have total sales of $50,000.
  • Cell C5 in the Summary sheet is considered an input cell. Input cells contain values rather than formulas.
  • D15 is a dependent cell. Dependent cells typically contain formulas that use the values in input cells in their calculations. Dependent cells are the basis of what-if analysis. In other words, what will be the result of the calculation be if the input cell value (or values) changes?
  • Click in cell D15 on the Summary worksheet.
  • Select the Data tab and in the Data Tools group, click on What-If Analysis and click Goal Seek….
  • The Goal Seek dialog box opens and D15 appears in the Set cell: textbox
    (because D15 was the active cell when you clicked the What-If Analysis button on the Ribbon).
  • Set cell identifies the cell that is used for setting the goal. The cell selected for Set cell must be a dependent cell and must contain a formula or function. The =SUM(D8:D14) formula for total sales is in cell D15.
  • Type: $50,000 in the To value: box. To value is the amount of our goal, which is $50,000.
  • Type: C5 in the By changing cell: textbox.

By changing cell is the cell that will change to give us the goal amount we are looking to achieve. This is the ticket price in C5. If C5 displays as $C$5, that is OK.

  • The Goal Seek dialog box reads like this: Set cell D15 to a value of $50,000 by changing cell C5.
  • Click the OK button.
  • The Goal Seek Status window now appears. It lets you know that it found a solution (or not!).
  • Click OK.
  • Look in cell C5, the ticket price has been changed to $3.60. This is the ticket price for $50,000 in total sales.
  1. Include your name in all of the worksheets
  • Group ALL of the worksheets.
  • In cell C3 type Prepared by: YourName (using your name instead of the words, YourName).
  • After entering your name Ungroup the worksheets.
  • Save the workbook.
  1. Review Print options
  • Select the File tab and then select Print. Click on the down arrow within the Settings area of the Print dialog box.
  • Excel provides various printing options:
  • Print Active Sheets – prints the worksheet that is currently displayed
  • Print Entire Workbook – prints all of the worksheets within the workbook
  • Print Selection – prints only the part of the worksheet that has been highlighted.

 

  • Close Print: Click the button (upper left corner) or press the Esc key.
  1. Create a PDF of the Summary worksheet with the formulas displayed using Export.

q Display the formulas on the Summary worksheet. Remember to select the Summary worksheet.

  • To display formulas, simultaneously press the “Ctrl” and “`” keys on the keyboard. The ` key should be at the upper left corner of your keyboard to the left of the 1 key. The same key includes ~.
  • OR go to the Formulas tab on the Ribbon and choose Show Formulas in the Formula Auditing group.

q In the Summary worksheet, adjust the column widths so that ALL of the formulas display completely in column C and D. (Pay special attention to column D and make sure it is wide enough for the formulas to display!)

  • Do not be concerned if the contents of A1 or column B are not fully displayed.

q Select the File tab and then select Print. A preview of what will be printed is shown on the right side of the page.

  • Notice that the total sales column is on page 2. You will use Landscape orientation to fix this.

q Change the page orientation by selecting the down arrow to the right of Portrait Orientation and selecting Landscape Orientation.

q The result must fit on 1 landscape page!
If it NOT 1 page, click the drop down arrow to the left of No Scaling and select Fit Sheet on One Page

q Click Export in the green navigation pane (left side)

q Click the Create PDF/XPS Document (shaded green under the large font word Export)
If Adobe Acrobat is installed, Create Adobe PDF may also be used. It will be installed at Mt. SAC.

q Click the Create PDF/XPS button (in the white area)
OR click the Create Adobe PDF button (in the white area).

q Save the PDF file with the following name: YourLastName Summary Formulas

q Redisplay the values in the worksheet. You can use “Ctrl” and “`”. You will need to adjust the column C and D widths manually so the data fits into each column and does not spill over to column E. Do NOT make column C too wide. The text “Prepared by: YourName” should fit in cells C3 and D2.

  • To manually size a column: Hover between the column heads C and D until you see the black plus with arrow heads on the horizontal leg. Click and Hold then drag to the right (or left) to size the column as needed. Repeat for the column D.

q SAVE the work but leave the Excel file open so that you can use it when needed to answer the questions on the next page.

  • IF you change values after this step, do NOT save!
  1. Answer the ten (10) questions

q Open a Word document and name it YourLastName Excel P3 Answers

 Leave one blank line between each answer.

  • Please do not retype any part of the question. Simply type the question number and the correct answer followed by a blank line.
  1. What key on the keyboard is used to group multiple worksheets
    one at a time (not adjacent to each other)?
  2. True or False When you group worksheets, you can change the formatting for multiple sheets all at the same time.
  3. True or False The formula =C5*C8 produces the same results as =$C$5*C8 when the formula is copied to other cells.
  4. True or False An input cell contains a value.
  5. What is the name of the number format that displays dollars signs on a value when you press the $ button from the Number group on the Home tab of the Ribbon?
  6. Type the formula that is in cell C5 in the Rosemead worksheet.
  7. What are the total sales in the Summary sheet if the ticket price is $3.00?
  8. What are the total sales in the Summary sheet if the ticket price is $5.00?
  9. What would the ticket price need to be to produce total sales of $60,000.00?
  10. What would the ticket price need to be to produce total sales of $25,000.00?
  • If you change the value of the ticket price as you answer the questions,
    REMEMBER DO NOT SAVE!
    The file you submit to the instructor should have the ticket price from the Goal Seeking step 16
  1. Wrap Up

 Select File tab, then select Close (to close the document) and then Exit (to close Excel).

 If you used a flash drive for this assignment, make sure you remove your flash drive before leaving.

  1. Close Excel by clicking on the Close button at the top right corner of the Window.
  2. Remove your flash drive before leaving.

 

  1. Use Excel Help to learn about grouping worksheets

q When you are working in Excel you can press the F1 key (at the top left of your keyboard) to view Excel’s Help files.
or click on the Help tab above the ribbon
or click the Search textbox and type help to open the Help dialog box.

q When the Help dialog box opens, enter the topic that you want to learn about in the textbox near the top of the window.
Type group worksheets in the textbox and press the Enter key

q Select the topic, Enter data in multiple worksheets at the same time. You may need to click Show More and scroll down the results list to find this entry.
Read this topic and note that when you group worksheets, the changes that you make in one worksheet will be changed in the other worksheets in the group.
Close the Help window.

  1. Use Excel Help to learn about Goal Seek

q Press the F1 key (at the top left of your keyboard) to view Excel’s Help files. Enter goal seek in the textbox near the top of the window and press the Enter key. Select Use Goal Seek to find the result you want by adjusting an input value from the list of topics.

Rubric

Name of Excel file = YourLastName Excel P3 Fun Zone Amusements -1/4

Name of PDF file = YourLastName Excel P3 Summary Formulas -1/4

Name of Word answer file = YourLastName Excel P3 Answers -1/4

 

s.6 A1 Fun Zone Amusements on all sheets -1/4

s.6 A1 fonts size = 18 pt, B -1/4 ea

s.7 B8:B14 = Bold, Italic -1/4 ea

s.8 Copy Chino sheet to new sheet -1/2

s.8 New sheet name = Summary -1/4

s.9 B3 = Summary -1/4

s.9 C8:D14 clear contents -1/4

s.9 C5 on each city sheet: Summary -1/4 ea

s.10 C5 on city sheets use 3-D reference to Summary sheet -1/2

s.11 Summary sheet C8: SUM city sheets -1

s.11 Summary sheet C8:C14 fill down -1/4

s.11 Summary sheet D8: SUM city sheets -1

s.11 Summary sheet D8:D14 fill down -1/4

s.11 Replace bottom border on C14:D14 -1/4

s.13 Goal Seek ticket price -1

s.14 C3: Prepared by: StudentName -1/4

s.16 Summary sheet, Display formulas -1/2

s.16 Summary sheet, Display landscape -1/4

s.16 Summary sheet, Display create PDF -1/2

s.16 Adjust column widths to display contents in column C and D -1/4

s.16 Turn off formula view -1/4 ea

s.16 Adjust column widths to display in Column C and D -1/4 ea

s 16 Saved workbook should have goal seeking values from step 13 -1/2

s.17 Questions 1/4 ea

Order NOW to get 15% Discount!

Pages
0.00

Why Choose Us

Quality Papers

We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.

Professional Academic Writers

Over the years, our Acme Homework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.

Affordable Prices

Our prices are fairly priced in such a way that ensures affordability. Additionally, you can get a free price quotation by clicking on the "Place Order" button.

On-Time delivery

We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.

100% Originality

At Grade One Essays, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.

Customer Support 24/7

Our support team is readily available to provide any guidance/help on our platform at any time of the day/night. Feel free to contact us via the Chat window or support email: support@gradeoneessays.com.

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Grade One Essays has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.

Essays

Essay Writing Services

At Grade One Essays, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.

Admissions

Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.

Editing

Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.

Coursework

Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.

See all services