WE WRITE CUSTOM ACADEMIC PAPERS

100% Original, Plagiarism Free, Tailored to your instructions

Order Now!

Spreedsheets

Assignment Requirements
 
 
Assessment Description
This assessment is divided into four (4) parts:

Part 1 – Branch Template
Part 2 – Branch Workbooks
Part 3 – Head Office Template
Part 4 – Head Office Workbook

For this assessment task you are required demonstrate knowledge and skills in:

Preparing to develop spreadsheets by analysing tasks to determine specifications and organising task requirements in relation to data entry, storage, output, reporting and presentation requirements
Developing a linked spreadsheet solution including using formulae and functions, formatting cells, using data attributes with absolute and/or relative cell references and testing formulae to confirm output meets tasks requirements
Automating and standardising the spreadsheet operation by using templates and macros
Using the spreadsheets including entering/checking data to be error free, importing/exporting data, using manuals and documentation, previewing and printing tasks and finally naming and storing spreadsheets
Representing numerical data in graphic form.

Assessment Task 2 of 3

Develop and use complex spreadsheets – Portfolio of Evidence

Instructions
You are required to use Microsoft Excel to create workbooks or templates as instructed below. Please follow instructions and submit all required workbooks, templates and printouts.

Scenario

Breeze Appliances have three branches across Australia: Melbourne, Sydney and Brisbane. Every quarter, each branch manager is required to calculate the sales commission each sales person achieves each month and send this information to head office. At head office the sales information is collated into one spreadsheet for analysis.
A template will be required for the recording and calculation of sales and commission for each branch. Head office also requires a template to analyse the sales data received from each branch.

 

 
Part 1 – Branch Template
A workbook template will be created that will record and calculate the sales data for each branch shown in appendix 1. (The data for each branch must be recorded in a separate workbook.)

Spend some time planning and designing your template. You may wish to note down the plan on a piece of paper.
Import the text file called Commission Rates.txt into a new workbook. Name the worksheet Commission Rates.
Create a named range for the commission rates data. The Commission Rates data is used to look up the % commission each sales person will receive. This percentage will then be used to calculate the commission for each month, based on monthly sales.
Insert a new sheet before the Commission Rates worksheet. Name the new sheet Sales.
On the Sales worksheet, calculate the commission each sales person will receive each month.
Each month if a sales person equals or exceeds a 12% commission target, they receive an additional bonus of $1,250. Insert a column for each month to determine if the sales person will receive the bonus. (This can be achieved by combining an IF and a VLOOKUP statement.) The commission target and the amount of bonus on offer will vary every quarter. Ensure that this is taken into account when designing the spreadsheet by positioning the bonus amount and the % commission target in separate cells at the top of the worksheet data. You will need to think of using absolute referencing in your formula as well.
Columns must be wide enough to display data properly.
Include a header containing the file name of the workbook.
Today’s date and a Branch name area must appear on the worksheet. (The branch name will be filled in when the template is used.)
Calculate the total sales for each month.
Save the workbook file as a template with an appropriate file name in the correct templates folder. Note that macros are required in the template.
A summary of the sales for each of the three months recorded must appear on the worksheet. This summary must include average, maximum and minimum calculations for each month. All figures must be rounded to the nearest $100.
Format the spreadsheet so it is clear and easy to read. Use Calibri font in keeping with the organisation’s house style. Format currency columns to two decimal places.
Check all that formulae work correctly.
Ensure that the workbook template is set up to automate all calculations so the user only needs to enter the sales data for each month.
Ensure users cannot enter data in formula cells by protecting these cells. Please use ‘password’ for your password when protecting the sheet as the marker needs to be able to access the formulae.
Develop a macro that will sort all sales persons into alphabetical order.
Develop a macro that will print only the sales summary information.
Assign these two macros to buttons at the top of the worksheet.

 
Part 2 – Branch Workbooks

Using the template created in Part 1 to produce a workbook for each branch using the sales data in appendix 1 for all three branches.
Ensure all data is checked to ensure accurate input.
Each workbook should be saved with an appropriate file name in your working folder.
Ensure the sales person data is sorted into alphabetical order and the summary information is printed for each workbook. Scan the reports into a pdf file to submit.

Part 3 – Head Office Template
Create a workbook template that will display the total sales for each month from each branch to be used at Head Office.

Spend some time planning and designing your template. You may wish tonote down the plan on a piece of paper.
Columns must be wide enough to display data properly.
Include a header containing the date (to the right) and a footer with the file name in the lower right corner.
Use Calibri font in keeping with the organisation’s house style.
Save the workbook file as a template with an appropriate file name in the correct templates folder.
Total each column and row to determine the total sales for each month and each branch.
Create a chart that displays the sales for each branch over the three month period. Use an appropriate chart type and add a title. Insert the chart on a new worksheet and rename the sheet appropriately.
Create a chart that displays the total sales for each branch. Use an appropriate chart type that will show which branch has the larger percentage of sales against all other branches. Use appropriate data labels and add a chart title. Explode the largest segment. Insert the chart on a new worksheet and rename the sheet appropriately.

