Project #39012 - Create and use a computer spreadsheet to solve a problem

Elements and Performance Criteria

Element 1

Plan a spreadsheet to solve a problem using a supplied brief.

 

Performance Criteria

1.1              The plan identifies the purpose, specifications and features required for the spreadsheet in accordance with the brief.  

 

Element 2

Create a spreadsheet to solve a problem using a supplied brief.

 

Performance Criteria

2.1               Data is entered and formatted to create the spreadsheet required by the brief.

                   Range: formatting may include but is not limited to - column width, alignment, text, number formats.

2.2              Spreadsheet cell functions and formulae are entered and accuracy and data integrity against original sources is confirmed.

Range: includes but is not limited to - add, subtract, multiply, divide, sum, average,

maximum.

2.3              The spreadsheet is saved according to the requirements of the brief.

                   Range:  may include but is not limited to – file type, file name, location.

 

Element 3

Use the spreadsheet to provide a solution to the problem.

 

Performance Criteria

3.1              Entered data is manipulated to provide the solution required by the brief.

                   Range:  may include but is not limited to – new data added, formulae amended.

3.2              Graph appropriate to the solution is created in accordance with the brief.

                   Range:  two different types of graphs.

3.3                   The completed spreadsheet and graph are printed out in hard copy in a format specified by the brief and are readable.


 

Before you start…

Conditions

§     This is an open book assessment. You are encouraged to refer to your 2784 workbook for help.

Resources

§     Your computer.

§     A calculator for Task 2. You can use the calculator on your computer. (You can find this by opening then )

 

Notes:

 

The boxes with a faint dotted border are not directions. They are the NZQA criteria you will be assessed on, and that the tutor checks. Nothing is required of you within these boxes. You may find them useful to read through, as they show what you will be assessed on.

 

If you’re typing the answers into this assessment booklet in Microsoft Word, the boxes with the blue borders are where you should type or tick to select an option.

 

All printouts are to be printed as Adobe .pdf files. You will find instructions on how to print to Adobe in your Unit 2784 folder in Datafiles.  Go to C:/My Documents/Datafiles/Unit 2784/2784 Other Resources and open “Printing to PDF” file.

 

 

 

 

 

 

 

 

Task One: Plan A Spreadsheet.

Element 1

Plan a spreadsheet to solve a problem using a supplied brief.

 

PC 1.1

The plan identifies the purpose, specifications and features required for the spreadsheet in accordance with the brief.

Brief:

You are working for Cybermart Computer Supplies Ltd.  The Sales Manager needs to make informed business decisions so he has asked you to create a spreadsheet to show & calculate Cybermart’s quarterly financial results. 

 

In order for the Sales Manager to be able to make his decisions he needs to know:

- Total sales for each month of the last quarter

- Projected sales for April (which is expected to be a 10% increase on the March sales)

- Total Estimated Sales

- Total Actual Sales

- GST Content

- Average Monthly Sales.

 

He has specifically asked that:

-  Long headings be text wrapped

-  Title and Sub-title be ‘merged & centred’

-  All numerical values should have a comma (thousand separator) and show two decimal places.

-  Columns need to be wide enough so that all data can be easily read and is not all jammed up.

-  Results be displayed in two different charts on separate sheets in the Excel File.

-  The spreadsheet be saved as an Excel file in your My Documents, named Cybermart and include the spreadsheet and two charts on separate sheets.

-  All printouts are to be on one  A4 landscape page

 

Aside from what the Sales Manager has specified above, you must also choose appropriate formatting to enhance your spreadsheet to make it look professional.  He will expect you to use appropriate font types and sizes, bold items that should stand out, use alignment options.  You may use enhancements such as italics, apply borders and adjust column widths as appropriate. 


The Sales Manager has provided you with the following data to use in your spreadsheet.  This data represents sales for Hardware and software for Cybermart.

 

 

 

 

 

 

 

Cybermart Computer Supplies Ltd

 

Sales January-April 200X

 

 

 

 

 

 

Hardware Brand

January

February

March

 

Espon

24670.00

27500.50

30455.60

 

Pewlett Hackard

32700.00

30500.55

33450.70

 

Pomcap

52230.45

55640.75

63570.20

 

Nonac

43100.25

45677.45

46770.75

 

Total Hardware

 

 

 

 

 

 

 

 

 

Software Type

 

 

 

 

Games

10670.90

22320.50

25678.95

 

Business

25437.60

27654.85

32450.90

 

Home

16540.77

18540.55

21345.60

 

Other

12355.67

15430.60

16320.55

 

Total Software

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As part of your planning process, you are required to use the brief and the information/data you have been given so far to complete a diagram and a Specification Plan, before creating your spreadsheet.

 

 

Complete the Diagram over the page for all Headings and Labelsonly; exclude the actual financial amounts for now.

 

 

