Workshop Management Database3
Normalization CTelNo as the key attribute..3
Normalization of Job number as the key attribute based on the Bill
of Material file 5
Normalization of Inventory Book, Part No. as key attribute..7
Entity Relationship Diagram for 3NF..9
Protoype design Microsoft Access..11
Introduction to United Kingdom’s Bicycle Industry14
Peleton Ltd ..14
Income Statement/ Financial and Non-Financial Performance15
Financial and Non-financial Performance16
Additional Financial and Non-financial Performance.16
Other Areas of Concern18
Recommendation.19
Introduction
In today’s business world, an up-to-date, complete, and reliable financial database system is a must for organizations that aim for efficient, secure, and orderly operation. The succeeding sections will discuss the logical design for a prototype relational database of Peleton, a distributor of bicycle, accessories, and related services for its workshop management. Peleton needs a system that will record each customers bike type and number, services acquired, mechanic and date frame of transaction. Below are the proposed logical system designs for Peleton’s workshop management database as done through normalization. Agile Data’s (2015) website stated that normalization is important as it increases the cohesion of the entities, and it reduces redundancy.
Workshop Management Database
Normalization CTelNo as the key attribute
1NF- The Job Book’s system was improved by adding tabs for Price, Method of Payment, and Date of Payment for the first normal form.
2NF - The second normal form of the database system provided the full name and contact details of customers based on the surname. Some customers may have the same surnames as they may be relatives or not. It also provided job numbers with description. Notice that the system added the job number six so the record keeper can specify other services that are not included in the choices. A customer can have several bikes, and each bike can have several service orders. The second normal form will record each bike number and desciritpn and the job no. and job description. The 2NF will provide the job numbers and job descriptions for each type of bike by clicking the buttons beside the job number based on its corresponding job description. The record keeper can click the price button for the automatic computation of cost breakdown with tax. The method of payment button will provide options if the customer paid in cash, debit, or credit card.
3NF- The third normal form provided for the options for card type. The record keeper can click the buttons of either debit or credit card. After doing so, the record keeper can click the button of the acceptable card types that the customer used for the payment.
Normalization of Job number as the key attribute based on the Bill of Material file
1NF - The first normalization tab was improved by adding tabs for Total Price, Method of Payment, and Date of Payment.
2NF – The second normal form for the Bill of Material presents the Job No. from 1-6, 6 being the Others-specify. Each job number has corresponding job description. The job no.1 will have Parts no. 1-6, while the job no. 2 will have Parts no. 7-12, while the job no. 3 will have parts no. 13-18, job no. 4 will have Parts 19-24, job no. 5 Parts 25-30 , while job no. 6 is from 31 until appropriate. Each Part No. will have corresponding specified parts. The 2NF also identifies method of payment whether its cash, debit, or credit.
3NF –The third normal form for the Bill of Material provided the options for card type. The record keeper can click the buttons of either debit or credit card. After doing so, the record keeper can click the button of the acceptable card types that the customer used for the payment.
Normalization of Inventory Book, Part No. as key attribute
1NF- The inventory book was improved by adding the tabs of the Name who ordered the parts, total paid, pending payment, and methods of payment. Each recorded name will have a link to their master record on the CTel No.
2NF – The Name of who ordered tab will show the name of customer and the date he or she ordered for references. The inventory book will also have the to-date total amount paid and to-date pending payment. It will also show the total amount paid in cash, debit, and credit which are automatically computed based on the previous records on CTelNo and Bill of Material.
3NF – The third normalization form will provide information about the total amount paid in Visa, Mastercard JCB, and AMEX whenever checking on the total amount paid in Debit or Credit.
Entity Relationship Diagram for 3NF – The entity that will related the 3NF in the three files is the Method Of Payment Tab which will show the transactions in the card types. One can click the tab of Debit along the row of Visa to see the total amount paid in Visa Debit and the breakdown of the amount paid in Debit card for a specific period of time. The same goes to other card types.
Below is the Entity Relationship Diagram for 3NF for the whole transaction process.
I I I
I I I
I I I
I I I
I I
I I
I I
2. Protoype design Microsoft Access
Introduction to United Kingdom’s bicycle industry
The United Kingdom’s bicycle industry experienced a large growth with bicycle components sales reaching the booming state as it generates at an average of 3.5 million units sales per year in 2014, significantly higher than the 2012 records (Brown, 2014). The bike sales in the United Kingdom increased by 14 percent to GBP 745 million in 2013 from the recorded GBP 639 million in the last five years, 2008 (Harker, 2014).
Harker (2014) and Brown (2014) attributed such growths to rise in the number of commuters and recreational cycling and sporting boom. The bicycle industry is fragmented and the key players include Brompton, the largest producer of bicycle and its components with average production of 45,000 units per year, quite small compared to the total average yearly sales of 3.5 million units, and Pashley Cycles which produces 10,000 units per year and had GBP 4.5 million sales in 2013 (Brown, 2014).
An article of Malcolm Moore (2015) in the Financial Times revealed that the cycling industry continues to see more growth in the future. He noted that currently, there are about 2 million people cycling every week for sports excluding commuters, and industry players expect the number to increase to 2.5 million to 3 million in the next ten years (Moore, 2015). Such provides growth opportunities for the bike and component manufacturers and distributors in the sports arena. Moore (2015) also stated that a company, Halfords, distributes a quarter of bicycles in Britain.
Peleton Ltd
Peleton Ltd., a less publicized bicycle and bicycle component distributor and repair services provider, is considered another small player in the fragmented market of bicycles. Supposing the annual average growth of bicycle industry is 12 percent, we can estimate that the 2015 industry sales reached GBP 934.5 million. Basing from the 2013 sales of Pashley Cycles of GBP 4.5 million, we can estimate that Peleton Ltd had GBP 3.5 million sales in 2013 being a smaller player in the industry.
The interview with the Peleton Ltd’s Operations Director suggests fast growing sales in components and repair services. Hence, we shall estimate the growth of Peleton Ltd above industry average of 12 percent which can be about 14 percent. Therefore, Peleton Ltd’s estimated revenue by the end of 2015 is GBP 4.55 million. Below are the estimated financial and non-financial performances of Peleton Ltd for 2009-2015.
Income Statement/ Financial and Non-Financial Performance
Financial and Non-financial Performance
The table above shows a generally positive financial and non-financial performance as suggested in the website of Kaplan Financial Knowledge Bank (2015). Peleton Ltd showed growing profitability and liquidity measures which signify that the company has increasing cash flow and higher funds and ability to pay debts. Similarly, the non-financial performance suggests an overall satisfactory rate in terms of employees. management, quality control, and customer satisfaction which resulted to growing market share albeit a small percentage.
Additional Financial and Non-financial Performance
Financial Performance
Peleton Ltd registered a growing return on capital which is 69 in 2009 and dramatically increased to 83 percent, thanks to higher demand of bicycles and bicycle repairs and component and controlled inflation rate on raw materials. Similarly, gross profit margin increased from 90 percent in 2009 to 119 percent in 2015 while the net profit margin also increased from 58 percent in 2009 to 65 percent in 2015. The cost-efficient operations resulted to growing net income of GBP 1.1 million in 2009 to GBP 3 million in 2015 after all taxes and deductions were subtracted.
The company’s debts also decreased from GBP 200,000 in 2009 to GBP 190,000 in 2015. Peleton Ltd’s current ratio or ability to pay debts increased from 21.5 percent in 2009 to 49.5 percent in 2015, thanks to the strong growth in revenue, cost efficient operation that ballooned net income from the past six years that consequently dramatically increased the company’s liquidity.
Non-financial performance
Financial Times (2015) defined non-financial performance as the entity’s performance that are not expressed in the monetary terms such as customer or employee satisfaction, quality, market share, and number of new products.
Peleton Ltd’s number of employees increased by 25 percent from 36 in 2009 to 48 in 2015, and the average annual turnover rate is 3 percent which is quite low. The number suggests high employee satisfaction rate, good financial health, and good management due to strong growth of the number of employee and the low turnover rate.
The product and services quality rate also increased by two and four percent respectively during the reviewed period The service quality rate dipped in 2014 prior the creation of the system database and recovered in 2015 after the application of the new system database.
The customer satisfaction rate has also increased by 2.3 percent during the reviewed period from 90 percent in 2009 to 92.3 percent in 2015. Similarly, the company’s market share increased from .48 percent in 2009 to 52 percent in 2015, thanks to strong growth in revenues and improved services in the bicycle repair and fitting sales which accounts for 40 percent of the total sales.
Bicycle Repair and Fitting Sales
Other Areas of Concern
Aside from the discussed financial and non-financial performances above, it is important to note that bulk or about 70 percent of the Peleton Ltd’s sales come from the high street retail channels while 30 percent come from online sales. The online sales, however, registered the fastest growth in 2015 with 19 percent increase while the retail sales recorded 15 percent increase.
The 2015 retail sales was GBP 3.19 million which is a high growth from the 1.33 million in 2009. The online sales, on the other hand, was GBP 1.46 million in 2015 which is much a higher growth from the recorded GBP 570,000 in 2009. The cost in retail sales is also higher, about 30 percent of its gross sales, while online sales incur smaller cost of about 10 percent of its gross sales.
Retail Sales
Online Sales
Recommendation
Jeremy Bradley (2015) stated in his article in Chron that the objectives of the Human Resource Theory are cost-effectiveness, leveraging potential to ensure that the workload are achievable, matching the skills and career objectives of the employees, and managing good relationship among the company’s stakeholders that include owners, managers, employees, and customers.
Based on the financial and non-financial data above, we can say that the human resource department effectively performed its roles and objectives. The company operated in a cost-efficient way as reflected in the areas of spending that are lower than the budget, but continued to deliver quality products and services. The table below will show that the company underspent in the areas of spending.
Relevant Budget and Actual Spending for 2015
In addition, the low turn-over rate and high customer satisfaction rate, and strong revenue growth suggest that the company has properly leveraged workload, matched the skills and career objectives of the employees, and fostered good relationship among the stakeholders.
The financial health, customer relationship, and numbers of employees growth of Peleton Ltd reflected that the human resource theories on satisfaction of needs, motivation, decision making, and participative decision making were met (Lardbucket, 2015). Therefore, it is recommended that the human resource department be retained as it effectively performs its roles.
Further to continue enhance the cost-effectiveness measures of the company to maximize profit, it is recommended to strengthen the online sales as it incurs less cost but high growth. Such can be done by implementing online programs such as fastest delivery/ within-the-day delivery of bicycles and bicycle components at a low/competitive cost. The company should be able to close deal with a cost efficient shipping services provider that will be able to serve the company’s delivery needs. Discounts or freebies shall also provide for online purchasers if they buy a certain number of goods from the company.
The human resource department should continuously innovate programs that will continuously enhance human relations of the stakeholders and employee and customer loyalty.
References
Agile Data. 2015. Introduction to Data Normalization: A Database "Best" Practice. Retrieved from http://agiledata.org/essays/dataNormalization.html
Brown, John Murray. 2014. UK bicycle component producers ride sales boom. Financial Times. Retrieved from http://www.ft.com/intl/cms/s/0/7b477768-bc16-11e3-a31c-00144feabdc0.html#axzz3wCnv7CEw
Harker, Jonathon. 2014. UK bike sales grow 14% to £745 million. Bike Biz. Retrieved from http://www.bikebiz.com/news/read/uk-bike-sales-grow-14-to-745-million/016303
Kaplan Financial Knowledge Bank. 2015. Performance Management. Retrieved from http://kfknowledgebank.kaplan.co.uk/KFKB/Wiki%20Pages/Financial%20Performance%20Indicators%20(FPIs).aspx
Financial Times. 2015. Definition of non-financial performance measures. Retrieved from http://lexicon.ft.com/Term?term=non_financial-performance-measures
Moore, Malcolm. 2015. UK cycling industry sees space for more growth. Financial Times. Retrieved from http://www.ft.com/intl/cms/s/0/67eafdf2-5319-11e5-b029-b9d50a74fd14.html#axzz3wCnv7CEw
Bradley, Jeremy. 2015. What Are the Major Objectives of Human Resources Theory? Chron. Retrieved from http://smallbusiness.chron.com/major-objectives-human-resources-theory-61815.html
Lardbucket. 2015. 3.3 Human Resources Theories. Retrieved from http://2012books.lardbucket.org/books/an-introduction-to-organizational-communication/s05-03-human-resources-theories.html
