unit 42 – a2 · web viewaverage sales for comic c over 12 months sales 41365 41395 41426 41456...

Unit 42 – A2

Brandon Johnson

20249518 Blackburn College

Brandon Johnson Unit 42 – A2 20249518

ContentsP4..........................................................................................................................................................2

Sales graphs:................................................................................................................................2Expenses graphs:........................................................................................................................9Pie charts:....................................................................................................................................12

Bar charts:...................................................................................................................................17

P5........................................................................................................................................................19

Restricting Data Entry:............................................................................................................19

Protecting Worksheets and Cells.........................................................................................19

Modifying Toolbars and Menus.............................................................................................20

Checking Data, Data Validation...........................................................................................21

Displaying Appropriate INPUT Messages..........................................................................22

Displaying Appropriate ERROR Messages........................................................................22

P6........................................................................................................................................................23

Each Graph has its own Worksheet....................................................................................23

Correct Implementation of Macros.....................................................................................24

Sub Menus...................................................................................................................................24

Updated Menu............................................................................................................................27

M2 – Analyse and Interpret Data.............................................................................................28

M3 – Compare Different Automation Methods....................................................................35

Macro.............................................................................................................................................35

ActiveX..........................................................................................................................................36

Control Toolbox..........................................................................................................................36

Comparing the Different Methods.......................................................................................37

1


P4All of these charts and graphs are fit for purpose. I have used pie charts to show proportion, line graphs to show progress, and bar/column charts to show performance.

Sales graphs:You can see that I have included appropriate titles, labels, and axis in all of these graphs.This graph shows the sales for comic ‘A’ over 12 months. You can see that the sales peaked in September, and was at its lowest was in May. Overall, you can tell by the trendline that the sales of the comic increased over the 12 months

Apr-13 May-13 Jun-13 Jul-13 Aug-13 Sep-13 Oct-13 Nov-13 Dec-13 Jan-14 Feb-14 Mar-148200

8400

8600

8800

9000

9200

9400

9600

Average Sales for Comic A Over 12 Months

Months

Sale

s

This graph shows the sales for comic B over 12 months. You can see that the sales peaked in March, and was at its lowest was in August. Overall, you can tell by the trendline that the sales of the comic increased over the 12 months


5500

6000

6500

7000

7500

Average Sales for Comic B Over 12 Months

Months

Sale

s

2


This graph shows the sales for comic C over 12 months. You can see that the sales peaked in April, and was at its lowest was in March. Overall, you can tell by the trendline that the sales of the comic decreased over the 12 months


8500

9000

9500

10000

10500

11000

11500

12000

12500

13000

Average Sales for Comic C Over 12 Months

Months

Sale

s

This graph shows the sales for comic D over 12 months. You can see that the sales peaked in December, and was at its lowest was in July. Overall, you can tell by the trendline that the sales of the comic increased over the 12 months


6600

6800

7000

7200

7400

7600

7800

8000

8200

8400

Average Sales for Comic D Over 12 Months

Months

Sale

s

3


This graph shows the sales for comic E over 12 months. You can see that the sales peaked in December, and was at its lowest was in November. Overall, you can tell by the trendline that the sales of the comic decreased over the 12 months


17500

18000

18500

19000

19500

20000

20500

Average Sales for Comic E Over 12 Months

Months

Sale

s

This graph shows the sales for comic F over 12 months. You can see that the sales peaked in January, and was at its lowest was in May. Overall, you can tell by the trendline that the sales of the comic increased over the 12 months.


18200

18400

18600

18800

19000

19200

19400

19600

19800

20000

Average Sales for Comic F Over 12 Months

Months

Sale

s

4


This graph shows the sales for comic G over 12 months. You can see that the sales peaked in April, and was at its lowest was in March. Overall, you can tell by the trendline that the sales of the comic decreased over the 12 months.


4400

4600

4800

5000

5200

5400

5600

5800

