Unit 6 - Advanced Spreadsheet Skills

2.7 Buying

This department’s responsibility is to call up local suppliers to find out which products are available for the next few weeks of sales. The hold meetings to decide what produce will go in each box they do this twice a week. They pass their decisions on to the marketing department who will then advertise the new boxes available. The department currently employs 5 staff.

2.8 Sales and Marketing:

This department must make sure that customers are aware of the products available they do this by emailing customers, updating the website and simply calling the major customers direct they must produce leaflets and other materials like pictures so that Lesco can advertise the boxes and get a glimpse of there origin. The team employ 5 staff members.

3. Suppliers:

All the produce is supplied locally from farmers and growers who have to provide evidence their there crop is grown to organic standards. For this to happen they have to be subject to regular rigorous checks every week. The have to keep in contact with the buying department to inform them what produce is available and in return the department will provide a delivery date for the products to be collected.

4. Customers:

The customers were originally mainly locals however it has grown to encompass primary schools, care homes, local shops and supermarkets in the area. Lesco has a huge buying power and demand a special lower prices, a constant supply of produce and marketing information so that they can publish it on the website weekly.

5. Products:

The company supplies boxes of organic fruit and vegetables in a range of boxes all retailing at different prices. The range goes from small for £10, medium for £15, large for £20 and luxury for £25. All prices include UK delivery Lesco get a lower price which is small for £8, medium for £10, large for £14 and luxury for £19.

6.  Requirements:

The management team have given access to the following tables:

• Produce Details
• Order Details
• Sales Figures for the last 2 Years
• Customer Details

With this data the management team want the new system to be capable of the following:

• Enable the creation of invoices
• Automatically transfer invoice data to a summary sheet
• Create pivot tables, charts and graphs
• User friendly
• Be easy to navigate around the system
• Provide different types of prints

They also want the system to be capable of calculating the following:

• Total monthly sales
• Average sales per month
• Maximum and minimum sales per box size
• Monthly income per box size
• Annual income
• Profit and lose

The spreadsheet will consist of the following worksheets:

• Main Menu
• Invoice page
• Customer Details
• Product Details
• Order Details
• Summary Page

7. Different Worksheets

These worksheets will have the different types of data and functions within them, there are sets requirements to which all worksheets must abide by.

7.1 Main Menu:

The Main Menu must contain links to all pages and these must be clearly seen, these will be created through the use of macros. It must also have relevant colours with the company logo visible. The workbook must have appropriate titles and labels so can be easy navigated. The date must also be visible this is very simple to do by inputting the code “Date()”. Macros work by recording actions so that when clicked the spreadsheet will repeat these actions. Finally there must be an exit button so the spreadsheet can easily be exited.

7.2 Summary:

This page must contain all the information regarding sales and profits. It must also say how much the company has made and how much of that amount has been paid the amount left to be paid. This information will be imported from other workbooks this will be done by VLookUps. This page will be out as a table and will contain the customer’s ID number, the amount of products sold individually and overall it must also contain the amount of money the company has made. From this data there will be 3 pivot tables created and then some further charts and graphs. The date must be present on the page. To keep the spreadsheet consistent throughout the colours will be similar and the logo will appear in the same place on each individual workbook. The formulas used in this workbook are VLookUps, what if statements and logical formulas like sum, min and max.

7.3 Invoice:

This will have the same theme as the other workbooks. It will contain data on the customers, the products purchased, date of order, pay by date and their payments. The invoice will be printable so that it can be sent out along with the products ordered, this will be done as a macro and it will also have a save button and transfer button. It will contain the total cost including Totals, Sub-totals and VAT, it will also have a pay by date so that the customers are aware when they have to pay. It will contain the customer’s details such as address, name, town and postcode. This workbook will contain logical functions such as sum, min and max also absolute cell references and relative cells. For customers to easily find the most important information there will be different styles and fonts in use to highlight a specific area. We will do this by bolding or changing text to italics, there may also be need for borders and different shading. There is some information on this worksheet that may need protecting if any unauthorised person were to gain access to the spreadsheet. To do this they could use the hide and protect function which will hide any data selected and protect it with a password.

7.4 Customer Details:

This workbook will have a large data set containing information on the customers from there names to there email addresses. Also it will have data types meaning that some fields will only be able to contain certain information such as text or numbers. If a character is entered incorrectly then an error message will appear. This information will relate to the other sheets.

7.5 Products Details:

