office solution development
follow the module booklet, and you can find the lecturer as well on the college website http://elearning.ukcbc.co.uk username : batch6 password: batch6.and write the references

Module Booklet

Unit 22 Office Solutions Development
Unit level 4
Unit code R/601/1971
Qualification BTEC HND in Computing
Term Start Date 22/02/2016
Hand out date 29/02/2016
Submission Deadline 15/07/2016
Lecture 15 Weeks
Revision Clinic 3 Weeks
Module Leader Gasso Wilson
Lecturer

CONTENTS
TABLE OF CONTENTS
1.0 INTRODUCTION 2
1.1 Aim: 2
1.2 Objectives: 2
2.0 Unit Content 2
3.0 Learning outcomes and assessment criteria 2
4.0Teaching plan for the term 2
5.0 ASSIGNMENT FRONT SHEET 2
5.1 ASSIGNMENT BRIEF SECTION 2

1.0 INTRODUCTION

Within an organization, a range of business processes are functional at operational, tactical and strategic levels within the business structure. Some business processes are quite simplistic, especially at the operational level where routine, non-complex decisions and tasks are undertaken. However as you advance through the levels of an organization, the strategic levels, more complex business processes and decision making is required that may need support from more automated, on-demand information systems and solutions.

Providing office solutions to support a range of business functions and decision making can range from the implementation of a simple rule or macro, through to the design of a more complex piece of code or program to support different end users.

This unit is designed to build upon existing skills and knowledge previously gained from using different types of application and design software. Learners will be expected to demonstrate more advanced skills in terms of data manipulation, configuration, application and implementation of software packages to enhance a given business processes or processes.

On completion of this unit the learner should be able to use design application software at an advanced level of proficiency. Learners will engage with a range of software, and select the most appropriate application(s) to offer feasible and working business solutions.

1.1 AIM:
To equip learners with the knowledge and skills needed to develop application solutions that can be used to automate business processes.

1.2 OBJECTIVES:
On successful completion of this unit a learner will:
1. Understand how application software can support business processes
2. Be able to design and implement office solutions
3. Be able to demonstrate that business processes have been enhanced/improved.

2.0 UNIT CONTENT:
1 Understand how application software can support business processes

Applications software: Types e. g, spreadsheets, database, presentation, graphics, desk-top publishing, word processing
Business processes: management e. g, supporting decision making, problem solving; operational eg sales, purchasing, Marketing;
Support eg accounting, technical Supporting processes:
Improving the efficiency of a business process e. g, forecasting, decision making, and predictive reasoning;
Automating processes e. g, print runs, salary slips supporting the user:
User requirements e. g, accessibility, usability, clarity, help

2 Be able to design and implement office solutions

Solutions: supporting a business process e.g, end user requirements, systems requirement, application to automate procedures, designing a tool, program or package that can perform a specific task to support problem-solving or decision-making at an advanced level, creating an e-Commerce function for a website to support a specific business process, designing a program for a specific end user that will support another application or process.

Design: considerations e.g., addressing the user or system requirement; user-friendly and functional interface; consideration of user engagement and interaction with the designed solution; customization of the solution to satisfy the user and system requirements Advanced tools and techniques: tools e.g. analysing data with interactive PivotTables and Pivot Charts, linking Excel spreadsheets with SharePoint, team collaboration and document management with Windows SharePoint; techniques e.g. creating decision-making macros, programming Access objects, building intuitive user interfaces with VBA, building ASP.net web applications Testing:
Functionality: e. g, checking against requirements, error handling, documentation

3 Be able to demonstrate that business processes have been enhanced/improved

User engagement: engagement through e. g, meetings, questionnaires, interviews Enhanced/improved:
Comparisons e. g, more efficient, faster results, more user friendly, improved compatibility with other systems and processes, improved management information
3.0 LEARNING OUTCOMES AND ASSESSMENT CRITERIA

Learning outcomes

LO1 Understand how application software can support business processes
1.1 discuss ways in which applications software can support business processes
1.2 justify the use of different application software to support a given user requirement or business process
1.3 discuss the importance of addressing both user and business requirements

LO2 be able to design and implement office solutions
2.1 design a solution to address a business or user need
2.2 use advanced tools and techniques to implement a solution
2.3 test a solution against expected results