Average Sales for Comic G Over 12 Months

Months

Sale

s

This graph shows the sales for comic H over 12 months. You can see that the sales peaked in January, and was at its lowest was in May. Overall, you can tell by the trendline that the sales of the comic increased over the 12 months.


5800

6000

6200

6400

6600

6800

7000

Average Sales for Comic H Over 12 Months

Months

Sale

s

5


This graph shows the sales for comic I over 12 months. You can see that the sales peaked in September, and was at its lowest was in April. Overall, you can tell by the trendline that the sales of the comic increased over the 12 months.


7500

7600

7700

7800

7900

8000

8100

8200

8300

8400

Average Sales for Comic I Over 12 Months

Months

Sale

s

This graph shows the sales for comic J over 12 months. You can see that the sales peaked in February, and was at its lowest was in May. Overall, you can tell by the trendline that the sales of the comic increased over the 12 months.


4400

4600

4800

5000

5200

5400

5600

Average Sales for Comic J Over 12 Months

Months

Sale

s

6


This graph shows the average comic sales over 12 month. The sales were very inconsistent, and there wasn’t much of an increase, or decrease according to the trendline.


9500

9600

9700

9800

9900

10000

10100

Average Comic Sales over 12 Months

Months

Sale

s

This graph shows the average income over 12 months for the entire business. According to the trendline the company was successful with their income throughout the year.

Apr-13 May-13 Jun-13 Jul-13 Aug-13 Sep-13 Oct-13 Nov-13 Dec-13 Jan-14 Feb-14 Mar-14£22,500.00

£23,000.00

£23,500.00

£24,000.00

£24,500.00

£25,000.00

£25,500.00

£26,000.00

£26,500.00

£27,000.00

Average Income over 12 Months

Months

Inco

me

7


This graph shows the average expenses over 12 months, excluding the publishers cut. The trendline shows that without the publishers cut average expenses get cheaper.

Printers

and Paper

Distrib

ution

Retailer

Author and Transla

tor

Editing and Proofreading

£- £10,000.00 £20,000.00 £30,000.00 £40,000.00 £50,000.00 £60,000.00 £70,000.00 £80,000.00

Average expenses over 12 months (Exlcuding Publisher cuts)

Expenses

Expe

nses

8


Expenses graphs:This graph shows the printer and paper expenses over 12 months. Use can see that the price was its most expensive in March, and was its cheapest in November. According to the trendline the printer and paper expenses become more expensive over the 12 months.


£35,000.00

£36,000.00

£37,000.00

£38,000.00

£39,000.00

£40,000.00

£41,000.00

Printers and Paper Expenses Over 12 Months

Months

Expe

nse

This graph shows the distribution expenses over 12 months. Use can see that the price was its most expensive in March, and was its cheapest in November. According to the trendline the distribution expenses become more expensive over the 12 months.


£35,000.00

£36,000.00

£37,000.00

£38,000.00

£39,000.00

£40,000.00

£41,000.00

Distribution Expenses Over 12 Months

Months

Exen

ses

9


This graph shows the retailer expenses over 12 months. Use can see that the price was its most expensive in March, and was its cheapest in November. According to the trendline the retailer expenses become more expensive over the 12 months.


£70,000.00

£72,000.00

£74,000.00

£76,000.00

£78,000.00

£80,000.00

£82,000.00

Retailer Expenses Over 12 Months

Months

Expe

nses

This graph shows the author and translator expenses over 12 months. Use can see that the price was its most expensive in March, and was its cheapest in November. According to the trendline the author and translator expenses become more expensive over the 12 months.


£11,500.00

£12,000.00

£12,500.00

£13,000.00

£13,500.00

Author and Translator Expenses Over 12 Months

Months

Expe

nses

10


This graph shows the editing and proofreading expenses over 12 months. Use can see that the price was its most expensive in March, and was its cheapest in November. According to the trendline the editing and proofreading expenses become more expensive over the 12 months.