This workbook will have a large data set containing information on the products this will be in a table format. Like the other worksheets this one will have the same colours and styles. This worksheet will contain information on all the products and must be updated when necessary.  It must have a save button on in case a new product needs adding. Also it must have a print button so that staff and customers may get copies of the products and finally a user must be able to navigate away from this page so a menu button will be needed.

7.6 Order Details

This workbook will have a large data set containing information on the orders taken. It will be possible to see how many orders have been taken within any specific time or date. Also it will be possible to see which customers have ordered and what they have ordered. This information will all be imported from other worksheets. It will contain all of the logical functions and relative cell references.

7.7 Sales Figures

This workbook will have a large data set containing information on the sales figures set back a number of years. This includes the profits and loss these can also be displayed using charts and graphs. This will be used to accounts and finance department to see how the business is doing and also to create new what if scenarios. This work sheet will also need to have protection on it to prevent unauthorised access.

8. Implementation of the new system:

The first thing the system needed was to be design. The system designs have been done by looking closely at the requirements and of how Eden Farm’s old system functions. Several storyboards have been created. These designs now need to be transferred into excel. The sections above show what each workbook must contain.

8.1 Excel Tool Bar:

8.2 Macro Tool Bar:

To create a macro first the user will need to be on the workbook the macro will start from. Then the user will need to click on tools, macro, record new macro e.g.

        

After this is done the user will then need to assign this macro to a button. The eden Farm Spreadsheet contains many macros some of which are on the main menu which can be seen below.

This is the main menu of my spreadsheet it contains simple macros which deliver a means of navigation to the user. The exit button however required me to input the coding manually this is how I did this.

I right clicked on the exit button and selected the assign macro option this then brung up this window.

        

This shows the coding behind the end button.

This is the customer details page. As can be seen the colour and design is consistent with the menu page.

This is very basic workbook the data was imported from another spreadsheet using excels import wizard and the macro was done as shown previously.

This workbook is to very basic it contains the product details that are available to purchase within Eden farm these where imported from Eden farms database.

This is the Invoice page this contains VLookups and if statements as well as a complex paste special function which is behind the transfer button.

This shows the Vlookup formula, it simply finds data in a different field and inputs it to the field selected. All the data shown varies as to what cell I4 contains as this is the absolute cell meaning the cell that is referenced back to.

When a different ID is selected the customer that number belongs to will be displays in the details table. Also the products table works in a similar way. When a different ID is select that means a different product which will mean a diffent price and a different outcome.

The Discount is determined by the customer type for this to be calculated in a spreadsheet an IF statement is needed. The statement shown above shows how it is calculated it shows that a personal customer get 0% discount, a supermarket gets 10% discount, a care home gets 8%, a school gets 8%, a nursery gets 5% and a hotel gets 5%. The date field required a formula also this is =TODAY(). Also the customers get seven days to pay the bill so the due by formula is =DATE(2009,3,24) as that was the date seven days after the order.

This shows that after pressing the print button the documented printed.

The transfer button works as a complex function called a paste special this is doine by recording a macro and copying data you want transferred and then paste special it to the desired location. Also the print area button sets the computer to print the invoice that is within the border and not the empty space around it. I have done this as I found once I did it in one computer the next one I used would not have save the changes so I created a macro to do this for every new computer.

This is the summary page this is where the transfer button take the information from the invoice and inputs it her all the buttons are the same except for one. The format new entry button changes a new addition to the table in to the correct format e.g. currency. I found that when I transferred the data some of the fields would be in the wrong format despite me redoing the transfer I could not correct the problem so created the macro to do it for me.

This is the expenditure table it calculates the prices of the baskets per year adding an additional 2% on each year.

This shows the formula adding an additional 2%. This worksheet calculate the expenditure by seeing how much the costs have been percentage wise and then deducting this from the income this then determine the profit as shown in the table above.

        

This shows the sales figures for the year of 2006 the there are many complex formulas within this page including if statements and print macros.

        

This shows the formula for the selected cell it says that its calculating 1 cell and multiplying it by another in the worksheet named expenditure. This worksheet is where the expenditure profit and income are calculated.

        

The sales figure pages are all the same however when the formulas refer back to the expenditure page they refer back to different cells. Also the pages contain the formulas average, min and max  these formulas do as they suggest average finds the average number in a range of fields as shown above. MAX shows the maximum number in a range and MIN shows the minimum in a range of fields.

This shows the possible sale figures for 2010 and uses the what if scenarios to determine what the costs and sales will be.

This shows that scenarios have been entered for the basket sales.

This shows the table which comes from the possible sales figures page it shows the income, expenditure and profit.