Project #38163 - Create a spreadsheet to solve a problem!

I got the date wrong about my course finishing and although this is easy work i have another unit to do as well and with my son here its hard to focus, please help! dont want to fail my course iv passed everything so far!

 

Create and use a simple computer spreadsheet to solve a problem

 

Pre-assessment Instructions and Conditions:

 

 

Before you start…

Conditions

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

§    You may also consult your tutor.Your tutor can’t help you when you are doing a real assessment, but he/she can help you in the pre-assessment if you get badly stuck.

Resources

Your computer with a spreadsheet application such as Microsoft Office Excel installed.

A calculator for checking amounts. You can use an ordinary calculator or the one that’s on your computer which you can find by selecting
Start > All Programs > Accessories > Calculator.

Printouts:

All printouts are to be printed as PDF files using CutePDF Writer as your printer.

 

Note: Microsoft Excel 2010 has a Save as PDF option, but you must not use that option to create your PDFs, because PDFs made by the Save As method do not qualify as proof that you know how to print Excel worksheets.

Task One: Plan A Simple Spreadsheet.

 

Instructions:

You help out at your local cricket club, the West Coast Knights, and the coach has asked you to put together a spreadsheet to show achievement details for the stop six batters who played in last week’s game.  The coach has given you the following list of names, scores and the number of balls bowled to each batter.

Name

Runs

Balls Faced

Sally

124

164

Fred

46

92

Josh

22

31

Nick

89

82

Anne

26

18

Simon

40

38

 

Your Brief

The coach has asked you to provide the following information:

·         Calculate the total number of runs for these six batsmen (Sum)

·         Calculate the number of runs scored per ball (Division)

·         Calculate the how many runs each batsman would have scored if they could have improved their batting by 10% (Multiplication)

·         Calculate the top score of all the batters (Maximum)

·         Calculate the average of score of all the batsmen combined (Average)

·         Include a 3_D column chart showing the different run results of each batter. Include the chart on the same sheet.

·         Ensure your spreadsheet and chart print on one A4 page

·         The main title for the spreadsheet should be West Coast Knights Cricket Club, and the subtitle should be Top Six Batters.

·         Make the title of the chart a centred overlay title, Runs Scored. and make the X-Axis title Player.

·         Use appropriate formatting for titles, headings and labels

·         All numbers that are the result of calculations should show TWO decimal places, but the Runs numbers and the Balls Faced numbers should have 0 decimal places.

The coach would also like any printouts of the result to fit onto a single A4 landscape page.

Note: the club colours are two shades of light purple.

Task One Steps

1.    Complete the layout diagram on the next page for all Titles, Headings and Labels.

Ø  Don’t put in the actual numbers- just the titles, headings and labels.

Ø  The table on page 4 just shows three columns, but if you read the brief carefully, you will see that you will need to use more columns than that. Make sure your layout diagram shows those extra columns, too.

Ø  Long headings should be text wrapped.

Ø  The title & sub-title should be merged & centred.

Ø  Also show roughly where on the sheet you plan to put the chart, by doing these steps:

Ø  Select the range of cells in the table that are roughly where you will put the chart.

Ø  Use keyboard shortcut Alt+O,B to open the Borders and Shading dialog window.

Ø  Choose the Shading tab, then set the shading style to 15%.

Ø  Click OK.

(The area that shows where the table will go is now shaded.)

2.    Complete the specification plan on the page after the layout diagram.

Follow these guidelines as you fill out the plan:

Ø  Leave the alignment blank if you don’t intend to change the default alignment. E.g.,

(1) Excel left-aligns alphabetic text, so only state the alignment if you are planning to do something different such as centre or right-align.

(2) Excel automatically right-aligns numbers, so only state the alignment if you are planning to do something different such as centre or left-align.

 

 

 


Layout Diagram

 



 

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

Purpose of Spreadsheet:

 

Student Check

(ü)

Assessor Check

(ü)

Item

Font Type

Font Size

Alignment  / Borders / Number Format

Enhancement
(Bold / Italic, etc)

 

 

Title:

 

 

 

 

 

 

Subtitle:

 

 

 

 

 

 

“Name”

heading:

 

 

 

 

 

 