£23,000.00

£23,500.00

£24,000.00

£24,500.00

£25,000.00

£25,500.00

£26,000.00

£26,500.00

£27,000.00

Editing and Proofreading Expenses Over 12 Months

Months

Expe

nses

This graph shows the publisher expenses over 12 months. Use can see that the price was its most expensive in March, and was its cheapest in November. According to the trendline the publisher expenses become more expensive over the 12 months.


£50,000.00

£52,000.00

£54,000.00

£56,000.00

£58,000.00

£60,000.00

Publisher Expenses Over 12 Months

Months

Expe

snes

11


Pie charts:You can see that I have included appropriate titles, labels, and axis in all of these charts.These pie charts show the expenses for each month. The charts show that the biggest expense over the year --in every month, is the retailer price.

£36,750.00

£36,750.00

£73,500.00 £12,250.00

£24,500.00

£52,916.67

Expenses Chart for April 2013

Printers and Paper Distribution RetailerAuthor and Translator Editing and Proofreading Publisher

£36,881.25

£36,881.25

£73,762.50 £12,293.75

£24,587.50

£53,135.42

Expenses Chart for May 2013


12


£37,237.50

£37,237.50

£74,475.00 £12,412.50

£24,825.00

£53,729.17

Expenses Chart for June 2013


£37,012.50

£37,012.50

£74,025.00 £12,337.50

£24,675.00

£53,354.17

Expenses Chart for July 2013


£37,031.25

£37,031.25

£74,062.50 £12,343.75

£24,687.50

£53,385.42

Expenses Chart for August 2013


13


£37,365.00

£37,365.00

£74,730.00 £12,455.00

£24,910.00

£53,941.67

Expenses Chart for September 2013


£36,937.50

£36,937.50

£73,875.00 £12,312.50

£24,625.00

£53,229.17

Expenses Chart for October 2013


£36,157.50

£36,157.50

£72,315.00 £12,052.50

£24,105.00

£51,929.17

Expenses Chart for November 2013


14


£37,200.00

£37,200.00

£74,400.00 £12,400.00

£24,800.00

£53,666.67

Expenses Chart for December 2013


£37,436.25

£37,436.25

£74,872.50 £12,478.75

£24,957.50

£54,060.42

Expenses Chart for January 14


15


£37,462.50

£37,462.50

£74,925.00 £12,487.50

£24,975.00

£54,104.17

Expenses Chart for February 2014


£40,012.50

£40,012.50

£80,025.00 £13,337.50

£26,675.00

£58,354.17

Expenses Chart for March 2014


16


This pie chart shows that the average expenses over 12 month. You can see that the retailer was consistently the most expensive, whilst the author and translator expense was the cheapest.

£37,290.31

£37,290.31

£74,580.63

£12,430.10

£24,860.21

Pie chart showing the average expenses over 12 months (Exlcuding Publisher cuts)

Printers and Paper Distribution RetailerAuthor and Translator Editing and Proofreading

Bar charts:You can see that I have included appropriate titles, labels, and axis in all of these charts.This bar chart show the best performing comic in terms of sales. You can see that title E is the best performing comic in term of sales.

Title A Title B Title C Title D Title E Title F Title G Title H Title I Title J0

50000

100000

150000

200000

250000

Bar Chart Showing the Best Performing Comic(In Terms of Sales)

Comics

Sales

17


This bar chart shows the best month in terms of sales. You can see that February was the best month in terms of sales.


95000

96000

97000

98000

99000

100000

101000

Bar Chart Showing the Best Month (In Terms of Sales)

Months

Sales

18


P5Restricting Data Entry:The target sales of the company might be private information. I have restricted data entry by hiding the information altogether. This will help keep the information safe.In this screenshot you can see that I have chosen to hide the target sales.

