office solution development

Unit 22 Office Solutions Development

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 process 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 ane-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 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

Case study
World Wide Publishing Co. Ltdpublishes 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 InvoiceOutput 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

    Office Solutions Development

Source Document2: Current Picking List Report

    Office Solutions Development

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.

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:

Office Solutions Development

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


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

Explore your knowledge and learning on Business Courses >>>

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 the importance of addressing both user and business requirements of World Wide Publishing Co. Ltd
LO2 Be able to design and implement office solutions
TASK 2   The objective for this task is to design and implement office solution. You are required to address the task as specified and research considering the given case study for task 2 and address the learning outcomes as specified:
A.C 2.1 Ddesign a solution to address a business or user needs of World Wide Publishing Co. Ltd
A.C2.2 use advanced tools and techniques to implement a solution for World Wide Publishing Co. Ltd
A.C2.3 test World Wide Publishing Co. Ltd solution against expected results.
LO3 Be able to demonstrate that business processes have been enhanced / improved
TASK 3: The objective for this task is to d demonstrate that business processes have been enhanced / improved .You are required to address the task as specified and research considering the case study assigned for task2 to address the learning outcomes as specified
A.C3.1 discuss based on World Wide Publishing Co. Ltd, ways in which end user engagement has taken place
A.C 3.2 provide evidence that World Wide Publishing Co. Ltd business processes have been enhanced/improved.
A.C 3.3 Evaluate possible further improvements that could be made to enhance the system