Then complete the Specification Plan on the following page, include the formatting that you were asked to do as well as the formatting that you have decided to use.   Also include what features of Excel you have used to display the results in a way that is clear and easily understood. 

 

 

Please Note:  It is okay to fill out parts of the specification plan as you go – it does not have to be totally completed before you create the spreadsheet, you may find you go back and add or change things.  Please ensure you double check that your specification plan is the same as your spreadsheet before you submit your assessment for marking and that you have checked off all of your student check column to show you have done this.

 


 



 

A

B

C

D

E

F

G

H

I

1

 

2

 

3

 

 

 

 

 

 

 

 

 

 

4

 

 

 

 

 

 

 

 

 

5

 

 

 

 

 

 

 

 

 

6

 

 

 

 

 

 

 

 

 

7

 

 

 

 

 

 

 

 

 

8

 

 

 

 

 

 

 

 

 

9

 

 

 

 

 

 

 

 

 

10

 

 

 

 

 

 

 

 

 

11

 

 

 

 

 

 

 

 

 

12

 

 

 

 

 

 

 

 

 

13

 

 

 

 

 

 

 

 

 

14

 

 

 

 

 

 

 

 

 

15

 

 

 

 

 

 

 

 

 


Specification Plan

 

Use the information supplied so far to complete the Specification Plan.  You will use the formatting and features you state here on the spreadsheet you will create.

 

Purpose of Spreadsheet:

 

Formatting:

 

Item

Font Type

Font Size

Alignment

Enhancement

  Student Check

(ü)

Assessor Check

(ü)

 

Title:

 

 

 

 

 

 

 

Subtitle:

 

 

 

 

 

 

 

Column headings:

 

 

 

 

 

 

 

Product Type  labels:

 

 

 

 

 

 

 

Product labels:

 

 

 

 

 

 

 

Totals labels:

 

 

 

 

 

 

 

Totals values:

 

 

 

 

 

 

 

Other numeric values:

 

 

.

 

 

 

 

Constraints: 

 

 

 

Features: 

 

 

 

 


 

 

Use this section to check your final spreadsheet once you have completed it.

 

 

HAVE YOU TICKED THESE OFF AS YOU COMPLETED THEM?

 

Completed or checked

The spreadsheet Cybermart includes all formatting as briefed.

 

 

The spreadsheet Cybermart complies with all formatting as stated in your plan.

 

 

The spreadsheet Cybermart includes two different charts.

 

 

All information including headings, labels, values and calculations make readable sense.

 

 

The spreadsheet is legible.

 

 

All printouts are presented on one A4 landscape page.

 

 

All information, including text, numeric values and formulae are accurate.

 

 

All calculations have been checked.

 

 

 

The spreadsheet is an Excel file, named Cybermart, saved into My Documents.

 

 

 

 

 


 

Task Two: Produce a Spreadsheet

Element 2

Create a spreadsheet to solve a problem using a supplied brief.

 

PC 2.1

Data is entered and formatted to create the spreadsheet required by the brief.

Range:  formatting may include but is not limited to – column width, alignment, text, number formats.

PC 2.2

Spreadsheet cell functions and formulae are entered and accuracy and data integrity against original sources is confirmed.  

Range:   includes but is not limited to – add, subtract, multiply, divide, sum, average, maximum.

PC 2.3

The spreadsheet is saved according to the requirements of the brief.

Range:  may include but is not limited to – file type, file name, location.

 

Instructions & Conditions:

You will be creating two spreadsheets, Cybermart and Cybermart Final

 

1.    Use your Diagram and Specification Plan from Task 1, as well as the numeric details supplied in the brief, to create a spreadsheet.  Apply all formatting as specified.  Adjust column widths to display all information clearly.  Remember to format all numeric values.

 

2.         Type your name in A25. 

 

3.         It is forecast that April sales for all hardware and software items will increase by 10% from March sales.  Click into the first row under the heading April.  Enter a formula to calculate March figures plus 10%.  Fill down for all hardware and software items.

 

4.         Enter formulae to calculate Total Hardware and Total Software sales for each month.  Format totals to display in line with your specification plan.

 

5.         Leave an empty row below the total for Software sales.  Then enter the label Total Sales.  Format this label.  Enter a formula to add up the sales from Total Hardware & Total Software.  Fill across all columns.  Format these totals in accordance with your specification plan.

 

6.         Click into the first cell below the heading Total Estimated Sales.  Use Autosum to calculate total sales for each item.  Fill this down including the total row.  Extend all formatting.

7.         Click into the first empty cell below the heading Total Actual Sales.   Enter a formula to subtract April's sales from the Total Estimated Sales.  Put similar formulae in this column for all Hardware and Software items.

 