LO3 be able to demonstrate that business processes have been enhanced/improved
3.1 discuss ways in which end user engagement has taken place
3.2 provide evidence that business processes have been enhanced/improved
3.3 evaluate possible further improvements that could be made to enhance the system.

4.0 TEACHING PLAN FOR THE TERM
Accrediting Body: EDEXCEL
Accrediting Body: EDEXCEL
Course: BTEC HND IN Computing and Systems Development
Unit: 22. Office Solutions Development
Wk Lecture Schedule Outcome of session Activity/seminar and formative assessment Resources
1
Introduction to Office Solution Development.
Introduction to the module, mode of assessment, awareness of the awarding body, assessment criteria and the mode of assessment.
Role play and individual induction, helping to know each other.
Module handbook.

2
Applications software: types eg spreadsheets, database, presentation, graphics, desk-top publishing, word processing
By the end of the session you will be able to discuss ways in which applications software can support business process.. Spreadsheets, database, presentation, graphics, desk-top publishing and word processing software’s in business context.
Review of Task P1.1
Case study, tutorial and addressing the learning outcome 1.1
Activities-
Research- Identify the features of spreadsheets, database, presentation, graphics, desk-top publishing and word processing software.

Power point slides from: Heathcote P, MSN Tutorial,
.

3
Business processes: management eg supporting decision making, problem solving; operational eg sales, purchasing, marketing; support eg accounting, technical
By the end of the session Learners will be able to justify the use of different applications software to support given user requirements. Case study, tutorial and addressing the learning outcome 1.2
Activities -Research
How applications software such as word processing, spreadsheets, graphics, supports business.
Power point slides from: Heathcote P, MSN Tutorial,

4
Supporting processes: improving the efficiency of a business process eg forecasting, decision making, predictive reasoning; automating processes eg print runs, salary slips
Learners’ will be able to understand the following:
• Business Process
• Decision making
• Forecasting, salary slip.
Review of Task P1.2
Case study, tutorial and addressing the learning outcome 1.3
Research-Activity
Select a company and identify and explain how business process are supported by the application software such as word processing ,spreadsheets etc. Course Notes,

5
Supporting the user: user requirements eg accessibility, usability, clarity, help Learners will be able to understand the business and user requirements.
Review of Task P1.3 Tutorial using MS Excel and recap the learning outcome 1.1, 1.2 and 1.3 Course Notes, Practical
6

Be able to design and implement office solution

Solutions: supporting a business process eg end user requirements, systems requirement, application to automate procedures, designing a tool, program or package that can perform a specific task to support problem-solving or decision-making at an advanced level, creating an
e-Commerce function for a website to support a specific business process, designing a program for a specific end user that will support another application or process
Learners’ will be able to understand the business process and end user requirements and identify the suitable solution to support business process for decision making.

Case study and tutorial and address the LO 2.1
Research activity
Based on the case study given identify the requirements of the users and advise them how application software can support specific task such as problem solving, decision making etc.
Course Notes, Practical

7
Design: considerations e.g. addressing the user or system requirement; user-friendly and functional interface; consideration of user engagement and interaction with the designed solution; customization of the solution to satisfy the user and system requirements
Learners’ will be able to prepare spread sheet for business requirements such as budget, financial report, salary slip using MS Excel according to user requirements.
Review of Task P2.1
Case study and tutorial MS Excel 2007 and address partly the LO 2.2
Activity – Prepare a spreadsheet model for enter staff details, hours worked etc and to calculate salaries. Course Notes, and Lab Practical, online video tutorial.

8
Advanced tools and techniques: tools e.g. analyzing data with interactive PivotTables and Pivot Charts, linking Excel spreadsheets with SharePoint, team collaboration and document management with Windows SharePoint; techniques eg creating decision-making macros,
programming Access objects, building intuitive user interfaces with VBA, building ASP.net web applications
Learners’ will be able to crate pivot tables and chart, pie and bar chart from the table to support to make business decision.
Review of Task P2.2 Tutorial and address the LO 2.3.
Activity- Prepare a spreadsheet for enter sales data, calculation of vat and total sales and analysis the sales data and produced sales data in a chart Course Notes, and Lab Practical, online video tutorial.

