Independent Project $1-6$

As accounts receivable clerk for Livingood Income Tax and Accounting, you track daily payments from clients in an Excel worksheet. After entering the data, you format the worksheet and prepare it for distribution to coworkers.

$[$Student Learning Outcomes $1\mathrm{.}1,1\mathrm{.}2,1\mathrm{.}4,1\mathrm{.}5,1\mathrm{.}6,1\mathrm{.}8]$

File Needed: $[$your name$]-$EX$2019-$IndependentProject$-1-6($Available from the Start File link.$)$

Completed Project File Name: $[$your name$]-$EX$2019-$IndependentProject$-1-6$

Skills Covered in This Project

Create and save a workbook.

Enter labels, dates, and values.

Use the Fill Handle to build series.

Use SUM.

Change font size and attributes.

Adjust column width and row height.

Choose a theme and cell styles.

Choose page layout options.

Rename and apply color to sheet tabs$.$

IMPORTANT: If you are a Canadian user, be sure to verify that your browser and Microsoft Office use the same country settings. See here for a Help topic on how to change your settings.

Steps to complete This Project

Mark the steps as checked when you complete them.

Open the EX$2019-$IndependentProject$-1-6$ start file. 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$.$

NOTE: If group titles are not visible on your Ribbon in Excel for Mac, click the Excel menu and select Preferences to open the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show. Close the Excel Preferences dialog box.

Apply the Organic theme for the workbook.

Type Livingood Income Tax and Accounting in cell A$1$ and press Enter.

Type Accounts Receivable in cell A$2$ and press Enter.

Type the labels in row $4$ as shown here in Figure $1-104$:

The table displays the data to be entered in row $4.$

A B C D E F

Date Invoice Client ID Amount Payment Date Payment Type

Figure $1-104$ Data to be entered

AutoFit columns E:F to display each label in row $4.$

Type $8/18/19$ in cell A$5$ to enter the date. In cell A$6,$ type $8/20/19$ to set a pattern for the dates.

Select cells A$5$:A$6$ and use the Fill Handle to fill in dates to cell A$15.$

Select cell B$5$ and type $1001$ for the first invoice number. In cell B$6,$ type $1002$ to set a pattern for the invoice numbers.

Select cells B$5$:B$6$ and use the Fill Handle to fill in invoice numbers to reach cell B$15.$

Type LITA$001$ in cell C$5$ as the first client ID$.$ In cell C$6,$ type LITA$002$ to set a pattern.

Use the Fill Handle to complete the client ID numbers.

Type $425$ in cell D$5.$ In cell D$6,$ type $450$ to set the pattern and fill in the amounts.

Type $8/28/19$ in cell E$5$ to enter the date. In cell E$6,$ type $8/29/19$ to set a pattern for the dates. Fill the rest of the cells to E$15.($Figure $1-105).$

The table displays the data to be entered in rows $5$ through $15.$

A B C D E F

Livingood Income Tax and Accounting

Accounts Receivable

Date Invoice Client ID Amount Payment Date Payment Type

$8/18/20191001$ LITA$0014258/28/2019$ Check

$8/20/20191002$ LITA$0024508/29/2019$ EFT

$8/22/20191003$ LITA$0034758/30/2019$ Credit card

$8/24/20191004$ LITA$0045008/31/2019$ Check

$8/26/20191005$ LITA$0055259/1/2019$ EFT

$8/28/20191006$ LITA$0065509/2/2019$ Credit card

$8/30/20191007$ LITA$0075759/3/2019$ Check

$9/1/20191008$ LITA$0086009/4/2019$ EFT

$9/3/20191009$ LITA$0096259/5/2019$ Credit card

$9/5/20191010$ LITA$0106509/6/2019$ Check

$9/7/20191011$ LITA$0116759/7/2019$ EFT

Figure $1-105$ All columns filled

Type Check in cell F$5.$ Type EFT in cell F$6.$ Type Credit card in cell F$7.$ Fill the rest of the cells to F$15.$

Format data.

Select cells C$5$:C$15,$ increase the indent one time, and AutoFit the column. Do the same for the payment type data.

Format the values in column D as Accounting Number Format.

Select cells A$1$:F$2.$ Click the Format button $[$Home tab, Cells group$]$ and select Format Cells... Select the Alignment tab. Click the Horizontal alignment arrow, choose Center Across Selection, and click OK$.$ Change the font size to $18.$

Select the labels in row $4$ and apply bold and center alignment.

AutoFit columns that do not show all the data.

Apply All Borders to cells A$4$:F$15.$ Apply an Outside Border for cells A$1$:F$2.$

Apply the Green, Accent $1,$ Lighter $60\%$ fill color $($fifth column$)$ to cells A$1$:F$2$ and A$4$:F$4.$

Apply the Green, Accent $1,$ Lighter $60\%$ fill color $($fifth column$)$ to cells A$6$:F$6.$

Use the Cmd key to select the data in rows $8,10,12,$ and $14$ and apply the same fill color.

Rename Sheet$1$ as AR and set the tab color to Green, Accent $1($fifth column$).$

Define page layout and add document properties.

Center the worksheet horizontally on the page.

Create a header with the sheet name in the left section and your name in the right section.

Delete an existing author name and key your first and last name as Author in the Properties dialog box.

Type Receivables in the Title box $($Figure $1-106).$

The Title and Author properties are change