Other column headings:

 

 

 

 

 

 

Players’ names

 

 

 

 

 

 

Summary labels:

 

 

 

 

 

 

Summary numbers:

 

 

 

 

 

 

Scores & Balls Faced Numbers:

 

 

 

 

 

 

Calculated numbers

 

 

 

 

 

 

Features:

 

 

 

Constraints:

 

 

 

Colour Scheme
(if any):

 

 

 

 

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

HAVE YOU TICKED THESE OFF AS YOU COMPLETED THEM?

The West Coast Knightsspreadsheet complies with all formatting and features as briefed.

 

 

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

 

 

The spreadsheet is legible.

 

 

The spreadsheet printout is presented between margins on one A4 landscape page.

 

 

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

 

 

All calculations have been checked.

 

 

 

Task Two: Produce the Spreadsheet and Chart

1.    Using your Diagram and Specification Plan from Task 1 and the numeric details supplied in the brief, create the spreadsheet.  Apply all formatting as specified in your specification plan.  Adjust column widths to display all information clearly.  Remember to format all numeric values, including ones that are the result of calculations.

2.    Save and close the spreadsheet, naming it West Coast Knights

3.    In the space below describe the steps you took to save and name your spreadsheet

 

 

4.    Ensure your West Coast Knights file is closed.  Now locate West Coast Knights and reopen it, in the space below explain the steps you took to do this.

 

 

5.    In the Sally row, add the formulas to calculate runs per ball and the runs she would have scored if her batting improved by 10%.

6.    Fill these formulas down into the rows for the other players.

7.    Select the cell where the total runs figure will go, and enter a SUM function to  calculate the answer.

8.    Select the cell where the top score  figure will go, and enter a function to  calculate the answer.

9.    Select the cell where the average score  figure will go, and enter a function to  calculate the answer.

10.Create the chart as specified in the Brief, and position it in a location that looks balanced in relation to the spreadsheet labels and data.

Note: if you want to use Print Preview to see how the data and chart will look together on the printed page, click away from the chart first. If the chart is selected when you select Print or Print Preview, Excel will assume that you just want to view and print the chart.

Task Three: Modify the Spreadsheet

Sally

114

Fred

82

Josh

34

Nick

65

Anne

21

Simon

38

1.     Insert a new column in between the Balls Faced and Runs per Ball columns.

2.    The heading for this column is Minutes at Crease

3.    In the new column, enter the data that’s shown in the table at right. (Just the numbers, not the player names).

4.    Format the new values the same as the numbers in the Runs and Balls Faced columns.

Task Three: Evaluate the Spreadsheet

1.    Leave one blank row after the last row you’ve used in the spreadsheet, and type the label Check Total: in column A. You don’t have to specially format this label.

2.    In the neighbouring cell in column B, build a “deduct-backwards” formula to check that the Total Runs value you calculated is correct. Centre-align the resulting number, which should be 0.

3.    Use a calculator and check the first Runs per Ball calculation.
The calculator will display more decimal points that you see on the spreadsheet, but the value should be essentially the same. Write your calculator’s answer here.

 

 

4.    Check backwards and forwards between each line of in the top part of your specification plan and the spreadsheet, and make sure that the spreadsheet matches the plan.

·        Put a tick in the Student Check column on that line of the plan when you are sure the spreadsheet matches what that line of the plan says.

Note: you can copy and paste the tick from the Student Check (ü)column heading.

5.    Now look at the things to evaluate that are listed in the bottom half of the specification plan. Double-check that your spreadsheet meets all of those requirements.

·         Put a tick in the Student Check column on that line of the plan when you are sure the spreadsheet meets that line’s requirement.

6.    Print the spreadsheet to PDF. (Make sure that the PDF shows both the spreadsheet data and the chart).

7.    Save and close the spreadsheet.

What to Submit for Marking

 

Please submit the following files to your tutor for marking:

·         This pre-assessment document with your answers filled in.

·         Your West Coast Knights Excel spreadsheet file.

·         The PDF you created at Task Three, Step 6.

·         Also: your McWilly’s Wool Shop Excel spreadsheet file.

 

 

Subject Mathematics
Due By (Pacific Time) 08/20/2014 02:00 pm
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