9
Advanced tools and techniques: tools eg analyzing data with interactive PivotTables and Pivot Charts, linking Excel spreadsheets with SharePoint, team collaboration and document management with Windows SharePoint; techniques eg creating decision-making macros,
programming Access objects, building intuitive user interfaces with VBA, building ASP.net web applications
Learners’ will be able to crate pivot tables and chart, pie and bar chart from the table to support to make business decision.
Review of Task P2.3 Tutorial and address the LO 2.3.
Activity- Prepare a spreadsheet for enter sales data, calculation of vat and total sales and analysis the sales data and produced sales data in a chart Course Notes, and Lab Practical, online video tutorial.

10
Testing: functionality: eg checking against requirements, error handling, documentation Learners’ will be able to produce the test plan and test outcomes
Review of Task P3.1 Tutorial and address the LO 3.1
Activity –Produce a test plan according to the requirements and show evidence of the spreadsheet model is working. Course Notes, and Lab Practical, online video tutorial.

11
Testing: functionality: eg checking against requirements, error handling, documentation Learners’ will be able to test the data and handle the error on created application solution and be able to create documentation for business use.
Tutorial and address the LO 3.1
Activity –Produce a test plan according to the requirements identified in task 2 and show evidence of the spreadsheet model is working. Course Notes, and Lab Practical, online video tutorial.

12
Able to demonstrate that business process have been enhanced/improved

User engagement: engagement through e.g. meetings, questionnaires, interviews
Learners’ will be able to understand the importance of user involvement and the ways to engage the user with the software solution.
Review of Task P.3.2 Tutorial and address the LO 3.2
Research – Why it is important of user involvement and the ways to engage the user with the software solution such as interviews and observations. Course Notes, and Lab Practical, online video tutorial.

13 User engagement: engagement through e.g. meetings, questionnaires, interviews
Learners’ will be able to understand the importance of user involvement and the ways to engage the user with the software solution.
Tutorial and address the LO 3.2
Research – Why projects failure? Does it matter to identify the user requirements when design a new system? Course Notes, and Lab Practical, online video tutorial.

14 Enhanced/improved: comparisons e.g. more efficient, faster results, more user friendly, improved compatibility with other systems and processes, improved management information Learners’ will be able to understand, how the software solution will enhance and improve the business process with better information management. Case study and tutorial and address the LO 3.3
Research – based on the case study task 2 identify and describe how the model improves the business process of the company. Course Notes, and Lab Practical, online video tutorial.
Research activity-
15 Enhanced/improved: comparisons e.g. more efficient, faster results, more user friendly, improved compatibility with other systems and processes, improved management information Learners’ will be able to understand, how the software solution will enhance and improve the business process with better information management.

Review of Task P3.3 Case study and tutorial and address the LO 3.3 Course Notes, and Lab Practical, online video tutorial.
Research activity-
16 ASSIGNMENT SUPPORT AND ADVISE
17 ASSIGNMENT SUPPORT AND ADVISE
18 REVISION AND ASSIGNMENT SUBMISSION

Recommended text and links
Books
Heathcote P – Successful ICT Projects in Excel (Payne Galway, 2002) ISBN-10: 1903112710
Jellen B – Brilliant Microsoft Excel 2007 VBA and Macros (Brilliant Excel Solutions) (Prentice Hall,
2007) ISBN-10: 0273714058
Jellen B – Excel VBA and Macros with Excel (Video Training) (Live lessons) (QUE, 2009)
ISBN-10: 0789739380
Rendell I, Mott J – Advanced Spreadsheet Projects in Excel (Hodder, 2008) ISBN 0340929243

Websites
http://spreadsheets.about.com/od/advancedexcel/Advanced_Topics_in_Excel_Spreadsheets.htm
www.ehow.com/topic_2573_advanced-excel-tutorial.html

Wk Session Update Date Signature
1

2

3

4

5

6

7

8

9

10

11

12
13
14
15
16
17
18
Lecturer: Gasso Wilson
Campus: Wentworth house
Contact details: [email protected]

Assessment:
The module will be assessed meeting all the LO as specified by the awarding body, Ed Excel.
Please read the instructions carefully while addressing the tasks specified.
Contribution: 100% of the module
Outline Details: Individual report approx 4000 words. Details enclosed in the assignment brief.

