ISA $315$ EXCEL MID$-$TERM SKILL ASSESSMENT

Welcome to the Midterm Exam. Important Reminders:

$1.$ You Can Only Take the Exam Once and Can Only Submit One Exam.

$2.$ You Have Five Hours to Complete and Upload Your Midterm Exam To CANVAS

$3.$ Keep Track of Your Time to Ensure You Submit Your Exam Before Its CANVAS folder closes.

$4.$ Do Your Best to Complete the Entire Exam.

$5.$ Complete each process based on Modules $1,2,$ and $3.$

$6.$ Now that you have started your exam, I cannot answer questions about the exam instructions or

process. However, I assure you that everything in this exam you have or should have seen

before in Modules $1,2,$ and $3.$

Sincerely, Best of Luck

Scenario: Facade Importers is a clay facade export$/$import company in India. You have signed a

contract to create an original Excel worksheet that analyzes Facade$$s sales representatives$2099$

sales performance. To meet your contractual requirement, you perform the following tasks in order to

create an original Excel worksheet:

Part $1$: Construct the Worksheet

$1.$ Open the Facade workbook where you will begin creating your original worksheet.

$2.$ On your original worksheet, change the worksheet theme to one that is not the default theme.

$3.$ Enter a worksheet title in A$1$ for Fa$$ade Importers

$4.$ Enter a worksheet subtitle in A$2$ to show that you will analyze $2099$ sales representatives$$

performance.

$5.$ Starting in A$3,$ enter the following column labels: Sales Representatives, $2099$ Quota, Total

Sales, Sales Cancelled, Sales Cancelled Percentage, and Net Sales

$6.$ Next, enter Fa$$ade$$s sales data from Table $1.$

Part $2$: Worksheet Calculations and Design

$1.$ In E$4$:E$9,$ use Total Sales and Sales Canceled in a simple formula that computes Sales

Cancelled Percentage. If total sales represent $100\%,$ what percentage part of total sales are

cancellations?

$2.$ In F$4$:F$9,$ use a simple formula to compute each representative$$s Net Sales, which is the

difference between what the representative sold and the sales that were canceled or returned.

Table $1$ Fa$$ade Importers Sales Data

Sales Total Sales $2099$

Representatives Sales Cancelled Quota

$3.$ Facade wants to see a dollar value that shows if a representative exceeded, met, or failed to

meet their $2099$ Quota.

a$.$ First, add Above$/($Below$)2099$ Quota column label to the worksheet.

b$.$ Next, in G$4$:G$9,$ use a simple formula to compute each representative$$s Above$/($Below$)$

$2099$ Quota value based on the representative$$s sales quota and their net sales volume.

$4.$ Complement the above and below quota information in G$4$:G$9$ by computing a representative$$s

net sales performance as a percentage of their sales quota in H$4$:H$9$:

a$.$ Add the column label Percentage of Quota to the worksheet

b$.$ Employ a simple division formula and the round function to produce a percentage that is

limited to two decimal places.

$5.$ Next you create two worksheet labels: $2099$ Compensation and $2100$ Sales Quota in order to

compute this year$$s compensation and next year$$s quota.

a$.$ You want to avoid using hard values in your compensation and quota formulas:

b$.$ You create a Compensation$/$Quota section on the worksheet in A$12$:B$12$

c$.$ In A$13$:B$18$ the following information labels and values are added in separate cells.

$1)$ Compensation Percentage, which is $12\mathrm{.}3\%$

$2)$ Fixed Taxes, which is $25\mathrm{.}5\%.$

$3)2100$ Quota $\%$ Increase $10\%,$ which is $10\%.$

$4)2100$ Quota $\%$ Increase $5\%,$ which is $5\%.$

$5)$ Sales Cancellation Limit, which is $2\%$

$6)$ Quota Percent Limit, which is $100\%$

$6.2099$ Compensation:

a$.$ According to Federal compensation Guidelines: Fa$$ade$$s sales representatives$2099$

Compensation $=$ Income $($compensation percentage paid on net sales$)$ less taxes $($a fixed

percentage of income$).$

b$.$ In I$4$:I$9$ use a complex formula to compute each representative$$s $2099$ Compensation. Your

formula must include cell references and parentheses while avoiding hard values in the

formula.

c$.$ As a complex formula, this formula is a challenge. In one formula, you have to find income,

then find taxes separately before subtracting one from the other. Try working it out on

paper before using Excel.

$7.2100$ Sales Quota

a$.$ Fa$$ade$$s representative$$s $2100$ Sales Quota calculation might be challenging as well:

$1)$ If a representative$$s net sales are less than their $2099$ quota, their $2100$ quota will be

their $2099$ quota increased by five percent. For instance, if a representative$$s $2099$ quota

was $$100$ their $2100$ quota will be $$105.$

$2)$ If a representative$$s net sales are equal to or greater than their $2099$ quota, their $2100$

quota will be their $2099$ quota increased by ten percent. For instance, if their $2099$ quota

was $$200$ their $2100$ quota will be $$220.$

$3)$ In J$4$:J$9$ use a complex formula to compute each representative$$s $2100$ Sales Quota.

Your formulas must include cell references, parentheses, and the round function. The

round function must round the quota value to the lowest $$100.$ Thus, for example, say a

$$475,250$ quota would round to a $$475,200$ quota.

$4)$ No complex function i