Zookal
Zookal

We’d love to hear any feedback or comment from you!

© 2011-2021 Zookal Pty Ltd

View question and answer

From our collection of questions and answers
Business · Other
Question details

 

Excel Consolidation

 

This assessment task allows you to provide evidence that demonstrates your competency in the unit of competency – BSBITU314 Design and produce spreadsheets

 

 

 

This task is to be completed during class time. You have 1.5 hours to complete the task.

 

 

 

Assessment Instructions

1.       You must complete all 5 tasks to achieve the result of Satisfactorily Completed.

2.       All tasks to be completed during class, unless there are special circumstances which you have negotiated with the trainer prior. If all tasks are not completed, only those submitted in will be assessed.

3.       All task documents must have your full name, student ID, the file name and the date in the footer.

4.       Upload your file to Moodle on completion

 

 

 

Part A

 

       Task 1 – Data entry

1.     Type the following data on a new worksheet within a new workbook.

Expenditure Budget

             

Expense Type

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Average Cost

Max Cost

Min Cost

Total

Wages

10000

12000

11500

9850

       

Raw Materials

12058

25854

26785

26972

       

Freight

1254

4872

6147

4517

       

Telephones

954

655

554

433

       

Electricity

1233

1100

1433

1200

       

Water

1250

1250

1250

1250

       

Insurance

1287

0

2587

0

       

Council Rates

1125

1125

1125

1125

       

Motor Vehicles

2500

2560

3200

1500

       

Entertainment

455

655

666

555

       

Expense Total

               

 

 

2.     Save the workbook as EXPENSE1 in your folder

 

3.      Sort the Expense Type into alphabetical order A-Z (sort the data only, not the headings)

 

4.      Change Orientation to Landscape (ensure that the data is displayed in the center of the page)

 

5.      Insert footer with your full name, student id, the filename and today’s date

 

6.      Name the worksheet tab EXPENSE 1

 

7.      Copy to another worksheet in the same workbook and name the new tab EXPENSE 2

 


Task 2 - Formulas

1.     Insert into the heading row the following heading Total (GST inc)

 

2.     Insert a heading GST Rate and 10% into the spreadsheet (at the bottom of the data area, ensure the 10% is located in a separate cell to the heading.)

 

3.     Insert formulas/functions below the headings (Average Cost, Max Cost, Min Cost, Total, Total (GST inc)

(Total (GST inc) you must use an Absolute Reference and the GST is inclusive, Assume all items have GST)

 

4.     Copy to another worksheet in the same workbook and name the new tab EXPENSE 3

 

Task 3 - Editing

1.     Delete the Entertainment row

 

2.     Change the Council Rates to 2546 for the Qtr1 and Qtr 3 only

 

3.     Copy to another worksheet in the same workbook and name the new tab EXPENSE 4

 

Task 4- Formatting

1.     Format the cells that contain numbers within the data area to comma, 0 decimal places

 

2.     Format the cells that calculated figures to currency, 2 decimal places

 

3.     Format the spreadsheet; you must include the following formatting features

 

                # change of font colour

 

              # change of background colour

 

              # insert borders

 

              # insert any image as a logo. Ensure the image size and location is suitable.

 

              # merge cells

 

4.     The ensure that the column width for Qtr1, Qtr2, Qtr3, and Qtr4 are all the same width

 


Task 5 - Charts

1.     Insert a Column Chart on a new worksheet (within the workbook) for Expense Type vs Qtr 1, 2, 3, 4.

 

2.     Insert a Pie Chart on the same worksheet for Wages vs Qtr 1, 2, 3, 4. Explode the sector with the smallest wage and display the percentages for each sector

 

3.     Name this tab EXPENSE 5

 

4.     Upload the workbook to Moodle

 

 

 

 

Answer
Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.

Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.