Please to help me
CASE: FORESTRY DEPARTMENT of JAMAICA MGMT2004 Computer Applications Group Project, SPRING 2021 MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 2 Contents INTRODUCTION ................................ ................................ ................................ ................................ ............ 3 SECTION A: SEEDLING SALES ................................ ................................ ................................ ........................ 4 Design S pecifications ................................ ................................ ................................ ................................ 4 Implementation Requirements ................................ ................................ ................................ ............... 7 SECTION B: TIMBER LICENSING ................................ ................................ ................................ .................... 8 Design Specifications ................................ ................................ ................................ ................................ 8 Implementation Requirements ................................ ................................ ................................ ............. 10 SECTION C: EXECUTIVE SUMMARY ................................ ................................ ................................ ............ 12 MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 3 INTRODUCTION The Forestry Department is an Executive Agency of the Government of Jamaica responsible for the management and conservation of Jamaica’s forest resources. The agency provides a variety of products and services that include creating digital maps and aerial phot os, selling seedlings and potted plants, issuing licenses and permits to cut/extract timber or forest produce, renting its training facility, and conducting an annual forest hike/trek 1. The Agency is also responsible for overseeing conservation and refore station, primarily on government/crown land and to a lesser extent on private land. As a licensed contractor, you are particularly interested in incorporating indigenous products into your developments, both for construction and landscaping. Having approac hed the Forestry Department for information on local timber and seedlings, you were directed to the Forest Operations Division for details on licensing, permits, and prices. Upon learning that you were a graduate of a prominent university, and quite savvy in Computer Applications, the manager you met with, Mrs. Laura Miller 2, asked for your help in doing some analysis of Forestry data. You offered to develop two prototypes to analyze Seedling Sales and Timber Licensing in MS Excel © and Access © respectiv ely. Mrs. Miller provided you with the following for both models: • Design specifications and implementation requirements , which are outlined in Sections A and B . • Test data for the last quarter of 2019 in the MG MT2004_ForestryData workbook. She further explained that the Forest Operations division is divided into two zones, Eastern and Western, and data is captured by zone. Licensing details have been kept to a minimum and masked to preserve confidentiality. 1 ForestryDepartment | Home 2 Name changed MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 4 SECTION A: SEEDLING SALES [60 MARKS ] Design Specifications The purpose of this module is to calculate revenue and general consumption tax for seedling sales. The Forestry Department sells four (4) categories of seedlings: Timber, Food/Fruit, Ornamental, and Wildlife. Figure 1 contains a sample of the unit prices and the full list is provided in the Figure1_Prices worksheet. Figure 1. Seedling Prices Seedling /Species Category Price (S) Ackee Food/Fruit 200 Cashew Food/Fruit 350 Neem Ornamental 200 Poincianna (Tangerine - Red) Ornamental 100 Poor man's orchid (Purple) Ornamental 150 Blue Mahoe Timber 50 Caribbean Pine Timber 50 Burnwood WildLife 150 Hogberry WildLife 100 Wild Ackee WildLife 150 Seedlings in the Timber and Food/Fruit categories are not taxed, and those in the other categories are subject to General Consumption Tax (GCT) at a rate of 15%. In honor of National Tree Planting Day, which is celebrated in October each year , the Forestry Department discounts the price of all seedlings for the entire month . Discounts vary by category as follows: Figure 2. October Discount % by Category October Discount % Cat egory 50 % Food/Fruit 30% Ornamental 40% Timber 20% Wildlife MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 5 For example, Ackee seedlings (in the Food/Fruit category) normally sold for $200 each would be sold for $100 each in October . Similarly, Blue Mahoe seedlings (in the Timber category) normally sold for $50 each would be sold for $30 each in October. You have been provided with the volume sales for each seedling by zone and month for the last quarter of 2019 and are required to complete a Revenue Report, the layout of which has been provided in Figure 3, as well as a Dashboard shown in Figure 4. Use the data in the Figure 3_Report worksheet for both. Figure 3. Revenue Report Outline Zone Month Seedling Qty Sold Category Taxed? Item $ Sales before Tax Item $ Sales after Tax Eastern Oct Ackee 2960 Western Oct Ackee 7925 Eastern Nov Ackee 12081 Western Nov Ackee 3262 Eastern Dec Ackee 8868 Western Dec Ackee 7467 Eastern Oct Almond 11895 Figure 4. Dashboard Layout Dashboard Total GCT Collected for the Quarter: (i) By Category: Total (S) % of Total Ornamental (ii) (iii) Wildlife (iv) (v) Total Earnings for the Quarter (vi) By Zone: Total (S) % of Total Eastern (vii) (viii) Western (ix) (x) MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 6 Figur es 5 and 6 explain the columns in the Revenue Report and Dashboard respectively. Figure 5. Revenue Report Field/Column Description Zone Eastern or Western. Given Month Oct or Nov or Dec . Given. Seedling Seedling Name/Species. Given. Qty Sold Volume Sales. Given Category Seedling category: Timber or Food/Fruit or Ornamental or Wildlife . Calculated. Taxed? Indicates whether or not the item is taxed and should result in Yes or No . Calculated. Item $ Sales before tax Item Sales in $ before GCT, which factors in any discount . Calculated. Item $ Sales after tax Item Sales in $ including GCT where applicable . Calculated. Figure 6. Dashboard Calculations Value Calculation (Note ALL calculations are for the period Oct – Dec 2019) (i) Total GCT collected (ii) Total GCT collected for Ornamentals (iii) Ornamental GCT as a proportion of Total GCT (iv) Total GCT collected for Wildlife (v) Wildlife GCT as a proportion of Total GCT (vi) Total earnings (vii) Total earnings for the Eastern division (viii) Eastern earnings as a proportion of Total Earnings (ix) Total earnings for the Western division (x) Western earnings as a proportion of Total Earnings Mrs. Miller is particularly interested in using the data for storytelling and hopes that your data visualization skills are on point. She also insists that your color scheme be consistent throughout the solution. MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 7 Implementation Requirements 1. Create a separate worksheet named Q1Inputs that contains all inputs needed to develop the report. 2. a. In a work sheet named Q2Revenue create the Revenue Report that contain s all the columns outlined in Figure 5. Also, complete the Dashboard calculations as outlined in Figure 6. Both should be well presented. Do not add extra columns. b. For the purpose of triangulation i.e., using different methods to cross check results, create a pivot to the right of the Dashboard to validate the GCT calculation s in the Dashboard. Display total Item $ Sales before tax and total GCT by category for the taxable categories only. Do not add columns to the original data. (Hint: Calculated field). 3. In a worksheet named Q3Top3 display the total quantity and total Item $ sales before tax for the top three (3) seedlings in each category. Mrs. Miller wants to be able to view this information for each month on a separate page. The top three (3) should be based on dollar sales. 4. Display total volume sales by category by month. It should be arranged so that you can show , in addition to the monthly totals, the total volume sales trend for all three months as a sparkline for each category. Give Mrs . Miller the ability to view this i nformation for each Zone on a separate page. Name this work sheet Q4VolumeTrend. 5. Create a stacked chart to show, by month, the percentage contribution of each category to total Item $ Sales before tax. Name this sheet Q5Proportion . 6. In a worksheet named Q6Ornamentals , determine the average quantity for Ornamentals in each zone. Determine which Ornamentals sold above average (quantity) in their respective zones and use conditional formatting to highlight these rows. (Hint: you can con ditionally format using complex criteria.) MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 8 SECTION B: TIMBER LICENSING [60 MARKS] Design Specifications The purpose of this module is to track licenses issued to cut /extract timber from crown (i.e. government) lands and their associated revenue. Based on a licensee’s needs, a block of land is identified in a forest reserve and the applicant is charged an overall license fee for the block as well as a fee for each tree that is felled/cut down as foll ows: • License fee, determined by block size /area in hectares and density of the land • Stumpage charge (per tree), determined by the type of timber and size/volume of the tree.Tree size /volume is measured in cubic metres ( m 3). The License fee attracts GCT. The database consists of three (3) tables: Timber, License, and Produce and you have been provided with data for the last quarter of 2019. The Timber table captures timber name and unit price in m 3. Figure 7 provides the table design and Fig ure 8 provides a sample of the data (not the data in its entirety.) Data for this table should be imported from the tblTimber worksheet. TimberID is the primary key. Figure 7. tblTimber Design Field Name Field Description Data Type Field Size Comments Timber ID Own er ID. Uniquely identifies each timber Short Text 10 PK Common Name Tree’s common name Short Text 20 ScientificName Tree’s scientific name Short Text 40 TimberPrice Price per cubic metre (m 3) in Jamaican $ (JMD) Currency AvgYrsToMature Average Years to Maturity Number Byte Notes Short Text 100 MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 9 Figure 8. tblTimber Sample Data TimberID CommonName ScientifcName TimberPrice AvgYrsToMature Notes bMahoe Blue Mahoe Hibiscus elatus $3,707.00 20 cPine Caribbean Pine Pinus caribbea $4,544.00 15 jCedar Juniper Cedar Juniperus lucayana $8,540.00 65 The License table contains license details including location, block size (in hectares), density, and license fee. Density is categorized as sparse, moderate or dense. License fee per hectare before GCT is quoted in U.S. dollars (USD) as follows: $100 for sparse, $75 for moderate, and $50 for dense. This is converted to JMD based on the JMD:USD exchange rate at the time of application. Incorporate an Exchange Rate table into your database , relate it where appropriate, and populate with the Bank of Jamaica c ounter selling rates for the last quarter of 2019. Recall that, from a database perspective, no table can be an island. LicenseID is the primary key and values begin with NE for north east, SE for south east, NW for north west, and SW for south west. NE and SE are subdivisions of the Eastern zone, whereas NW and SW are subdivisions of the Western zone. Figure 9 provides the table de sign and Figure 10 provides sample data. Data for this table should be imported from the tblLicense worksheet. License Data is depicted in mm/dd/yyyy format. (Remember to check the date format on your computer) Figure 9. tblLicense Design Field Name Field Description Data Type Field Size Comments License ID Uniquely identifies each license Short Text 10 PK LicenseDate License date of issue Date/Time Short Date BlockSize Parcel area in hectares Number Single Density Parcel density – categorized as Sparse, Moderate, or Dense Short Text 10 Listbox Fee License fee in Jamaican $ (JMD) Currency Figure 10 . tblLicense Sample Data LicenseID LicenseDate BlockSize Density Fee NE1020 10/15/2019 0.5 Dense SE5673 11/20/2019 1.75 Sparse NW7012 12/4/2019 1.5 Moderate SW8004 10/3/2019 1 Dense Eastern Zone Western Zone MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 10 The Produce table captures details of every tree felled in the block of land associated with each license. Figure 11 provides the table design and Figure 1 2 provides sample data. Data for this table should be imported from the tblProduce worksheet. ProduceID is the primary key. Figure 11. tblPro duce Design Field Name Field Description Data Type Field Size Comments Produce ID Uniquely identifies each felled tree Autonumber Long Integer PK LicenseID Permitting license Short Text 10 TimberID Type of tree Short Text 10 TreeSize Tree volume in cubic metres (m 3) Number Single Figure 1 2. tblProduce Sample Data LicenseID TimberID TreeSize NE1020 cPine 2.18 NE1020 jCedar 4.62 NE1565 cPine 4.06 NE1565 jCedar 3.19 NE1565 sElm 2.96 NE2120 cPine 3.17 Implementation Requirements 7. Create the Timber, License, and Produce tables by importing data and modifying designs per specifications. All foreign keys should have a lookup and e nforce referential in tegrity. Construct the following forms/q ueries/reports and name as indicated: 8. [query name: qryQ 8CalcFee ] Create an update query to determine Fee for each license . Recall that fee is based on block size (in hectares) and density , and is subject to tax 9. a. [query name: qryQ 9aNone ] Display all details for any timber that has not been supplied on any license in Nov ember 2019. b. [query name: qryQ 9bStumpage] Create a crosstab query that displays the number of trees felled by Zone (i.e. Eastern or Western] by Timber (i.e. Common Name ). Also display the total number of trees for each timber and each zone . MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 11 10. [query name: qryQ 10 Density] Prompt the user to enter a specific density and list the three (3) most popular timbers (by common name) felled on such parcels of land and their respective total sizes/volume (m 3). (Test your query with spar se ) 11. [form name: frmQ11Timber] Mrs. Miller would like to view specific license details for species of timber that take at least 30 years to mature on average. For each such timber, display its timber details (i.e. common name, scientific name etc.) and a list all the licenses used to h arvest the timber, license dates, and total volume harvested . The form should also display the grand total volume harvested for the timber across all licenses. 12. [report name: rptQ 12 Revenue ] Create a revenue report for licenses granted in December 2019 that lists, by license, the following details for each timber: common name, number of trees, average tree size(m 3), and stumpage revenue. Calculate total revenue per license, which should include the fee. Use the layout in Figure 1 3 as a guid e. (8 marks) Figure 1 3. Report Sample Layout Note: License fee is estimated in JMD (before tax ) for illustration as follows: $10,000 for sparse, $7500 for moderate, and $5000 for dense. MGMT2004 Computer Applications, Group Project, Spring 2021 Page | 12 SECTION C: EXECUTIVE SUMMARY [20 MARKS] The executive summary should be submitted as a document that contains: i. Title page – include group members’ names and ID numbers ii. Table of contents iii. Problem/Opportunity overview iv. Spreadsheet discussion of additional assumptions made (if any), limitations, and suggestions for improvement. v. Database discussion of additional assumpt ions made (if any), limitations, and suggestions for improvement. vi. Conclusion – articulate the benefits that the organization should derive from using your solution.
