Homework answers / question archive / MGMT2006 – Management Information Systems I Course Project MGMT Bakehouse Inc

MGMT2006 – Management Information Systems I Course Project MGMT Bakehouse Inc

Business

Share With

---

MGMT2006 – Management Information Systems I

Course Project

MGMT Bakehouse Inc.

CASE BACKGROUND

MGMT Bakehouse supplies bread, cakes and other specialty products, to a large percentage of the country; to the following customer types. Retailers e.g. supermarkets, who sell baked goods to the public, and MGMT Bakehouse delivers to the retailers. Wholesalers also sell to the public,

but come to MGMT Bakehouse directly to collect their baked goods. Finally, institutions like hotels and hospitals, to whom MGMT Bakehouse delivers directly, buy baked goods to incorporate into offering for their clients e.g. guests menus and patient meal plans. 

 

From the ordering of flour and other ingredients to the processing of cash, receivables and payables, the company uses an integrated ERP with a common database for all of its business processes. Internally MGMT Bakehouse is organized into the following departments: Production which bakes the bread; Distribution that delivers the bread; Sales Department where customer orders and wholesale pickups are managed; Marketing that promotes and manage the company’s branded products; Finance and Accounting that manages the customer accounts; and HR which manages staffing and other related matters.

 

Returns management is all about the details. In a market where the customer can return stale bread for up to a 100% refund, having ‘Returns’ is like burning paper money. Supermarkets and wholesalers can return bread but not institutions like hotels and hospitals. All baked goods have a shelf life of one week. That is, bread that is sold on Monday is returned the following Monday. Most customers place orders in advance but wholesalers and hotels are sometimes ad-hoc and the company has to project/predict their orders.

The company wishes to maximize sales while minimizing returns. This requires them to anticipate the true demand but also to know which products and which locations are having problems on a timely basis. As a new manager with the company, you are tasked with analysing the provided information and reporting your findings and possible recommendations at the upcoming meeting.

 

 First, you need to peruse the database. It comprises the following tables:

PRODUCTS: A list of all the baked goods supplied by the company.

CUSTOMERS:	A list of all the customers of the company.
CUST_TYPES:	A list of the types of customers the company services.
ROUTES:	A list of the geographic routes that delivery trucks service.
SALES:	A table of statistics which include true demand, actual sales, and returns.  The data is for three months in the first quarter on the financial year.

You are asked to prepare several queries, as well as some reports. 

 

Pre-Project Suggestion: 

• A step-by-step exercise is available for you to follow which imports data from Excel to Access and provides examples on creating queries and functions listed above. This will help you to become familiar with database management systems.
• You should complete these tasks as they will greatly assist you BEFORE you begin your project.

 

Project Requirements                             Due week 8 (27 Oct) – 25%

 

You are given a database containing the previously described tables to provide some results for MGMT Bakehouse Inc. (That is, your CC will provide you with the database). You should:

1. Rename the database as MGMT2006_G# <first name last name>, where G# is your group number.
2. Create relationships between pairs of tables and enforce referential integrity as necessary
3. Add your name as a new CUSTOMERS and associate it with a customer type and a route. 
4. Also TWO new PRODUCTS for your new customers along with SALES information for TWO sales transactions.
5. Then add TWO new SALES records for TWO existing customers.

 

6. Create the following queries:
   1. List the names and addresses of all wholesale customers. Name this query Q1-WholesaleList.
   2. Produce a query that shows the top 12 products with the greatest demand.  Show the product name and demand fields.  Sort the demand field in descending order.  Name this query Q2-Top12InHighDemand.
   3. What is the average selling price for all “Bun” products? Include the product ID, product name and average selling price. Name this query Q3-AvgBunPrice.
   4. How many customers from each parish had total sales of more than $150,000. Sort in descending order by sales.  Name this query Q4-TotalSalesByParish
   5. (In Microsoft Access) Returns continue to be of concern for management. Create a query that shows customer name, product name, total sales, total returns, returns percentage, where the percentage is greater than 70%. Name this query Q5-ReturnsPercentOver70.

OR

e. (In Libre Office Base) Create a parameter query that accepts the name of a parish and displays the number and types of customers, their total sales and total sales returns. 

Name this query Q5-SalesReturnsPerParish. 

7. Use query Q3-QvgBunPrice to create a chart that shows at a glance, in descending order the distribution of averages.  Name this chart “Distribution of Product Averages” 
8. Create a report that produces the month, total sales, total returns and net sales, by route and format the columns as currency. However, for each route, show a detail line for each of the three months in the quarter. Show subtotals for each route and sort each route by month in ascending order. Adjust the columns to display all the data. Print the information on each route on a new page. Title (and name) the report “Quarterly Route Sales”. 

 

Hint: For queries, when using the totals options, consider the “where” and

 

“expression” clauses also.

 

9. (a) In Microsoft Access, create a switchboard named MGMT Bakehouse Menu System.  Include two queries, the database report and an exit option.  At the bottom centre of the switchboard, create a label using a 10-point font which states that you created the switchboard.  For example, ‘Created by <firstname lastname>

 

            OR

