LKE Distribution in North Platte, Nebr

aska, sells everything needed to outfit

an office from basic office supplies to high$-$end office equipment and technology to furniture.

Laura Easterling manages the Accounts Receivable $($the amount customers owe LKE Distribution$).$

She has entered the billing information in an Excel workbook. She wants you to enter formulas that

will help her to analyze the data. Complete the following:New Perspectives Microsoft Office

$365$ & Excel $2016$: Comprehensive

June Jamrich Parsons

Expand all $|$ Collapse all

Contents

Productivity Apps for School and Work

Excel Modules

$$ Module $1$: Getting Started with Excel

$$ Module $2$: Formatting Workbook Text and Data

$$ Module $3$: Performing Calculations with

Formulas and Functions

$$ Module $4$: Analyzing and Charting Financial

Data

Module $5$: Working with Excel Tables,

Copy the Invoices worksheet, and then rename the copied worksheet as Overdue Accounts. In

the Overdue Accounts worksheet, do the following:

a$.$ Filter the records so only invoices whose balance is past due are displayed. These are

all records with an amount in $1-30$ days $($column I$),31-60$ days $($column J$),61-90$ days

$($column K$),$ or Over $90$ days $($column L$).$

b$.$ Sort the filtered data by invoice date $($oldest first$).$

c$.$ Include a Total row in this table, and display sums for columns I through L$.$

d$.$ Hide columns D$,$ E$,$ F$,$ G$,$ and $H.$

e$.$ Remove the filter buttons and gridlines from the table. $($Hint: Use options on View tab and the

Table Tools Design tab.$)$

Troubleshoot $8.$ In the Invoice Reports worksheet, Laura used the COUNTIF function to

count the number of invoices for each sales rep. The formulas she created display only zeros.

Fix the formulas in the range B$3$:B$6$ so that they display the number of invoices processed by

each sales rep.

In the Invoice Reports worksheet, complete the Sales Rep Analysis report. In the Commission

and Total Amount columns $($columns C and D$),$ use the SUMIF function to summarize sales

commissions $($column E in the Aging table in the Invoices worksheet$)$ and the invoice amount

$($column $F$ in the Aging table$)$ for each sales rep.

In the range $B7$:$D7,$ enter a formula to calculate the totals. Format these columns appropriately.

In the Invoice Reports worksheet, complete the Accounts Receivable Aging report. In the

Number of Invoices column, create formulas that count the number of invoices for each group

in the Invoices worksheet. $($Hint: A cell with a zero, not blank as specified in Step $6,$ will be

counted$).$

In the Total Amount column, sum the total amounts for those invoices.

Save the workbook, and then close it$.$