Teaching and Learning Activities
The module tutor(s) will aim to combine lectures with tutorial activities. This environment will provide opportunities for the student to understand the course material through case study and text and to apply it in a practical way. The intent is to facilitate interactive class activities, and discussion about the significant role of research in a global and local business environment.
2.3 Teaching Ethos
The college’s approach towards teaching and learning is simple and effective. The main aim of UKCBC is to assist learners in maximising their potential by ensuring that they are taught clearly and effectively. This will enable students to engage in the learning environment and promote success in both their academic studies and subsequent career.

2.3.1 Methods of Delivery:
LECTURES:
These will be developed around the key concepts as mentioned in the indicative course content and will use a range of live examples and cases from business practice to demonstrate the application of theoretical concepts. This method is primarily used to identify and explain key aspects of the subject so that learners can utilise their private study time more effectively.
SEMINARS:
These are in addition to the lectures. The seminars are designed to give learners the opportunity to test their understanding of the material covered in the lectures and private study with the help of reference books. This methodology usually carries a set of questions identified in advance. Seminars are interactive sessions led by the learners. This method of study gives the learner an excellent opportunity to clarify any points of difficulty with the tutor and simultaneously develop their oral communication skills.
CASE STUDIES:
An important learning methodology is the extensive use of case studies. They enable learners to apply the concepts that they learn in their subjects. The learners have to study the case, analyse the facts presented and arrive at conclusions and recommendations. This assists in the assessment of the learner’s ability to apply to the real world the tools and techniques of analysis which they have learnt. The case study serves as a supplement to the theoretical knowledge imparted through the course work.
Plagiarism:
Any act of plagiarism will be seriously dealt with according to the colleges and awarding bodies’ regulations. In this context the definition and scope of plagiarism are presented below:
Plagiarism is presenting someone’s work as your won. It includes copying information directly from the web or books without referencing the material; submitting joint coursework as an individual effort; copying another student’s coursework; stealing coursework form another student and submitting it as your own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the college procedure. (For further details please refer to the plagiarism policy and the student code of conduct.)

Assessment:
The module will be assessed meeting all the LO as specified by the awarding body, Ed Excel.
Please read the instructions carefully while addressing the tasks specified.
Contribution: 100% of the module
Outline Details: Individual report approx 4000 words. Details enclosed in the assignment brief.

5.0 ASSIGNMENT FRONT SHEET
Unit: 22 – Office Solutions Development
Learning Hours: 45 Hours. Self-learning hours: 90 hours. Course: HND CSD – Level 4 CREDIT VALUE: 15 CREDITS Term:
Module Leader: Gasso Wilson Lecturer: Internal Verifier: Raj Kanapathi
ASSIGNMENT TITLE: Office Solutions Development

Guidance in addition to the Pass criteria’s, this assignment gives you the opportunity to submit evidence in order to achieve the following Merit and Distinction grades. Assessment Grid indicating their pass, merit and distinction
Unit: 22- Office Solutions Development

Outcomes/criteria: Indicative characteristics Possible evidence Contextualisation
In this assessment you will have the opportunity to present evidence that shows you are able to : Feedback
LO1-Task1. Understand how application software can support business
processes

LO2-Task 2. Be able to design and implement office solutions

LO3.-Task 3 Be able to demonstrate that
business processes have been
enhanced/improved
As per the assessment criteria specified for pass
Do

Do

Addressing 1.1,1.2 and 1.3 as instructed
Addressing 2.1,2.2 and 2.3 as instructed

Addressing 3.1,3.2 and 3.3 as instructed
Merit Description: Contextualisation

M1. Identify and apply strategies to find appropriate solutions. An effective approach to study and research has been used
.. To achieve M1 an effective approach of study with good research skills being used across the assignment to address the criteria’s with reference to the given case study.
M2. Select/ design and apply appropriate methods/ techniques. . A range of methods and techniques has been applied. To achieve M2, you will have used range of methods and techniques have been applied with special reference to task2.

