MS Office Excel 2010 – Excel 4
Excel 4 – Activity 1 i-phone-u-phone
1. Open the Excel 4 Exercise 1 Excel document from the shared drive.
2. Save your file as Per Last First Excel 1 Activity 1 to your Excel 4 folder in your student drive.
3. On the i-Phone-u-Phone tab, setup your header and footer in class format.
4. Change the theme to Opulent, and the fonts to Office Classic 2.
5. Select columns A through I and apply an “autofit column width” to those columns.
(Home Tab-Cells Group-Format)
6. Insert a column between E and F. Add the title “Current Balance”. Wrap the title so it appears on two lines.
7. Calculate the Current Balance by subtracting the Credits and Payments from the Beginning Balance.
8. Fill the formula for Current Balance down to row 10. à Be sure to use the Autofill Options button to Fill without Formatting (This will copy the formulas but not the dollar signs).
9. Fill the Total formula from E11 to E12.
10. To Calculate the Service Charge Amt., if their Beginning Balance is over $500 they are charged the regular Rate of their Current Balance otherwise they get the discount Rate.
Calculate the Service Charge Amount in Cell F4, set up an IF statement that calculates the Service Charge Amount using the following criteria (Use Formulas-Logical-If).
Logical Test: If the Beginning Balance is greater than $500. (B4>500)
Value if True: The service charge amount is calculated by multiplying the regular service charge rate (C14) times the Current Balance ($C$14*F4) .
Value if False: The service charge amount is calculated by multiplying the discount service charge rate (C13) times the Current Balance ($C$13*F4).
Hint: If the formula was completed correctly, Dont Callme has a Service Charge Amount of $0.50.
Set cell G4 to Currency and cells G5:G10 to Number format
11. Fill the formula from G4 without formatting down to row 10.
12. In Cell I4 set up an IF statement that will insert the words Yes or No in the Late Fee column. If no payment was made by the customer, then a late fee will be applied so the word Yes should be displayed in the cell. Otherwise, the word No should be displayed in the cell.
Logical Test: If the Payment is greater than 0. (D4>0)
Value if True: “No”
Value if False: “Yes”
13. Fill the formula for Late Fee down to row 10. Center the text in cells I4:I10.
14. In Cell J4 set up an IF statement that calculates the balance after the late fee. If no payment was made by the customer, then add $25 (C15) to the new balance. Otherwise, the new balance will remain unchanged.
Logical Test: If the Late fee is “Yes” (I4=”Yes”).
Value if True: The new balance plus the Flat Fee (H4+$C$15).
Value if False: The new balance (H4).
15. Copy the formulas (but not the $ signs) in Cells F4:I4 down to the remaining rows. Adjust the column width to show the Total (autofit). Set cell J4 to Accounting and cells J5:J10 to Number (if there is no formatting, format J4 as Currency and then click on H5, choose the format painter and then highlight J5:J10—if ##### shows in the total row, adjust the column with do Autofit).
16. Set up Conditional Formatting for B4:B10. If the Beginning Balance is greater than $500, fill the cell with a light red fill color, dark red text.
· Highlight the cells B4 to B10
· Go to the Home Tab, Styles Group, Choose Conditional
Formatting-choose Highlight Rules-Greater Than—Type
500 in the first cell and choose Light Red Fill with Dark Red Text.
17. Add a comment to I3 that says “Late Fee added if No Payment was made”.
18. Add a comment to G3 that says “Discount Rate or Regular Rate charge based on Beginning Balance amount”.
19. Change the fill color for A1:J2 to Orange Accent 6 and the Font color to white.
20. Find a cell phone drawing in clipart, insert it, resize to fit in the right of the orange box.
21. Copy the phone clipart and paste in the spreadsheet. Move to the left side in the orange box. Flip the clipart Horizontally—Format, Arrange, Rotate, Flip Horizontal.
22. Update document properties to class format.
23. Create header and footer in class format.
24. Change the orientation to landscape and set margins to narrow.
25. Set the document to print one page wide by one page tall and to print gridlines and headings.
26. Make a copy of the i-Phone-u-Phone Sheet and move it to be after the i-Phone-u-Phone Tab. Rename it Phone Formulas—change to formula view-Best fit all columns-Resave the file.
27. Save the Formula Sheet as a .pdf and add Formulas to the end of the file name. Make SURE your .pdf shows all of your formulas in its entirety.
28. On the i-Phone-u-Phone Sheet, go to the Review Tab and select Show All Comments.
29. Take a full screen snipping of the page. Paste into a Word document and add a class Header & Footer. Save the file in your Excel 4 folder as Activity 1 Comments. Save as a PDF.
30. Save your regular view as a .pdf.
31. Upload the three PDF files for Excel 4 Activity 1 to edu20.org.
Excel 4 – Activity 2 File Management
1. Take a screenshot of the contents of your Excel 4 folder and paste in a Word Document.
2. Add a class Header/Footer
3. Save the screenshot as Period Last First Excel 4 File Management in your Excel 4 folder
4. Print and attach to your Excel 4 Packet