Part 4 – Head Office Workbook

Using the Head Office template created in Part 3 produce a Head Office workbook using the data from the Branch workbooks created in Part 2.
You will do this by linking the Branch workbooks to the Head Office workbook. Any changes in the data in the Branch should automatically update in the Head Office workbook. You will need to think about using relative referencing in your formulas to be able to copy the link across your months.
The workbook should be saved with an appropriate file name in your working folder.

 

 
Checklist
You will need to complete the following:

Please make sure you have followed all formatting requirements

Checked that all formulas and functions work properly

Part 1:
R  Created and submitted a Branch macro enabled template.
R  Please ensure worksheet protection password is ‘password’ for marker to access

Part 2:
R  Created and submitted 3 Branch macro enabled workbooks
R  Printed three sales report (one for each branch) submitted as pdf file

Part 3:
R  Created and submitted a Head Office template

Part 4:
R  Created and submitted Head Office workbook

Upload all documents to OpenSpace

Note: The field boxes above are an optional self-checklist. Double click each check box to mark as complete.
 
Assessment Description
This assessment task is divided into two parts:

Part 1 – Written Tasks
Part 2 – Printing Reports

For this assessment task you are required demonstrate knowledge and skills in

Developing a linked spreadsheet solution including using formulae and functions, and testing formulae to confirm output meets tasks requirements
Automating and standardising the spreadsheet operation by using templates and macros
Using the spreadsheets including entering/checking data to be error free, using manuals and documentation, previewing and printing tasks and finally naming and storing spreadsheets

Assessment Task 3 of 3

Develop and use complex spreadsheets – Short answer questions and Portfolio of Evidence

Instructions
Part 1 – Please use a Word document to answer the questions. Although there is no word limit, sufficient explanations must be present.
Part 2 – Please scan your printouts into a pdf file and submit the pdf file. We need to determine you can print these files.
Part 1 – Written Questions
1            Explain how you have checked formulas in the workbook template created in Assessment 2, Part 1.
2            Write down the formulas used to calculate the following in the Branch template created in Assessment 2, Part 1, question 5 and 12 respectively.
% commission determined for each sales person for the first month
The average sales for a month rounded to the nearest $100
                                                                                                                                                                                                     
 
3            Explain the steps you would take to edit the print range macro created in Assessment 2, Part 1, question 18 ie to change the data range selected.
……………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………
4            Fill in the table below indicating the file name and folder location you used to store spreadsheet files for this assessment.
 

 
File Name
Folder Location

Assessment 2, Part 1
 
 

Assessment 2, Part 2
 
 

 
 

 
 

Assessment 2, Part 3
 
 

Assessment 2, Part 4
 
 

5            Use manuals, user documentation and online help to look up the following topics. Record your findings in the spaces provided.
Explain what the PMT function is and when you would use it.
……………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………
How would you split the screen so you could view the top and lower part of a worksheet?
……………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………
 
Part 2 – Printing Reports
These questions relate to templates and workbooks created in Assessment 2.
1            A sales person was omitted from the data for Brisbane; his details are shown below. Add the sales person’s data to the Brisbane workbook (the Head Office Sales Workbook should automatically update). Sort the sales people into alphabetical for the Brisbane workbook. The below prints out will show new figures due to the added sales person.

Brisbane Branch

 
Jan 14
Feb 14
Mar 14

Elliott, James
5,870.00
6,030.00
5,968.00

2            Print preview and print the three worksheets that detail the sales for each branch for the quarter Jan – Mar 14. Ensure all data is printed.
3            Print the template that each branch uses, and then print another copy showing the formulas.
4            Print the template that is used by Head Office, and then print another copy showing the formulas.
5            Print the entire workbook that Head Office has produced that displays the sales from all branches for the quarter Jan – Mar 14.
6            Print preview and print the chart –total sales for each branch – found in the Head Office workbook (Part 3, question 8) for the quarter Jan – Mar 14.
7            Scan and upload all printed pages to OpenSpace.
Checklist
You will need to complete the following:

Submit questions for Part 1 as a Word document

Submit scanned printed reports as a PDF file for Part 2

Upload all documents to OpenSpace

Note: The field boxes above are an optional self-checklist. Double click each check box to mark as complete.
 
 
 
 
Order Now
https://thecustomwritings.com/order/
YOU CAN ALSO PLACE OTHER SIMILAR ORDERS ON OUR WEBSITE AND GET AMAZING DISCOUNTS!!!

Our Service Charter

  1. Excellent Quality / 100% Plagiarism-Free

    We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers.
  2. Free Revisions

    We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed.
  3. Confidentiality / 100% No Disclosure

    We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service.
  4. Money Back Guarantee

    If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit.
  5. 24/7 Customer Support

    We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.

Excellent Quality
Zero Plagiarism
Expert Writers

Instant Quote

Subject:
Type:
Pages/Words:
Single spaced
approx 275 words per page
Urgency (Less urgent, less costly):
Level:
Currency:
Total Cost: NaN

Get 10% Off on your 1st order!