Protecting Worksheets and CellsI have protected the cover price cells in my income sheet. This is because if they are altered, the information throughout the whole spread sheet is changed. To prevent people from changing a lot of data, I have protected the cells with a password.In this screenshot you can see that I have protected the cover price cells with a password.

Here is a screenshot of the restrictions I have chosen.

19


Modifying Toolbars and MenusI have customised the quick access toolbar in Microsoft Excel so that I can view my macros any time I want. I use macros to navigate between my work sheets, so it is useful that I can view all of the macros that I have created so easily.This screenshot shows me modifying the quick access toolbar, and choosing the “View Macros” function.

This screenshot shows the view macros icon on the quick access toolbar.

20


21


Checking Data, Data ValidationI used data validation when I created the drop down box for the author of the specific comic. If the data entered into the cell is not valid, then an error message appears, preventing the user from entering the incorrect information. The error message reads: “You must choose an author from the drop down list.”

This screenshot shows the error message prompt that appears, should the user input incorrect information.

22


Displaying Appropriate INPUT MessagesI have used appropriate input messages that indicate to the user that they should choose and author from the drop down box, if they want to enter data into that cell.You can see from this screenshot the drop down box icon and the input message.

Displaying Appropriate ERROR MessagesI have used appropriate error messages to pop up when the user inputs incorrect message into the cell that has the drop down box. They can enter information without using the drop down box, as long as the data is valid.

23


P6Each Graph has its own WorksheetI have created sub menus so that you can navigate each graph and chart, looking at them in their own work sheet.This screenshot shows an example of one of the sub menus.

This screenshot shows what the graphs look like on their own pages.

24


Correct Implementation of MacrosI have implemented macros into my spreadsheet. I have done this so that you can navigate my spreadsheet easily. I have implemented them in a way that you can get to any worksheet within 2 – 3 clicks. This screenshot shows some of the macros that I have created. I have created so many that you can only see macros starting with the letter ‘A’ (the list is sorted alphabetically). I have implemented 53 macros so far.

Sub MenusI have created five sub menus that you can use to navigate all the graphs and charts.This screenshot shows the first sub menu. It contains four buttons that lead to other sub menus that contain even more buttons.

25


This screenshot shows the next sub menu: Sales Graphs. This sub menu contains buttons that take you to single line graphs. You can see that the graphs include average sales over 12 months for every single comic, and other miscellaneous line graphs.

This sub menu includes expenses graphs. You can see that all the menus keep the same look and feel, keeping the spreadsheet consistent and easy to navigate.

26


This screenshot shows the pie charts sub menu. Again, you can see how I have kept the sub menus consistent with the look and feel. You will also start to notice the colour schemes.

This screenshot shows a rather small sub menu that contains two bar charts showing the best performing comic and month throughout the 12 months that have been recorded.

27


Updated MenuI have updated the main menu to include new buttons. The buttons take you to all of the graphs and charts on one page. You can also choose to use the ‘All Graphs & Charts’ button to go to a new sub menu that lets you open sheets for each and every graph and chart.This screenshot shows the updated menu with the new buttons and updated old ones to look more pleasing.

28


M2 – Analyse and Interpret DataIn this part of the document I will analyse and discuss the data from within my spreadsheet, and predict the sales of comics over the next six months. I will use the graphs and charts in my interpretation. After analysing each of the graphs for the average sales of comics over 12 months, I have found that four of the comics decreased in average sales over the 12 months, some more drastically than others.You can see from the line graph below that comic c has been decreasing in sales drastically over the 12 months. I have used trend lines to determine whether or not comics are decreasing in sales or not. Just by looking at the graph you can tell that this is a comic that obviously isn’t doing well, and needs to be discontinued.


8500

9000

9500

10000

10500

11000

11500

12000

12500

13000

Average Sales for Comic C Over 12 Months

Months

Sale

s

29