8.         Click into the first empty cell below the heading GST Content.  Enter a formula to calculate the GST content of Total Actual Sales.  Put similar formulae in this column for all Hardware and Software items. Please note: Total Sales include GST (calculate by multiplying by 3 then dividing by 23).

 

9.         Click into the first empty cell below the heading Average Monthly Sales.  Enter a formula to calculate the Average Monthly Sales.  Put similar formulae in this column for each item. 

 

10.      Enter the label Check Total in column A directly below the label for Total Hardware.  Enter the label Check Total in column A directly below the label for Total Software.  Enter subtraction check total formulas, to ensure that the total of the Total Actual Sales is correct for both Hardware and Software.

 

11.      Enter the label Hardware Maximum Sales in column A below all other labels. In the same row (under columns B – E), enter a formula that will calculate the Maximum Sales for each month for all Hardware Brands.  Format this in line with your specification plan. 

 

12.      You will be assessed on your accuracy.  (Please note all columns should have totals.)

 

13.      Check that your spreadsheet formatting is the same as you stated in your Specification Plan, and that your original figures are exactly the same as what was given in the brief.   Check off the student check part of your specification plan, for the items you have completed so far (top and bottom) to show you have done this.

 

14.      Preview to ensure all columns on your spreadsheet can be easily read.  Print your spreadsheet according to the instructions in the brief.  Name your PDF file Cybermart. 

 

15.      Save your spreadsheet into your My Documents and Name as Cybermart. In the space below describe the steps you took to save and name your spreadsheet as required.

 

 

 

 

16.      Close this file.

 

 

Element 3

Use the spreadsheet to provide a solution to the problem.

PC 3.1

Entered data is manipulated to provide the solution required by the brief.

Range:  may include but is not limited to – new data added, formulae amended.

 

17.      The Sales Manager has informed you that the figures you were given for the Hardware Brands in February were incorrect, he has provided you with the correct figures below.  Reopen Cybermartand correct the figures for Hardware Brands in February. 

 

Hardware Brand

January

February

March

Espon

24670.00

37500.50

30455.60

Pewlett Hackard

32700.00

31500.55

33450.70

Pomcap

52230.45

55645.75

63570.20

Nonac

43100.25

55677.45

46770.75

Total Hardware

 

 

 

 

 

 

 

 

 

18.      Save this file as Cybermart Final.

 

19.      The Sales Manager has informed you that April sales are actually forecast to be 12% higher than March, not 10% as you were previously told.  He wants you to amend the formula for April to show this.Fill this down including the total row.

 

20.      Ensure you save these changes to Cybermart Final and close the file.

 

PC 3.2

Graph appropriate to the solution is created in accordance with the brief.

Range:  two different types of graphs.

 

21. Reopen Cybermart Final.  Create a “Clustered Column with 3-D Effect chart on a separate sheet to display all Hardware Brand sales from January - April 200X.  

- Add the titles from A1 and A2. 

- Include a legend showing series labels.

 

22. Create a “Line chart with markers displayed at each data value”, on a separate sheet, that compares the Total Estimated Sales and the Total Actual Sales for all software items. 

- Include a legend showing series labels

- Make the Category (x) Axis Labels Software Types

- Make the title Software – Estimated v Actual 

 


PC 3.3

The completed spreadsheet and graph are printed out in hard copy in a format specified by the brief and are readable.

 

23. Preview your spreadsheet to ensure all columns on your spreadsheet can be easily read and print according to the instructions in the brief.  Name your PDF file Cybermart Final. 

24. Print your column chart according to the instructions in the brief.  Name your PDF file Cybermart Chart 1. 

25. Print your line chart according to the instructions in the brief.  Name your PDF file Cybermart Chart 2. 

26. Give your Cybermart Final file a final check and ensure that you have checked off all the student check part of your specification plan, (top and bottom) to show you have done this.

 


 

NOTE: X Axis Label and Legend may be switched ie: X Axis maybe product labels, as long as data/graph is accurate.

 

Phew !!

you have completed your 2784v6 Assessment.

 

 

 

 

 

 

 

 

WHAT WE NEED FROM YOU: To be marked as competent, you need to send the following to your tutor for marking.

·        This 2784 Assessment Booklet

·        Your MS Excel File titled “Cybermart”.

·        Your PDF file called ‘Cybermart’

·        Your MS Excel File titled “Cybermart Final” with spreadsheet and two Charts included

·        Your PDF file called ‘Cybermart Final’

·        Your PDF file called ‘Cybermart Chart 1’

·        Your PDF file called ‘Cybermart Chart 2’

 

On Campus Students:

 

Hand in your 2784v6 Assessment Booklet and place your 2784 Assessment files in your Tutor’s DropBox$ (S:)

Subject Business
Due By (Pacific Time) 09/07/2014 07:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy