Title: Produce and use computer spreadsheets for business use Unit Number: 2785
Level: 3 Credits: 5
Student Name Class Code
Element / Question / performance criteria / First attempt / Resubmission1.1 / P2/1 / A brief is formulated that identifies the organizational requirements of the spreadsheet in terms of purpose and target users.
1.2 / Part 2 / The brief outlines the requirements (including constraints) to be met by the spreadsheet for it to provide a solution.
1.3 / P2/2 / A working plan, which includes an outline of the spreadsheet specifications and/or features required to provide a solution, is developed to realize the brief
2.1 / P1/1,2,3,4, / Data is entered and is formatted to produce the specified spreadsheets in terms of cell width, alignment, text, number, and date and time formats
2.2 / P1/5,6,7,8,12
P2/6 P1/9,16 P1/15 / Spreadsheet formulae are used to produce the specified spreadsheets in terms of: statistical, financial, and logical functions; time and date calculations; sorting; and cell referencing.
2.3 / P1/18 / Templates are created, used, and documented to meet the specifications.
2.4 / P1/15 / A macro command is created, used, and documented to meet the specifications.
2.5 / P1/14 / Cell ranges within the spreadsheets are graphed to meet the specifications
2.6 / P1/1, 11, 13
P2/4 / Data integrity practices are demonstrated in terms of comparison with original information sources, audited formulae, and check-totals.
3.1 / P2/3 / The spreadsheet is evaluated for compliance with the brief, appropriate formatting, readability, legibility, presentation, accuracy, and data integrity.
3.2 / P2/7 / The spreadsheet is modified if required and compliance with the brief is confirmed
4.1 / P1/15 / A simple user document is created for the spreadsheet.
Conditions
· Read the instructions carefully before you attempt the questions.
· Tick off each question as you complete it and do not leave any question unanswered.
· Make sure you name is on all sheets to be printed out.
Part 1
The Problem
Healthier Hens Hen Farm want some statistical data calculated from raw data they have collected on the perfor
mance of 20 of their hens over an 8 week period. The data is shown in the table below.
1. Enter the data from the table above and check that each entry is correct. (pc 2.1) o
2. Wrap text within cells to prevent having excessively wide columns. (pc 2.1) o
3. Adjust columns so that all data is visible. (pc 2.1) o
4. Format cells so that you can easily distinguish between data and formulae. (pc 2.1) o
5. Find the total number of eggs laid by each hen over the eight-week period by summing each row.
(pc 2.2) o
6. Find the average number of eggs laid by each individual hen over the eight week period. (pc 2.2) o
7. Find the average number of eggs laid by each hen in the last two weeks (week 7 and week 8).
(pc 2.2) o
8. Calculate the number of eggs laid each week. ie Week1, Week2… etc. (pc 2.2) o
9. Calculate the revenue (money) earned each week from the sale of eggs assuming the price per egg is 30c (Use an absolute cell reference in this calculation so that this value can be easily changed). Format these cells to two decimal places.(pc 2.4) o
10. The egg farmer wants to cull (remove) the hens that are not laying well. The spreadsheet is to have ”Yes” returned if a hen is to be culled and ”No” if it is to be kept.
A hen is to be culled (removed) if it lays an average of less than 4 eggs over the 8-week period and also lays an average of less than 5 eggs over the last two weeks. (pc 2.3) o
11. Using the Total Number of Eggs row (Q8) Create audited formulae to reduce to 0 to check that your date is correctly entered (pc 2.6) o
12. Produce a table to show the following: (pc 2.2)
a) current price of eggs which is to be $0.30 o
b) number of eggs laid in the week that most eggs were laid. o
c) number of eggs laid in week that least eggs were laid. o
d) number of hens to be culled. (If necessary use Help on using “CountIf”) o
e) total revenue earned from eggs over the 8 week period. o
13. Check your formula by manually checking which hens should be culled.
Place a tick P beside your calculated value if it is correct and show all the working for you manual calculations. (pc2.6) o
14. Create a line graph as a new sheet, to show the total weekly egg production over the 8 weeks. (pc2.5)
In creating the chart you must:
a) Set the minimum y axis value to 75 o
b) Have no legend showing o
c) Show major grid lines for both the y and x axes o
d) Add a suitable title o
e) Label the y axis o
15. Create two macros which can to be called by clicking on buttons placed on the worksheet.(pc 2.4)
The macros are to:
a) sort the hens into descending order based on production (Highest to lowest producing hen) o
b) return the hens to the original order that they were entered. (Hen 01, Hen 02 etc) o
c) Write instructions to use your Macro. (4.1) o
Remember to back up your spreadsheet before creating your macros. You may wish to create another column ranking the hens from 1 to 20 to easily return the sort.
16. The hen farmer wants to take out a loan for $40,000 to build more hen houses. He visits two banks and gets the following terms:
a) a 15 year term at an interest rate of 8.6%.
b) a 20 year term at an interest rate of 7.9%.
c) Use a financial function to calculate the amount payable each month to service the loan, assuming constant payments, a constant interest rate and full repayment of the loan. You may use “Help” on PMT to find out how to use this function. (pc 2.1, pc 2.4) o
d) From your calculations above which loan would give the best deal. Explain (P.C 2.11) o
17. Create a new folder within your existing Assessments folder named Spreadsheet. Save your workbook in this folder as Assess2785COM7<class code> <Given name Family name>
eg Assess27857COMYg1 Fred Smith or Assess27857COMDx7 Jayne Green o
18. Remove all the data but not formulae and labels from the file Assess 2785 and save it as a template file named TAssess2785COM7<class code> <Given name Family name> (pc 2.3) o
19. Preview and print your worksheet. o
20. Preview and print your graph. o
21. Modify your worksheet to show formulae (Tools è Options, Click the View tab, and click Formulas check box), adjust column widths to best fit the data, preview and then print this sheet. o
Part 2
Terrific Television and Sound Repair Service
Problem
Terrific Television and Sound Repair Service require their repair charges to be calculated using a spreadsheet. They wish to calculate all charges for the electrical repair work they carry out.
Terrific Television and Sound Repair Service billing system is required to calculate charges on the following basis: (pc 1.2)
· A call out charge of $40 is made. This is a fixed payment before any work is done. o
· A two-dollar travel charge is made for every kilometer over 12 km. o
· Parts used in the repair are added to the bill. o
· GST at 12.5% is calculated and added to the final account. o
· A reduction of 6% is made for prompt payment. This is when the payment is made within 14 days. (includes the 14th day) o
· Any of the above data must be able to be changed simply by changing values in a table. Use absolute values in cell references where necessary. o
· The time on the job is to be recorded to the nearest 1/4 hour. o
· A date formula must be used in your calculation for the discount for prompt payment. o
The Brief
1. Explain why you would use a spreadsheet to solve this problem. Include a brief discussion of the features of spreadsheets that will be used in your solution. (pc 1.1)
______
______
______
______
______
______
______
______
2. The Working Plan. You are to design your spreadsheet on paper. In your design, you are to:
a) plan the layout including headings, labels and cell formats. For example Title – 12pt Arial Bold Centered List these formatting with your plan.
b) plan all of the formulae or functions you will use.
c) Plan where you will use relative and absolute references (PC 1.3)
Use the guide over the page.
Item / Format / Student Check(ü)
Purpose of Spreadsheet / To display and calculate the past months income for each employee.
Title Format: / Centred across columns, Arial font, 14, Bold.
Subtitle Format: / Centred across columns, Arial font, size 12, bold
Column headings Format: / Arial, size 10, bold, centred (Text Wrapped)
Category labels Format: / Arial size 10
Numeric values Format: / Comma with 2 decimal places, Arial, size 10.
Totals: / Arial size 10, bold, with single top and double bottom border.
3.1 / The spreadsheet complies with all formatting as briefed.
All information including headings, labels, values and calculations make readable sense.
The spreadsheet is legible.
The spreadsheet printout is presented evenly between margins on one A4 landscape page.
All information, including text, numeric values and formulae are accurate.
All calculations have been manually checked using a calculator and all are correct.
3.2 / Where modifications/corrections have been necessary, these have been made and the spreadsheet reprinted correctly.
Create the spreadsheet that you have planned and documented.
3.
Enter the trial data given below for the year 2002 (3.1)
4. Do a manual calculation (Calculator and paper) using your trial data for Mr Biggs and show all your working. Check that this is the same result you obtained from your spreadsheet. (PC 2.6) o
5. Save your worksheet under the file name “Your name” TV project o
6. Print two copies of your worksheet, one with formulae displayed and one without. Make sure that your printout is appropriately positioned on the page. o
7. If the Hourly charge increases to $75 and the callout fee is reduced to $35, What would be the total cost for each of the clients. Print out your answer. (PC 3.2) o
8. Check that the Final spreadsheet (from Q6) conforms to your Brief. (3.1) o
Declaration:
I declare that this is entirely my own work and that I have not copied or obtained help from any other person to complete this project.
Signed: ______
Page 2 of 4