This line graph shows the second comic that is decreasing in sales. You wouldn’t say that the comic is performing badly, but the trendline indicates that the sales are ultimately decreasing. This would be because of how bad one month was for one month, juxtaposed to how average it was throughout the year. I wouldn’t say that this is a cause for concern yet though, it started selling well again at the end of the 12 months.


17500

18000

18500

19000

19500

20000

20500

Average Sales for Comic E Over 12 Months

Months

Sale

s

This line graph shows comic G, which is definitely a comic that needs to be discontinued. It was selling an average amount of comics as the beginning of the 12 months, but then showed somewhat of a rocky performance as it dwindled down to its lowest point at the end of the 12 months. This comic definitely needs to be discontinued.


4400

4600

4800

5000

5200

5400

5600

5800

Average Sales for Comic G Over 12 Months

Months

Sale

s

30


This line graph shows the average sales for comic H. This comic is another one that the company should be suspicious of. It declined in terms of sales quite a bit throughout the second half of the 12 months, then carried on to end at the exact number of sales that it was making at the beginning of the 12 months. The company should consider discontinuing this comic.


5800

6000

6200

6400

6600

6800

7000

Average Sales for Comic H Over 12 Months

Months

Sales

After analysing the data for all of the comics that are decreasing in sales (according to the trendline) we have established that comics C and G both need to be discontinue, as they are putting the company at a loss. They are failing to sell and are decreasing in sales quite a bit. Despite some of the comics not doing so well in terms of sales, the majority of comics aren’t doing so badly. Comics A, B, F, I, and J are all performing tremendously and have made the company turn a profit over the 12 months.This is comic A. Comic A increased in sales throughout the year. You can see from the trendline that the trendline that there was a rather large increase in sales over the 12 months.


8400

8600

8800

9000

9200

9400

9600

Average Sales for Comic A Over 12 Months

Months

Sales

31


This is comic B you can see from the trendline and the data on the line graph that this comic did very well, and made a lot of money for the company over the 12 months.


5500

6000

6500

7000

7500

Average Sales for Comic B Over 12 Months

Months

Sales

This graph shows the average sales for comic F over the 12 months. You can see that this is the most popular comic that the company is selling. That didn’t stop the comic from selling even more though, the comic went on to reach 19,800 sales in February and increase overall over the 12 months.


18200

18400

18600

18800

19000

19200

19400

19600

19800

20000

Average Sales for Comic F Over 12 Months

Months

Sales

32


This is comic I. You can see from the line graph and the trendline that the comic made a steady increase in sales over the 12 months. This is an example of a comic that already sold well, but then went on to sell even more. The company should definitely continue selling this comic.


7500

7600

7700

7800

7900

8000

8100

8200

8300

8400

Average Sales for Comic I Over 12 Months

Months

Sale

s

This graph shows the average sales for comic J over 12 months. You can see that this isn’t the most popular comic of all time, but he trendline and the rest of the data on the graph show that the comic is doing particularly well for the company, and despite not spelling that much, made some profit for the company. The company should continue selling this comic.


4400

4600

4800

5000

5200

5400

5600

Average Sales for Comic J Over 12 Months

Months

Sale

s

33


The data that I have shown proves that the company is doing well, and that it is making a profit, however there are ways of improving it. You can improve the profit by eliminating any of the under-performing comics, like I have already mentioned. If I were to delete the data for the under-performing comics and change the expenses so that I didn’t have to pay for the discontinued comics, then the profit would look like this:The new profit would be £805,125, whilst the old profit was only £100,000. This is a big upgrade, and this shows why companies should get rid of products that don’t sell. It improves the profit, and in this case, it was over 8 times as successful.I have used trend-lines to predict the sales of comics and income over the 6 months following the sales. I did this for each individual comics, the average sales of the comics, and the average income. The relevant graphs out of the ones that I have created are the average sales and the average income.This graph shows the average comic sales over 12 month. You can see that I used the trendline to predict the sales for 6 months after the initial 12. You can also see that because the profit was so small, the trend-line predicts that there won’t be that the average sales won’t increase or decrease over the next 6 months, meaning that some of the underperforming comics need to be discontinued.


