T Financial Information System List of Tables
|1||Sales figures for the month of October 2013||4|
|2||Sales figures for the month of November 2013||4|
|3||Sales figures for the month of December 2013||5|
List of Figures
|1||Product Details and Prices||6|
|2||Monthly Sales Figures||7|
|3||Sales Bonus Calculation 1||8|
|4||Sales Bonus Calculation 2||9|
|5||Sales Bonus Calculation 3||9|
|6||Profit and Loss Account for October 2013 to December 2013||11|
|7||More information on Profit and Loss Account||11|
Question You are required to develop a financial information system based on a fictional company of your choice. The company will be a small business run as a sole trading enterprise. The company sells three products to its customers using five self-employed sales people. The company only has limited computing resources and you are required to develop the system using Excel 2010. The companyâ€™s most urgent need is to have information concerning the profitability of the products they sell; the profitability of the locations where they are selling their products; and the performance of their salespeople. The company would like to use the system for forecasting and analysis purposes and therefore expects it to be constructed in such a way that data can be varied and results calculated automatically. The companyâ€™s price list is based on a mark-up of 80%. The sales people receive a small basic monthly salary plus sales bonus of 33% paid one month in arrears. Question 1 A) Write a brief description of the company and the business in which it is engaged. (Max 250 words). MATTA Travel MATTA Travel was set-up as a sole-proprietorship since 8th June 2013 and based in Penang, Malaysia. It is engaged in the business of selling black luggage bag in three different sizes 20 inches (lug20), 22 inches (lug22) and 24 inches (lug24). The company marked-up the products by the margin of 80%. MATTA Travel was handled by Mr Lim â€“ the owner and he hired five sales people to sell company products to customer. The sales people are self-employed and implement their own marketing approaches. Each of them market and sell the products to different locations such as Air Itam, Georgetown, Bayan Lepas, Bukit Tambun and Butterworth. The sales people receive a small basic monthly salary plus sales bonus of 33% on the sales value of the products sold. The sales bonus will be paid one month in arrears. Question 1 B) Provide data for three months of trading.
|October 2013||Markesh/ Air Itam||Keith/ Georgetown||Chun Keat/ Bayan Lepas||Ibrahim/ Bukit Tambun||Julia/ Butterworth|
Table 1: Sales figures for the month of October 2013
|November 2013||Markesh/ Air Itam||Keith/ Georgetown||Chun Keat/ Bayan Lepas||Ibrahim/ Bukit Tambun||Julia/ Butterworth|
Table 2: Sales figures for the month of November 2013 Question 1 B (cont..)
|December 2013||Markesh/ Air Itam||Keith/ Georgetown||Chun Keat/ Bayan Lepas||Ibrahim/ Bukit Tambun||Julia/ Butterworth|
Table 3: Sales figures for the month of December 2013 The above tables show the sales figures of five sales people during the month of October 2013 to December 2013. The data is shown according to luggage bag size and sales people. The code lug20 represent 20 inches luggage bag, lug22 represent 22 inches luggage bag and lug24 represent 24 inches luggage bag. The sales people that employed by MATTA Travel are Markesh who based in Air Itam, Keith who based in Georgetown, Chun Keat who based in Bayan Lepas, Ibrahim who based in Bukit Tambun and Julia who based in Butterworth. Question 1 C) Construct a financial information system, using Excel 2010, which meets the companyâ€™s requirements. I have since constructed a financial information system using Excel 2010 to meet the companyâ€™s requirements as per figures attached below. I will explain the usage of each sheet below the figure.
Figure 1: Product Details and Prices The Figure 1 shows Products Details and Prices sheet. In here, the user must first set up the markup margin and the cost price for each luggage bag. Once markup margin set and cost price keyed in, the system will auto calculate the sales price. The formula as follow: (Cost Price * Markup Margin) + Cost Price Question 1 C (cont..)
Figure 2: Monthly Sales Figures Question 1 C (cont..) The Figure 2 shows Monthly Sales Figures sheet. In Figure 2, the user will need to key in the sales figures for the month of October 2013 to December 2013 according to product code. Once the sales figures are keyed in, the system will auto calculate the SUBTOTAL for each product code according to sales people and area. The system will also calculate the TOTAL for each product code according to month and TOTAL for 3 months. From here, we are able to get the information about best-selling item. The best-selling item during the October 2013 to December 2013 is lug22 â€“ 22 inches luggage bag which sold 79 units, follow by lug24 â€“ 24 inches luggage bag which sold 70 units and lug20 â€“ 20 inches luggage bag which sold 68 units. We also can get the information about purchases for the particular product code from the â€˜Total Cost Price by Products (RM)â€™. It will auto generate by using the sales figures key in by user and multiply by the cost derived from â€˜Products Details and Pricesâ€™ sheet.
Figure 3: Sales Bonus Calculation 1 Question 1 C (cont..) Figure 4: Sales Bonus Calculation 2 Figure 5: Sales Bonus Calculation 3 Question 1 C (cont..) The Figure 3 to 5 show Sales Bonus Calculation sheet. In here, the user will need to set the Bonus Margin and the system will auto calculate the TOTAL SALES and BONUS for each sales people, each product code and each month. The Sales (Units) is taken from â€˜Monthly Sales Figuresâ€™ sheet and whenever any changes make to there, the figures will change too. The Value (RM) the cost price of product and is taken from â€˜Products Details and Pricesâ€™, the amount will be changed too if there is any changes make to there. Total Sales (RM) column will show the sales for each product code and the formula is: Sales (Units) * Value (RM) Bonus (RM) column shows the bonus calculation based on the total sales and bonus margin set. The formula is: Total Sales (RM) * Bonus Margin While for the Total for Oct 13, Total for Nov 13 and Total for Dec 13, these three rows will show the total of Sales (Units), total of Sales and Bonus received for each sales people. The above applied to all sales people column and at the second last column â€˜Total Sales for the month ofâ€™ indicates the total sales for three product code for that month i.e. October 2013, November 2013 and December 2013. While for â€˜Total Bonus Paid for the month ofâ€™ indicated the total bonus paid to sales people during the particular month. The last row of the sheet indicates the TOTAL of all columns for three months from October 2013 to December 2013. Those columns calculated for individual sales people are Sales (Units), Total Sales (RM) and Bonus (RM). While for â€˜Total Sales for the month ofâ€™ and â€˜Total Bonus Paid for the month ofâ€™ indicates the overall figures. Question 1 C (cont..)
Figure 6: Profit and Loss Account for October 2013 to December 2013 Figure 7: More information on Profit and Loss Account Question 1 C (cont..) The Figure 6 and 7 show the Financial Information sheet. In here, user do not required to key in any information. All the figures are link to previous sheet and calculation is done based on the figures derived. â€˜Salesâ€™ in Figure 6 is taken from TOTAL of â€˜Total Sales for the month ofâ€™ column from â€˜Sales Bonus Calculationâ€™ sheet. While for â€˜Purchasesâ€™, it is taken from â€˜Total Cost Price by Products (RM)â€™ of â€˜Monthly Sales Figuresâ€™ sheet. Gross Profit = Sales â€“ Purchases, which indicates the profitability of the products before deduct other expenses. â€˜Sales People Bonusâ€™ is referred to â€˜Total Bonus Paidâ€™ from Sales Bonus Calculation sheet. Net Profit = Gross Profit â€“ Sales People Bonus, which shows the profit after deduct expenses. Figure 7 indicates the information needed by the company such as profitability of the products they sell; the profitability of the locations where they are selling their products; and the performance of their salespeople. Question 2 A) What do you understand by the term â€˜quality informationâ€™? In answering this refer to your system which you have developed in question 1 above (max. 200 words). Quality information describes the standard of the content of information; it is also defined as the fitness for use of the information provided. Information is a product used to support decision-making, and its quality is manageable. The above system meets the company requirement i.e. profitability of the products they sell; the profitability of the locations where they are selling their products; and the performance of their salespeople. Thus, I would consider the system provide quality information. The business owner also can make decision with the quality information, for an example, how he can increase the profit; by increase the selling price or have a lower cost price, the profit can be increased. The system will also provide other information such as best selling product, best salesperson, best sales area, best sales month and etc. With these information, the business owner can implement his plan or strategy to allocate more resources to the particular product, area or salesperson. In conclusion, a system that can meet user requirement and allow user to make decision to improve business, it will consider as a quality information system. Question 2 B) How would you ensure your system continued to provide quality information? (max 200 words). The characteristics of quality information are accuracy, completeness, consistency, and timeliness. The information recorded into database must be accurate and reliable. Accuracy of the information can be validated by comparing the sources of data and the motivation for the information is being produced must be unbiased. A database system that is accurate and valid helps to develop business ideas and promoting its objectives. Completeness of data is important to endure high quality information. If the data is incomplete or only contains partial information, it fails to provide full picture. The facts and figures should not be concealed or missed out. Consistency is another aspect of quality information. For example, the column for Post Code entry 5 digits is the expected length of the field; we will set in the database, a number more or less than 5 digits will not be accepted. If the field are not set to a specific limit for the information, the consistency might not meet. Lastly, timeliness is also one of important attribute of information. Real-time information is a fundamental of timeliness. To access accurate information at the right time, the information should be communicated in time by selecting appropriate channel of communication. Question 2 C) Explain how the owner of the business benefits from this information (max 200 words) Possessing high quality information helps to improve business performance in term of financial, operations, marketing metrics and so on. Quality information ensures the data meets the needs of the owner of the business. The managing director doesnâ€™t have time to go through all reports; he wants the key facts of the details to make timely decision for company development. Besides, quality information presented using summaries can be communicated effectively to inform decisions about all aspects of business. Clear and concise information keeps workforces in the company and even customer current about companyâ€™s direction. Quality information in a useable form increases productivity in the fast-moving business environment. High quality information understands customer needs and provides clear information about the business, products and services, which can attract new potential business and keep existing customers up to date on company improvements. It also presents the business as dynamic and professional. The quality information publicizes the businessâ€™ objective accurately and provides verified details that communicate latest messages. Effective database system helps in knowledge management, as it enables managing director to monitor companyâ€™s operations and enhances collaboration among workforces. This also ensures better ways to measure performance and manages resources cost effectively. Question 3) The owner of the business realizes that all decisions connected with the future development of the company come from him and him alone. How would you suggest that he organises his workforce so that they can freely pass their ideas to him? (max 250 words). An effective way to run a business efficiently is to organise different department. The organisation can be structured to carry out its various activities to ensure that every workforce is well-aware of businessâ€™ objectives and communications. The process of organising are division of work, departmentalization according to job specification, linking departments to shape overall organization structure, assigning duties to each departments and lastly, defining hierarchal structure in the department. As each department specialised into smaller assigned tasks according to their qualification, they can propose a quality ideal to the business owner after finalising the discussion among themselves. This is a win-win situation as the ideal of workforce can be heard and the business owner can get a more concrete and beneficial ideal from expert who know what is good for company future development. Besides, in the organization, the function of every workforce is defined clearly. Each manager in the departments play important role in managing the team and report to the business owner. This ensured that the latest key message from director can be passed through to every workforce and everyone knows where the company is heading to. Whenever the changes happen in company, organizing function helps to communicate these changes to every workforce and hence it can be adopted systematically. In short, departmentalization helps in effective administration and ensures every workforce can work productively in a well-aligned business structure. An effective organization structure will result in increased profitability of the business. References Website
We will send an essay sample to you in 2 Hours. If you need help faster you can always use our custom writing service.Get help with my paper