M3. Present and communicate appropriate findings. . Appropriate structure and approach has been used. To achieve M3 the work should be appropriately structured addressing the Learning outcomes as specified with appropriate findings with special reference to given case studies across the assignment and Technical language accurately used with special reference to task 2 design and implement a system for the given requirements.
Distinction Description:
D1. Use critical reflection to evaluate own work and justify valid conclusion. . Synthesis has been used to generate and justify valid conclusions
. To achieve D1 you will have addressed the Criteria’s with valid conclusions, justifying improvements and characteristics of success. For example how applications can support business process for the given case study.
D2. Take responsibility for managing and organizing activities. Autonomy/independence demonstrated To achieve D2 you will have demonstrated autonomy across your assignment, with effective independent research and met the deadlines to submit all the tasks and achieve the unit assessment criteria.
D3. Demonstrate convergent, lateral and creative thinking Ideas generate and decisions taken To achieve D3 the learner demonstrates new ideas or suggestions for example task 3 for the given case study and your writing demonstrate research and shows some creative thinking across the tasks.

5.1 ASSIGNMENT BRIEF SECTION
ASSIGNMENT
Unit Number and title Unit 22/OFFICE SOLUTIONS DEVELOPMENT
Qualification BTEC HND Computing (Level 4)
Term Start Date 22 February 2016
Assignment hand out date 29 February 2016
Deadline/ Hand-in 15 July 2016

Case study
World Wide Publishing Co. Ltd publishes road Atlases which are sold by three different methods:
1. To wholesalers, who in turn sell them on to retailers (Bookshops etc.)
2. To retailers such as bookshops, service stations, shops specialising in Car accessories;
3. To individual members of the public who order directly by phone or mail in response to advertisement.

World Wide Publishing Ltd uses a specialised Accounts package to enter orders, prepare invoices and keep records of Customer accounts. Each day they accept orders by phone, fax, email or regular mail and prepare invoices accordingly. There are normally about 5-15 orders each day. These invoices together with an extra copy of each one, are then mailed the same day to Oxford Warehouse, a separate organisation which stores large quantities of books for hundreds of different publishers.

Oxford warehouse packs and dispatches the books, enclosing one copy of the invoice with the books and keeping the other copy for their records.
A “Picking List” report has to be enclosed with the invoices sent daily to Oxford Warehouse, telling them the total number of each title that has to be dispatched that day. The Warehouse covers several acres and a forklift truck driver has to be sent to collect the correct number of books from the specified bins and take them to the dispatch area where they are packed and collected for delivery by Securicor.

The end of each month World Wide Publishing Ltd receives an invoice from Oxford Warehouse for their services, based on the sales value of books dispatched that month.
The Current System
The user (Mrs. Joanne Bernard, the owner of World Wide Publishing Ltd) was interviewed to ascertain how the current system of producing Picking Lists works, and exactly what are the requirements of the new system.
Mrs. Joanne Bernard does not want any changes to the current method of recording customer orders and printing invoices using the software package sage sterling. However, the Picking List as described above cannot be generated by this system in the format required by Oxford Warehouse, and producing this by hand is time-consuming and prone to error.
At present the Picking List is prepared by adding up the total quantity of each book from invoices and entering the figures on to a photocopied form. Sometimes the Sales clerk uses a spreadsheet as a calculator just to make sure the addition is correct. But there is no set method of preparing the report. Mrs. Joanne Bernard would also like to be able to get instant information on total monthly sales, which is not available from Sage Accounts system in the format in which she would like it. Samples of an Invoice Output from Sage but used as input document for the Picking List, and a copy of the Picking List are as shown below:

Source Document1: Invoices

