How to work on a spreed sheet document using the information given

 

Independent Project 2-4

Windows   Mac

Central Sierra Insurance (CSI) sets bonus percentages based on commissions earned by each agent and calculates totals by branch office. This workbook also tracks fundraising efforts of employees for a community event.

[Student Learning Outcomes 2.1, 2.2, 2.3, 2.5, 2.6, 2.7]

File Needed: CentralSierra-02.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-CentralSierra-02.xlsx

Skills Covered in This Project

  • Create, copy, and edit formulas.
  • Name cell ranges.
  • Set mathematical order of operations.
  • Set cell references to be absolute.
  • Use the NOW function.
  • Use HLOOKUP and VLOOKUP
  • Use the SUMIF function.
  • Build an IF function.
  1. Open the start file CentralSierra-02.xlsx workbook. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
  2. Select the Tables sheet, select cells A6:B10, and create range names using the Create from Selection button [Formulas tab, Defined Names group].
  3. Select cells B1:F2 and click the Name box. Name the selection H_Rates. Note that the first row is arranged in ascending order.
  4. Create an HLOOKUP function to display the bonus rate.
    1. Click the Commissions sheet tab and select cell F5.
    2. Start the HLOOKUP function and use cell E5 as the lookup_value.
    3. For the table_array argument, use the H_Rates range.
    4. Use the second row for the row_index_num argument (Figure 2-100).The formula is =HLOOKUP(E5,H_Rates,2)Figure 2-100 HLOOKUP function to display bonus
  5. Format the results as Percent Style with two decimal places.
  6. Copy the formula in cell F5 to cells F6:F14.
  7. Set order of operations to calculate total earnings.
    1. Select cell G5.
    2. Build a formula to add the commissions amount (E5) to the commissions amount times the rate (F5*E5).
    3. Copy the formula in cell G5 to cells G6:G14.
  8. Create and copy a SUMIF function to calculate total earnings by branch office.
    1. Select cell E18.
    2. Start the SUMIF function with cells $D$5:$D$14 as the Range argument.
    3. Set the Criteria argument as a relative reference to cell C18.
    4. Select cells G5:G14 for the Sum_range argument and make the references absolute.
    5. Copy the formula in cell E18 to cells E19:E20 without formatting to preserve borders.
    6. Format cells E18:E21 as Currency.
  9. Total the earnings in cell E21.
  10. Create and format the current date.
    1. Select cell G23 and insert the NOW function.
    2. Select cell G23 and click the Number group launcher [Home tab]. On the Number tab, select the Date category.
    3. Scroll the Type list to find the date that displays the month spelled out, the date, a comma, and a four-digit year (Figure 2-101).The Date category in the Format Cells dialog boxFigure 2-101 Date format selected
    4. Click OK. Press Ctrl+Home.
  11. Create and copy a VLOOKUP function to display goals for each funding source.
    1. Click the Family Day sheet tab and select cell F6.
    2. Start the VLOOKUP function and use cell E6 as the lookup_value.
    3. Click the Tables sheet tab for the table_array argument and use cells $A$6:$B$10. The data is sorted by the first column in ascending order.
    4. Use the second column as the col_index_num The range_lookup argument is empty.
    5. Copy the formula in cell F6 to cells F7:F20 without formatting to preserve the fill color.
    6. Format cells F6:F20 as Currency with no decimal places.
  12. Create and copy an IF function.
    1. Select cell H6 and start an IF
    2. Type a logical_argument to determine if cell G6 is greater than or equal to (>=) cell F6.
    3. Type Yes as the Value_if_true argument and No as the Value_if_false argument.
    4. Copy the formula in cell H6 to cells H7:H20 without formatting to maintain the fill color.
    5. Center align cells H6:H20.
    6. Press Ctrl+Home.
  13. Insert a new sheet at the end of the tab names and paste the range names starting in cell A1. AutoFit columns A:B and name the worksheet as Range Names.
  14. Save and close the workbook (Figure 2-102).Excel 2-4 completedFigure 2-102 Excel 2-4 completed
  15. Upload and save your project file.
  16. Submit project for grading.
Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

We value our customers and so we ensure that what we do is 100% original..
With us you are guaranteed of quality work done by our qualified experts.Your information and everything that you do with us is kept completely confidential.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

The Product ordered is guaranteed to be original. Orders are checked by the most advanced anti-plagiarism software in the market to assure that the Product is 100% original. The Company has a zero tolerance policy for plagiarism.

Read more

Free-revision policy

The Free Revision policy is a courtesy service that the Company provides to help ensure Customer’s total satisfaction with the completed Order. To receive free revision the Company requires that the Customer provide the request within fourteen (14) days from the first completion date and within a period of thirty (30) days for dissertations.

Read more

Privacy policy

The Company is committed to protect the privacy of the Customer and it will never resell or share any of Customer’s personal information, including credit card data, with any third party. All the online transactions are processed through the secure and reliable online payment systems.

Read more

Fair-cooperation guarantee

By placing an order with us, you agree to the service we provide. We will endear to do all that it takes to deliver a comprehensive paper as per your requirements. We also count on your cooperation to ensure that we deliver on this mandate.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency