Coursework Brief
For this coursework you are required to solve two problems using Microsoft Excel and Microsoft Access. Your solutions should be word-processed, including output from these programs, and submitted electronically.
In each case, your solution should include both the output itself and a detailed account of the methods you have used to produce the output (e.g. the menus and icons used to achieve the results). Where appropriate you should also explain the reasons you have chosen those particular methods.
Question 1:
A project requires different numbers of employees on different days of the week. Each employee must work 5 consecutive days, and then have 2 days off. We want to minimise the number of employees to be hired.
The number of employees required each day is:
Sun 17; Mon 14; Tue 14; Wed 14; Thur 18; Fri 18; Sat 12.
Using Excel Solver, calculate the minimum number of employees to be hired, and list the number actually hired each day. Explain carefully how we ensure that the solution does not include negative or fractional numbers of employees.
On some days there will be more people employed than are actually required. For example, if our solution involves employing 15 people on Saturday, the number of “excess” workers on that day is 3. While a small number of excess workers can contribute usefully to the project, it is undesirable to have a large number on any particular day. As a secondary objective we would like to minimise the largest number of excess workers employed on any day. Modify your solution to include that extra constraint. What is the largest number of excess workers in your final solution?
Question 2:
The Excel workbook “Orders” (available on Blackboard) contains two worksheets, “Orders” and “Customers”. “Orders” contains details of 673 orders, including the date of the order and the shipping address. OrderID is a unique identifier for each order. The CustomerID identifies the customer, referencing the field of the same name in the “Customers” sheet, where it identifies uniquely the contact person for that customer. The contacts are not necessarily based in the same city as the shipping address.
We wish to invite all contacts based in London to a reception, and to facilitate this we need a printout of all orders where the contact has a London address.
Import the Excel worksheets into Access as tables, defining suitable primary keys, and establish a suitable “relationship” between the CustomerID fields in the two tables, enforcing the referential integrity. Then create a Query to produce a table of orders with contacts based in London. Include the following fields: Company Name, OrderID, Order Date, Contact Name, Phone Number, and the City to which the order must be shipped. [Note that you will also need to include the “City” field for the Contact in the query, in order to select the London based ones, but that field should not be printed out.] Finally export the resulting table to a Word File (in rtf format) for inclusion in your submitted work.
Key Marking criteria will include:
• Accuracy of the numerical solutions
• Appropriateness of the methodology
• Clarity and conciseness of the descriptions of the methodology
Word Limit
The word limit for this individual assignment is 1000 words.
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.
[order_calculator]