Source Document2: Current Picking List Report
Objectives of the new System:
The new System should be able to perform the following:
• Allow data from the invoices (Quantity of each book and total invoice value) to be recorded quickly and accurately;
• Calculate totals and produce a picking list from this automatically
• Produce monthly sales summaries
• Produce a chart showing monthly sales over the past year, and indicating predicted sales based on these sales figures
• The system must be robust and easy to use
You have been appointed as a Systems Analyst and Software Developer.
For the purpose documentation you are required to adhere to the following procedure:
1. You must draw a Context Diagram preceded by a table of catalogue of requirements
2. You must draw a Data Flow Diagram (Level 1) outlining the process of ordering, invoicing and dispatching books
3. On this DFD(Level 1) clearly show the boundary of the system with dotted lines
4. Implement the system for the given case study.
5. Include performance indicators as follows:
• It should not take longer than 30 seconds to enter each invoice
• Data entry should be made so simple that the resulting Picking List is 100% accurate
• The Picking List will be produced in one operation by the user – for example selecting from a menu or clicking a button on a customised toolbar
• The monthly sales summary and chart will be produced in a similar way, using a menu item or button.
• The system must cater for any number of titles to be added at a future date as the company expands
• It must be impossible to accidentally erase formulae, headings etc..
• The System should be easy to use for anyone with rudimentary knowledge of Excel
Based on your analysis of the requirements, you should be able to recommend the type of computer system to use both Hardware and Software.
There are two users of this system namely Mrs Joanne Bernard who is not involved in the Data entry and Mrs. Nicholson who will be extensively involved in entering data together with a new recruit, James Sunday.
In terms of the choice of software, you are restricted to using Excel and MS ACCESS and MS Word.

Worksheet Design
The System will be based on two workbooks:
1. A Products workbook containing details of all products
2. A Template containing a number of separate worksheets for data entry, reports and summaries( Detailed design next)
At the beginning of each year the user will be able to open a new workbook based on this Template and enter daily invoice data which will be accumulated day by day over the year. Reports and charts will be produced from this accumulated data by means of options on a Custom Menu stored in the template. In addition a front end menu will enable the user to choose which task they wish to perform e.g., enter data from invoices, print a monthly sales summary or chart. Data to be entered include product number, title, and Bin number and for invoice data this will include date, invoice number, quantity, sales etc.

The application Template will contain 5 sheets as follows:
Sheet1 – Menu: This sheet will act as a front end and will be selected automatically using an autoexec macro when the workbook is loaded. It will have 4 Options: Enter Invoices, View Picking List, Monthly Sales Summary, and Exit

Sheet2 – Invoices: This is where the daily invoices will be entered

Sheet3 – Pick List: This sheet will take the form of the Picking List which will have formulae linking it both to the World Wide Publishing Ltd Atlas Products workbook for the product titles and the Bin numbers, and the Invoices worksheet from where it will get the Totals.
It will be as follows:

Sheet4 – Sales: This will contain all the daily Sales amounts for the current year. It will have just two columns:

Date Amount

The day’s data will be automatically added to this list when the user presses Add to monthly sales button on Invoices sheet

Sheet5 – Monthly: This sheet will hold a PivotTable that will show year-to-date monthly sales. The user can create the PivotTable report by selecting the option monthly sales summary either from a custom menu (Atlas) on the menu bar or from the front end menu. This runs a macro called Monthly Pivot which groups sales totals by month.

The user can produce a chart and trend line from these figures using the standard chart button.
The following are suggested macros that you are required to create:
1. Insert Product
2. Enter Invoices
3. View Picking List
4. Add To Monthly Sales
5. Monthly Pivot
6. Auto-Open(Runs automatically when the Atlas workbook is opened)
7. Auto-Close(Runs automatically when the Atlas workbook is closed)
8. Transfer data to ACCESS Database
9. Mail Merge ACCESS data with Standard Letter in MS Word to send to Customers
10. Make sure to use advanced features such as VLOOKUP and HLOOKUP in your data manipulation

Finally:
1. Test your System
2. Write user and Technical manuals
3. Write evaluation and finish all documentation.

TASK 1 to TASK 3: You are required to follow the instructions as specified towards each task and support with research by using supportive materials like books, websites, etc., and give feedback on the findings by relating your arguments to the relevant case study as specified towards each task. Answers without reflecting the case study practice will be referred. Your report should fully follow the Harvard Referencing model.

LO1 Understand how application software can support business processes

TASK 1: The objective for this task is to address and exhibit an understanding of how application software can support business process. You are required to address the task as specified and research by using supportive materials like books, websites, etc to address the learning outcomes as specified: Please note that the research and subjective skill needs to exhibit transferable skill by reflecting it to the chosen Case Study “World Wide Publishing Co. Ltd.”. Answers without reflecting the case study practice will be referred.

A.C 1.1 Discuss ways in which applications can support business processes of World Wide Publishing Co. Ltd
A.C1.2 Jjustify the use of different application software to support a given user requirement or business process of World Wide Publishing Co. Ltd
A.C1.3 Ddiscuss