Docstoc

Spreadsheets 3

Document Sample
Spreadsheets 3 Powered By Docstoc
					e-Quals – Spreadsheets - Level 3 Assignment - 043 Version 3.2

Candidate Instructions

Candidate Instructions
Assignment - 043 Version 3.2 Spreadsheets – Level 3

e-Quals – Spreadsheets - Level 3 Assignment - 043 Version 3.2

Candidate Instructions

Assignment 7262-03-043

Spreadsheets - Level 3

Candidates are advised to read all instructions carefully before starting work and to check with your assessor, if necessary, to ensure that you have fully understood what is required. You must, at all times, observe all relevant Health and Safety precautions. Time allowed: 6 hours Introduction This assignment is broken down into 5 parts: 1. 2. 3. 4. 5. A brief scenario is provided for candidates. Task A requires candidates to design the layout of a spreadsheet to provide invoices. Task B requires candidates to create and test the spreadsheet designed in Task A. Task C requires candidates to import information and summarise data. Task D requires candidates to find the repayment amount for a fixed term loan.

Scenario You have been asked to assist a small bakery called YUM YUMS, which specialises in wedding and other celebration cakes, by setting up a spreadsheet to provide invoices. Due to expansion, they are now producing approximately sixty cakes per month. Sometimes the wrong price has accidentally been typed on the invoice so the bakery owner would like you to set up an invoice that automatically takes the cake details from a lookup table. An invoice is required to show • company details: YUM YUMS, 25 London Road, Midhurst, West Sussex, RH8 4GH, Tel No 01203 447234 • suitable graphic image • invoice number • customer name and address • today’s date • code number for the cake • cake type • cake decription • cake size • quantity • cost of each line item • total invoice • TAX @ 17.5% • total of invoice plus TAX • any explanatory comments Task A 1. Start the spreadsheet application and open a new spreadsheet. 2. Import the comma delimited text file called PRICES into the first worksheet of the new spreadsheet. Rename this worksheet as PRICES and save the spreadsheet as BAKERY. Print a copy of this worksheet and label it PRINTOUT1.

1

e-Quals – Spreadsheets - Level 3 Assignment - 043 Version 3.2

Candidate Instructions

3. Produce a design specification to include • identification of the structure of the INVOICE worksheet including data labels, hidden or protected cells • identification of data input areas, with any validation • identification of formulas and functions to be used including any rounding or absolute cell references that may be required • definition of the printout required to include page size, orientation, multi-page or fit to page, headers, footers and any named ranges used • macro to be used to automate the printing of the invoice • position and size of graphical image • identification of formatting for data including alignment, enhancements, borders, shading and number Label this DESIGN1. 4. Design a data capture form for input data. Label this DESIGN2. 5. Create THREE sets of test data to be used to create an invoice. Calculate the expected results from the test data. Label this DESIGN3. Task B 1. Create the worksheet named INVOICE according to your design from Task A. 2. Improve and adjust the design as necessary. 3. Enter the test data created in Task A to test your invoice and correct any design or implementation errors. Print out a copy of EACH invoice. Label these PRINTOUT2. 4. Set the worksheet INVOICE to show formulae. Print a copy of the worksheet. Label this PRINTOUT3. 5. Produce a listing of any macros. Label this PRINTOUT4. 6. Produce user documentation for the spreadsheet to explain how to do the following: • log on to the computer • access software • enter data • save the file • print invoices • action to be taken on errors • describe the use of macro. • include an introduction giving a brief description of the spreadsheet and its purpose. Label this DOCUMENTATION.

2

e-Quals – Spreadsheets - Level 3 Assignment - 043 Version 3.2

Candidate Instructions

Task C 1. Import the comma delimited text file called SALES into a new worksheet and name this worksheet SALES. 2. Use a formula to insert the size of the cake for each cake using the number at the right of the cake number. 3. Use the sort facility to reorder the sales information on the SALES worksheet into ascending order of Inv No. Produce a printout of this worksheet. Label this PRINTOUT5. 4. Produce a printout of the SALES worksheet showing formulas. Labels this PRINTOUT6. 5. On the SALES worksheet, summarise the totals by Inv No and include a grand total. Print a copy of this worksheet. Label this PRINTOUT7. Task D 1. 2. 3. 4. 5. 6. Open the spreadsheet LOAN. The company have been offered a loan for £50,000 repayable over 10 years at an annual interest rate of 10.75%. Enter these figures in the appropriate cells. Enter a formula in cell B3 to display the monthly payments needed to repay the loan. Enter a formula in cell B9 to display the total repayments made over the 10 years. Print a copy of the spreadsheet showing formulas. Label this PRINTOUT8. Save the spreadsheet, close all files and close the spreadsheet application.

Note • At the conclusion of this assignment, hand all paperwork and disks to the test supervisor. • Ensure that your name is on the disk (if using a floppy disk) and all documentation. • If the assignment is taken over more than one period, all floppy disks and paperwork must be returned to the test supervisor at the end of each sitting.

3


				
DOCUMENT INFO
Shared By:
Stats:
views:38
posted:1/18/2010
language:English
pages:4
Description: Spreadsheets 3