COMPUTER BUSINESS SYSTEMS 1110
BUSINESS PROBLEM SOLVING WITH SPREADSHEETS
A S S I G N M E N T 3
I N S T R U C T I O N S
• This assignment assesses your understanding and application of the course concepts. The instructor
will answer “how to” questions after the assessment is graded.
• Complete this assignment using Microsoft Excel 365 version or higher on a Windows-based system or
MacBook.
• This assignment is to be INDIVIDUAL effort only.
• To hand in your assignment, upload the file on Moodle. Email submissions are not accepted.
• Keep backup copies of the completed assignment file. ZERO will be assigned should a backup be
unavailable.
M A R K I N G R U B R I C
| Criterion | Exemplary (A) | Proficient (B) | Developing (C) | Beginning (D & F) |
| Problem Solution |
All information was accurate and was presented effectively. Subject knowledge was thorough and reflect an in-depth understanding of the problem. |
All information was mostly accurate and was presented effectively. Subject knowledge was evident and reflect a superior understanding of the problem. |
Information was relevant and presented appropriately, but not predominately accurate. Subject knowledge reflects a basic understanding of the problem. |
Information was confusing or irrelevant and/or not presented appropriately. Subject knowledge was not sufficient to answer the essential question and reflect a limited understanding of the problem. |
Q U E S T I O N S
| This assignment covers weeks 10-13 lecture videos and files that encompass applications of linear programming to solve scheduling and transportation problems and managing datasets in Excel. Solve each problem in its own worksheet and identify each sheet with the question number. Create a new file. Use the following naming convention to rename the file: Your last name, the last four digits of your student number, section name, and then the text “Assign3”. Example: Smith1234s10Assign3.xlsx Unless otherwise directed, solve each problem below in its own worksheet and identify each sheet with the question number (for example, q1, q2, q3 …). Order the worksheets sequentially. |
||
| Create a new worksheet positioned in the beginning of your file named Honour. In the worksheet, include your full name, student number, section name and the following honour pledge. Include a picture of your signature. NOTE: The assignment will not be marked if this honour pledge is missing. I affirm that I have not given or received any unauthorized help on this assignment, and that this work is my own. Your Signature Picture Here |
||
| 1. | Copy the worksheet from the college file to your workbook. This worksheet contains the query result from Alita University’s database. Rename the worksheet q1. Alita’s management would like subtotals inserted in the list displaying the average financial aid amount by major, and then under each major the number of students by city. |
5 |
| 2. | At the end of last year, there were 415,000 elephants left in the world. Each year 3.8% die of natural causes and 6% from poaching. The annual birth rate averages to 6.1%. Suppose these rates continue, use goal seek to determine in how many years, elephants will become extinct. Hint: use the formula: population*(1+br-dr-p)^n, where br is the annual birth rate, dr is the annual natural death rate, p is the annual poaching rate and n is the number of years. Conservation initiatives are bringing awareness to the plight of elephant population and, in turn, impacting consumer sentiments towards poached products. Suppose you want to analyze elephant population further at the extinction year determined above, build a sensitivity analysis using Excel’s one-variable table tool to show elephant population given varying poaching rates (from 0% to 20% at 1% intervals). |
10 |
| 3. | Copy the worksheet from the brew file to your workbook. The dataset represents information on sales for Brewster Kettle. a) Make a copy of the brew worksheet and name the copy q3a. Apply filters to display orders of German Schwarzbier delivered in February 2020 with Total amounts below $30. b) Make a copy of the brew worksheet and name the copy q3b. Apply filters to display all forms of stout with order dates between May 15 to 20, 2020 (inclusive) or orders with quantities between 45 to 48 (inclusive) that have subtotals below $34. c) Make a copy of the brew worksheet and name the copy q3c. Apply filters to display all forms of beer with Lager in its name that took 3 or under working days (exclude Sat & Sun) to ship with total order amounts above $100. |
10 |
| 4. | Hens Inc. require farm workers from 6 AM to 6 PM. Full-time staff work 8 hours and start either at 6 AM or 10 AM. Part-time staff work 4 hours per shift and start either at 10 AM or 2 PM. The company pays full-time workers $175 per shift and part-time workers $80 per shift. Hens Inc. can schedule no more than 4 part-time workers each day. The company needs to schedule the number of workers required for each 4-hour block as shown below. Develop a linear Solver model to determine how the company can minimize daily labour costs. 4-Hour Block Required 6 AM – 10 AM 8 10 AM – 2 PM 12 2 PM – 6 PM 9 |
10 |
| 5. | Copy the worksheet from the Alita file to your workbook. Alita University would like to analyze this dataset. Based on their comprehensive experience, Alita’s management believes that enrollment patterns at the university follow these trends: A. Enrollments are highest in Business. Management wants to know if there is a way to allow selection of one or multiple cities and see the data reflected in the summary? If so, include one. B. There is more full time than part time students in every one of Alita’s departments. C. Typically, students that apply in April perform the best at the university. Do not include students that applied this year. D. For credit hours grouped by 30 starting with 0, enrollment is highest within the 0- 29 credit hours grouping. Is Alita’s management correct? Display supporting data in a subsequent worksheet. |
10 |
| 6. | Copy the three worksheets from the health file to your workbook. These worksheets contain the hospital admissions for city hospitals. Health authorities would like to consolidate the data to find the average admissions, emergency and ICU numbers by province. Do not use the pivot table tool. For the summary, sort the provinces from West to East, then territories from West to East: BC, AB, SK, MB, ON, QB, NB, NS, NL, PE, YT, NT, NU |
5 |
| 7. | Spot Automotive manufactures the SG electric cars. The company operates two manufacturing plants, one in Abbotsford, BC and another in Toronto, ON. The Abbotsford plant can manufacture 5,500 cars each week and Toronto 4,000. Every week, the company ships enough cars to exactly meet the demand of six dealerships. However, a plant cannot ship cars to a dealership within the same province. For example, the Abbotsford plant cannot ship cars to the Surrey dealership. The cost of shipping a car over 10 km is $1.25. What is the most cost-effective method to ship each dealership their demand? |
10 |
Dealership Demand
Surrey, BC 1,500
Calgary, AB 1,200
Regina, SK 1,000
Winnipeg, MB 1,400
Ottawa, ON 3,000
Moncton, NB 1,000