Sam Project Office 2007: Excel Capstone Project – Guaranteed A+
CK I
SAM PROJECTS OFFICE 2007: EXCEL CAPSTONE PROJECT
SMALL WORLD MICROLOANS
SKILLS
Add borders
Add headers to worksheets
Apply a Quick Style to a chart
Apply accounting and percent number formats
Apply bold and italics
Apply cell styles
Apply conditional formatting
Apply date and comma number formats
Change font face
Change the font color
Change the font size
Check spelling
Create a formula using the Insert Function button
Create charts using column type formats
Create formulas using the AVERAGE function
Create formulas using the MAX function
Create formulas using the MIN function
Edit a chart
Enter and edit text and numbers in cells
Enter formula arguments (by clicking or typing)
Fill adjacent cells with formulas
Insert columns and rows
Merge cells and center their content
Modify column width
Modify row height
Modify worksheet names
Move chart to a new worksheet
Position a chart
Reposition worksheets in a workbook
Resize a chart
Rotate text
Save a workbook
Select non-adjacent cells
Set cell color
Switch to a new worksheet
Use absolute references
Use SUM function
PROJECT OVERVIEW
Small World Microloans (SWM) is a nonprofit organization in Washington, DC, that connects aspiring entrepreneurs in developing countries with individual lenders to encourage economic independence. You are an assistant to a financial analyst who requests a summary of financial statistics, such as the number and amount of loans made during the past five years. You’ve created an Excel workbook to calculate the statistics, and now need to complete the worksheets and format them.
STATUS
STUDENT START FILE
CS_Excel_1a_FirstLastName_1.xlsx (Note: Download your personalized start file from sam2007.course.com)
Instructions
1. Open the file CS_Excel_1a_FirstLastName_1.xlsx and save the file as CS_Excel_1a_FirstLastName_2.xlsx before you move to the next step. Verify that your name appears in cell B4 of the Documentation sheet. (Note: Do not edit the Documentation sheet. If your name does not appear in cell B4, please download a new copy of the start file from the SAM Web site.)
2. Switch to Sheet1. In cell K1, enter 0.96 as the repayment rate. In cell K2, enter 0.68 as the percentage of women entrepreneurs.
3. Make the formatting changes summarized in Table 1-1 to make the worksheet attractive and easy to read.
TABLE 1-1 Formatting Changes on Sheet1
Cells Formatting Changes
A1:A2 Title cell style
Calibri font
16 points
C2 03/14/01 date format
H1:J1
H2:J2 Merge cells
Italics
Right alignment
K1:K2 Percent Style format
B3:D3
E3:G3
H3:J3
K3:M3 Merge and center
A3:M3 Accent1 cell style
A4:M4 60% – Accent1 cell style
Center alignment
4. In cell B7, enter a formula that calculates the number of loans made in Ghana per donor. (Hint: Divide the number of loans in cell B5 by the number of donors in cell B6.) Copy the formula in cell B7 to cells C7:M7 to calculate the number of loans per donor in the other countries.
5. In cell B13, enter a formula using the SUM function to calculate the total amount loaned to Ghana entrepreneurs in 2006–2010. Copy the formula in cell B13 to cells C13:M13.
6. In cell B14, enter a formula that calculates the average amount of a loan to a Ghana entrepreneur. (Hint: Divide the total amount of loans in cell B13 by the number of loans in cell B5.) Copy the formula in cell B14 to cells C14:M14.
7. In cell B15, enter a formula using the AVERAGE function that calculates the average amount loaned in Ghana from 2006 to 2010. Copy the formula in cell B15 to cells C15:M15.
8. In cell B16, enter a formula using the MAX function that calculates the maximum amount loaned in Ghana from 2006 to 2010. Copy the formula in cell B16 to cells C16:M16.
9. In cell B17, enter a formula using the MIN function that calculates the minimum amount loaned in Ghana from 2006 to 2010. Copy the formula in cell B17 to cells C17:M17.
10. In cell B18, enter a formula to calculate the amount of loans made to Ghana in 2010 that SWM can expect to be repaid. In the formula, use a relative reference to the loan amount in cell B12 and an absolute reference to the “Repayment rate to date” value in cell K1. Copy the formula in cell B18 to cells C18:M18.
11. Apply the Accounting Number format to cells B8:M8 and to cells B13:M18. Apply the Comma Style format to cells B9:M12.
12. Change the font color of the values in cells B13:M13 to Blue, Accent 1. Bold the values. Add a Top and Thick Bottom border to cells B13:M13.
13. Add a Right border to cells A3:A18, B3:D18, E3:G18, H3:J18, and K3:M18.
14. For cells B14:M14, apply conditional formatting using blue data bars to show the variation in average loan amount from highest to lowest. Also apply the same conditional formatting using blue data bars to cells B15:M15 to show the variation in the average amount loaned per year.
15. Make the formatting changes summarized in Table 1-2 to format cells B20:E25 and cells H20:K25.
TABLE 1-2 Additional Formatting Changes
Cells Formatting Changes
Row 20 Height set to 30.00 points
B20:E20
H20:K20 Merge and center
Fill color set to Blue, Accent1, Lighter 80%
Heading 2 cell style
B21:B25
H21:H25 Accent1 cell style
Bold
Merge and center
Rotated 90 degrees
C21:C25
D21:E21
I21:I25
J21:K21 60% – Accent1 cell style
E22:E25
K22:K25 Currency format, dollar symbol, no decimal places
16. In cell D22, enter a formula to calculate the total number of loans made in Africa. (These values appear in cells B5, C5, and D5.) In cells D23:D25, calculate the number of loans made in Asia, Europe, and Latin America, respectively.
17. In cell E22, enter a formula to calculate the total loan amounts made in Africa. (These values appear in cells B13, C13, and D13.) In cells E23:E25, calculate the loan amounts in Asia, Europe, and Latin America, respectively.
18. In cells J22:J25, enter formulas to multiply the total number of loans for each region (from cells D22:D25) by the percentage of women entrepreneurs (cell K2).
19. In cells K22:K25, enter formulas to multiply the total amount loaned for each region (from cells E22:E25) by the percentage of women entrepreneurs.
20. Apply an Outside border to cells B20:E25 and to cells H20:K25.
21. Increase the width of columns C and I to 12.00 characters.
22. Insert a new row 27. Enter the following text in cell A27: *Based on current repayment rate. Format the text as italic.
23. Select A4:M4 and A13:M13 and then insert a 3-D Clustered Column chart for the selected range that shows the total amount loaned to each country in 2006-2010. Move the chart to a new sheet named Loan Totals. (Hint: Use the Move Chart button and be sure to choose the “New sheet” option rather than the “Object in” option.)
24. Apply Chart Style 34 to the chart, and then remove the legend. Edit the chart title to Total amount loaned 2006-2010.
25. Rename Sheet1 to Statistics.
26. Move the Loan Totals chart sheet so that it becomes the last sheet in the workbook.
27. Add a header to the Statistics worksheet that displays the current date in the center of each page.
28. Change the orientation of the Statistics worksheet to Landscape.
29. Change the margins of the Statistics worksheet to Wide.
30. Check the spelling in the Statistics worksheet, making corrections as appropriate. Your final worksheets should look similar to Figures 1-1, 1-2, and 1-3.
Save your changes, close the document and exit Excel. Follow the directions on the SAM Web site to submit your completed project.
FIGURE 1-1