9500

9600

9700

9800

9900

10000

10100

Average Comic Sales over 12 Months

Months

Sale

s

34


This graph shows the average income over 12 months, with the predicted average income for the following 6 months displayed. You can see that there is a steady increase in the average income over the next 6 months, meaning that the trend-line predicts that the company will make more money in the future.


£23,000.00

£23,500.00

£24,000.00

£24,500.00

£25,000.00

£25,500.00

£26,000.00

£26,500.00

£27,000.00

Average Income over 12 Months

Months

Inco

me

35


M3 – Compare Different Automation MethodsIn this part of the document I will talk about different methods that you can use to automate your spreadsheet. There are 3 methods that I am going to talk about. I will show examples of each automation method, then I will talk about the merits of each one, then compare them.

MacroThis is an example of a macro that I have created. I have created it to make it print out the current sheet. You can see from the screenshots that I have created a macro that prints out whichever sheet is active. I have used this macro on each of the pages that are available to print out.This screenshot shows the code for the macro. It also shows a correct implementation of Visual Basic.

This screenshot shows the prompt that shows when you click the “print sales” button.

This is the quick menu on the sales sheet. It contains the print button that has the macro applied to it.

36


ActiveXThis is an example of an ActiveX button that I have used. You can create ActiveX buttons to be different shapes. This allows you to be creative when applying macros to them. In my example I used an arrow pointing to the left, labelled back. This suggests to the user that the button would take you to the previous menu, and it does.In this screenshot you can see one of the sub menus that I have created. Underneath the four main buttons is the ActiveX button that I talked about. Clicking the button will take you to the previous menu. It also shows a correct implementation on Active X

Control ToolboxYou can use the control toolbox to use simple command buttons. You can use these command buttons to navigate your spreadsheet by using macros. Customisation with them is limited however, as there are other methods of automation for that. I used these command buttons to allow you to take a shortcut to the main menu from most work sheets. You can see in this screenshot that I used simple command buttons from the control toolbox to create a button that takes you straight back to the main menu, and one that takes you to the previous menu. It also shows a correct implementation of control toolbox commands.

37


Comparing the Different MethodsIn this part of the document I will talk about the merits of each of the automation methods that I have mentioned, and I will compare them with each other. Macros have quite a few advantages, but you can only really utilise them if you know VBScript (Visual Basic scripting language). VBScript is used for the code behind macros, and you can create more complex modules and macros once you’ve learned it. The example I showed was extremely basic as far as macros go, but you can automate your spreadsheet as much as you want if you are capable of using VB. Control toolbox commands don’t really have a lot of merits, but the main one that they have is the fact that they are so easy to create. You can just import a command button from the control toolbox on the developer tab and assign a macro to it easily enough.ActiveX buttons have a lot of customisability, and can be used to make your spreadsheet look more appealing. You can also insert them as shapes and then apply macros to them. You can make them in the shapes of arrows, indicating to the user that you can use them to go to the previous or next sheet. They can also be coloured, resized, and customised to have different fonts. Really allowing you to fully customise and automate your spreadsheet. You can’t really compare macros to ActiveX buttons and Control Toolbox buttons. This is because you can use VB to fully utilise macros to make the buttons look better, so it really comes down to ActiveX and Control Toolbox command buttons. Due to the limitless customisability of ActiveX, and the limited customisability of the command buttons, I would have to say that ActiveX buttons are better compared to the Control Toolbox command buttons. That being said, if you can fully utilise VB, then you can create impressive macros that you can then assign to ActiveX buttons, allowing you to fully automate your spreadsheet with more relevant, advantageous buttons.

38

unit 42 – a2 · web viewaverage sales for comic c over 12 months sales 41365 41395 41426 41456...

Documents