9. (b)  In Libre Office Base, create a form that shows all the fields from the Customers table in the main form.  Create a query which shows CustID and the total sales, to be used as the subform.  Make sure that the arrangement for the main form is different from the arrangement for the subform and that only one customer record is seen at a time.  The form must not allow existing data modification or deletion.  Insert a list box for CustTypeName next to CustType and a llist box for RouteName, next to Route, in the Customer Table.  Title (and name) the form “Customer Sales”.  At the bottom right, create a label that states that you created the form.  For example, “Created by <firstname lastname>.

 

POWER BI

 

10. If you installed Power BI then, 
    1. Import the data into Power BI. You can use either Access or Excel to import from.
    2. Ensure that the relationships between the pairs of tables are linked. 
11. If you did not install Power BI, and are using the Web Service only then,
    1. Use the pbix file provided on the course page.
12. Save your file as MGMT2006_G#_<first name last name>. For example, MGMT2006_G50_ Jane Doe. 
13. Create the following Power BI dashboard on one page:
    • A map (of Barbados) that indicates the amount of business done in each parish. Use sized circles to represent the sales volumes.
    • A donut chart to show the amount of business by customer type.
    • A Matrix visualization, sorted by returns, in descending order, and also showing sales, customer name, route name and product name. Title the matrix as “Top Returns Visualization”. Demonstrate that the matric can drilldown to any combination of route name and/or customer name and/or product name by including two (2) different views of the report in your final WORD document. 
    • A Treemap visualisation which shows for each Route, the total sales and the parishes in that route.

 

             

PROJECT (WORD) REPORT FOR SUBMISSION

 

To satisfactorily complete this part of the project, you should prepare a written report based on the Access queries and report, and the Power BI dashboard:

Create a word-processed report saved as MGMT2006_G#_<first name_last name>, (For example, MGMT2006_G50_ Jane Doe). All pages should be numbered. It must include:

1. A cover page comprising the UWI Open Campus title, course code and course name, the name of the Coordinator, your E-tutor, and group number, the semester and due date.

       (Remember your Orientation Activity?).                                           

2. A section discussing the findings of each query. Include copies of the query results. This means you should: 
   1. Type the question asked. (For example, ‘List the names and addresses of all

                      wholesale customers  …’)                                                      

1. 2. (i) Report on the findings from your query or Power BI. That is, a sentence or two on the results of the query as it relates to the business. Identify any outstanding

(exceptional) information which may arise from the query, IF NECESSARY. 

(ii) include the actual query. (That is, copy and paste your query from your Access / Libre Office Base database or Power BI that answers this question, to your Word document).       

1. 3. Type the next question and repeat point (b).
   4. Include a copy of Quarterly Route Sales report exported from your Access database/Libre Office Base.  (See MS Access handout on how to achieve this.  For Libre Office Base, use the natural remedy – copy and paste.). 
   5. Include a copy of your Pivot chart from Excel.
   6. Include two screen shots showing different views of the matrix visualisation from Power BI.

 

Remember, this is a report that that you ‘should’ be submitting to the administration of MGMT Bakehouse Inc. Please be professional in your reporting and presentation.

 

 

Draft Drop Box

You can (OPTIONALLY) upload your draft database ONCE for your e-tutor to review. Note that this is not for grading or for your e-tutor to guide you on how to arrive at a solution if you need assistance. The deadline to upload your draft is the WEDNESDAY BEFORE the deadline for the project. (The Draft Drop Box closes on Wednesday, 20 October, at midnight).  

 

The Late Box

If you are unable to submit your assignment by the deadline, you will be allowed ONE additional week (five days) to submit. However, your FINAL mark will be out of 15% instead of 25%.

Your work will still be graded and feedback given.

Please use the discussion forum for general questions on the project that other class members can answer or help each other.

 

C. Gibson

October 2021

 

MARK SCHEME ON NEXT PAGE

COURSE PROJECT Semester 1 2021-2022

 

Mark Scheme

Due week 8 (27 Oct)

50 marks total – 25%

 

Construct several queries and prepare reports. (Unit 4)

 

TABLES	Correct Database name  Correct Relationships	3 marks
WORD REPORT	Word report named appropriately All required information on cover page  Page numbering TOC automatically generated Overall good presentation	5 marks
RECORDS	Addition of data and records to the tables	2 marks
QUERIES	Word Report for EACH query, you MUST have the question stated before the result of the query  For Queries a to e:  ? Correct Query Result + Comment Note: Query RESULTS MUST Resemble Access Queries. No reproduction using MS Word tables is required or expected	15 marks
REPORT	Report title  Date inserted Grouping, sorting of fields Appropriate headings and page orientation Each set of info on a new page	6 marks
EXCEL	Pivot cart Title and Axes are labelled	4 marks
ACCESS SWITCHBOARD       OR   LIBREOFFICE FORM	Switchboard name Two queries and the report appropriately placed on switchboard Switchboard Exit button “Created by” information placed in middle at bottom of switchboard	5 marks
	Form name/Title All fields appropriately placed and legible Main and subforms have different views Customer type name and route name inserted Created by… information placed on form	5 marks
POWER BI	Map to show the density of the business (2) Chart to show the business by customer (2) Matrix (with title) of returns and sales (3) Treemap visualisation (3)	10  marks
	Total	50 marks (25%)