Activity 1

Jackson's Guitars uses an incentive scheme to pay its salespeople. All employees are paid a base retainer and sales in excess of the quota set for each employee are subject to an extra commission. Details for July are as follows.

Figure 1: Jackson's Guitars Sales Data

Commission Scales:

Sales in excess of quota from $0 to $5,000 get 10% commission

Sales in excess of quota from $5,001 to $10,000 get 15% commission

Sales in excess of quota from $10,001 to $15,000 get 20% commission

Sales in excess of quota greater than $15,001 get 25% commission

Required:

Task 1. On a new worksheet in the same workbook, create a spreadsheet model to calculate the total wage (including commission earned) for each employee. The IF statement must be used in your model. Format your range as table to reflect corporate colours. Name your worksheet as Assessment Activity 2 - Part A.xlsx.

Task 2. Then, highlight the employees with a green flag whose total wage is higher than $4,000 (e.g.; ) and with a red flag whose total wage is lower than $2,000 (e.g.; ).

Task 3. Calculate the following using appropriate functions:

3.1.How much commission and gross wage paid to employees in total?

3.2.What is the average monthly retainer paid to employees?

3.3.How many employees are working in Jackson's Guitar and what is the number of employees who are eligible to receive commission?

3.4.What are the lowest sales achieved? What is the highest sale achieved?

Task 4. Calculate the net annual salary (excluding any commissions earned) for each employee for the 2013 year based on the following criteria:

i. 2013 starts on a Tuesday (January 1, 2013) and ends on a Tuesday (December 31, 2013). It has a total of 365 days in the year including both weekdays and weekends. There are 104 weekend days (counting every Saturday and Sunday in the year), and 261 weekdays (Monday through Friday). Employees work on a normal 8 hour day on every weekday and don't work any overtime on the weekends, which accumulates a total of 2,088 hours over the 2013 year.

ii. Tax rate is a fix rate of 30% for all employees and there are no other deductions.

iii. Show only two decimals in your calculations for precision and use appropriate formatting for each cell.

Task 5. How can you test your formulas and functions to confirm output meets task requirements?

Task 6. Format your worksheet, preview, adjust and print in one A4 page.

Format your worksheet where required.

Submit two outputs:

1.

Output Showing Formulaes and Functions

Column B

Column C

Values

10

20

Total:

=B3+C3

Sum:

=SUM(B3:C3)

Figure 2: Formula View: output showing Formulas/Functions

2.Result View:

Output Showing Results

Column B

Column C

Values

10

20

Total:

30

Sum:

30

Figure 3: Result View: output showing the result of calculations

Activity 2

ZBA Marketing Department is using the following Country

- Marketing Region mapping:

Country

Marketing Region

Kazakhstan

Central Asia

Kyrgyzstan

Central Asia

Tajikistan

Central Asia

Turkmenistan

Central Asia

Uzbekistan

Central Asia

Czech Republic

Eastern Europe

Hungary

Eastern Europe

Poland

Eastern Europe

Russia

Eastern Europe

Slovakia

Eastern Europe

Bahrain

Middle East

Iraq

Middle East

Jordan

Middle East

Lebanon

Middle East

Palestine

Middle East

Barbados

North America

Belize

North America

Costa Rica

North America

Cuba

North America

Dominica

North America

Australia

Oceania

Fiji

Oceania

Kiribati

Oceania

Marshall Islands

Oceania

New Zealand

Oceania

Figure 4: ZBA Country - Marketing Region Mapping

There have been 50 students this year who applied for ZBA to study Cert IV in Business Administration. Nevertheless, there have been only 25 students who have been granted full offer to come and study at ZBA.

Based on the student profiles provided in the next page, the Marketing Manager requested you to finalise the mapping of corresponding marketing region for each student based on country of their residence.

Student ID

Country of Residence

Marketing Region

20130020

Australia

20130007

New Zealand

20130009

Kiribati

20130012

Dominica

20130022

Lebanon

20130023

Marshall Islands

20130027

Belize

20130028

Fiji

20130037

Kazakhstan

20130043

Kyrgyzstan

20130048

Palestine

20130061

Costa Rica

20130062

Jordan

20130069

Barbados

20130073

Poland

20130074

Bahrain

20130086

Cuba

20130093

Czech Republic

20130095

Hungary

20130098

Iraq

20130101

Russia

20130205

Slovakia

20130321

Tajikistan

20130898

Turkmenistan

20130951

Uzbekistan

Figure 5: Student profiles for ZBA

Required:

Task 1.Create a worksheet in a new workbook and enter the data shown above in Figure 4. Name your worksheet as "Marketing_Raw_Data".

Task 2.Create another worksheet in the same workbook and enter the student profiles shown above in Figure 5. Name your worksheet as "Student_Profiles".

Task 3.Name the range for ZBA Country - Marketing Region Mapping table (shown in Figure 4) as "Market_Region".

Task 4.Using a Lookup Table, map the marketing regions for each student based on their country of residence using the Market_Region named range.

Task 5.Submit two outputs:

Formula View

Result View

Task 6.Insert an appropriate heading {e.g.; Assessment Activity 2 Part C} on top of your document before you print.

Task 7.Format your worksheet, preview, adjust and print in one A4 page per output.

Save your workbook as "Assessment Activity Part C.xlsx".

Activity 3 - Automate and Standardise Spreadsheet Operation

Answer the following questions by following the document requirements provided in PART A - Prepare to Develop Spreadsheet.

Q.1. Open a new workbook and create the following template. Replace with your name. You are free to choose a different clipart as your logo. Insert Assessment Activity 3 in your header and insert your name, date and page number in your footer.

Caf sales figure

Q.2. Write a macro that will automatically do the following:

i. Change the font to "Cambria", size 12, Bold

ii. Fill in "yellow" colour in the first 3 rows designated for caf name, sales and months.

iii. Fill in "blue" colour in the first column designated for states.

iv. Calculate the total monthly sales for the caf.

v. Calculate the total quarterly sales for each state.

vi. Format all the sales value as currency.

vii. Save and print your macro.

viii. Save and print your macro.

Submission Requirements:

i. Name your Macro with the following format:

__

E.g.; James_Freckleton_20149876

Attach the Microsoft Visual Basic code to your assessment

[1] Note: This assessment is based on practical exercises from the learning resource Develop & Use Complex Spreadsheets Using Microsoft Office, Second Edition July 2012 (ISBN 9780 980 526 295) provided by Tekniks Publications Pty Limited for the Develop and Use Complex Spreadsheets - BSBITU402 unit. The intellectual property of this assessment remains with Tekniks Publications Pty Limited.