BIZ3189 Management Information Systems Take-home Exam 1 The tasks in this exam aim at developing a basic level of understanding on Excel and how you can use it to develop Decision Support Systems. There are four major parts in this exam. Part L. Create a Weekly Sales Report In the first part, there are a series of tasks to complete within one saved workbook (file) which will contain one worksheet (Cumulative). Instructions 1) Name a worksheet as \"Cumulative\" 2) Center the spreadsheet heading \"Weekly Sales Analysis across the spreadsheet. Type in all text and numbers and format them shown in the spreadsheet below. (Ensure that each cell has the right data) A o C D E F Weekly Sales Analysis 1 2 Date Daily Sales (units) |Sales Share |Cumulative Sales (units) |Cumulative Percentage 3 2023-01-01 50 4 2023-01-02 23 5 2023-01-03 45 6 2023-01-04 34 7 2023-01-05 60 8 2023-01-06 34 9 2023-01-07 39 10 11 Total Sales I:I 13 |Average (daily) Sales 14 Standard Deviation 15 Maximum 16 | Minimum 3) Create formulas to display a total for sales, average, standard deviation, maximum and minimum. Then write formula to calculate the sales share of each day. Sales share of a day is calculated by dividing the sales on that day divided by the total sales in the week. Then write another formula to calculate the cumulative sales (cumulative sales on a day is equal to sum of all the prior sales and the sale on that day) as well as cumulative percentage. Your final worksheet should look similar to the one shown below. BI73189 Management Information Systems Exam 1 Page 1 of 10 Weekly Sales Analysis Date Daily Sales (units) Sales Share Cumulative Sales (units) Cumulative Percentage 2023-01-01 50 17.54% 17.54% 2023-01-02 23 8.07% 73 25.61% 2023-01-03 45 15.79% 118 41.40% 2023-01-04 34 11.93% 152 53.33% 2023-01-05 60 21.05% 212 74.39% 2023-01-06 34 11.93% 246 86.32% 2023-01-07 39 13.68% 285 100.00% 1 Total Sales 285 100.00% WN 3 Average (daily) Sales 40.71 4 Standard Deviation 12.13 5 Maximum 60 6 Minimum 23 4) Save the workbook file. PART II: Creating an Employee Payroll Report Background Jack and Jill bakery has 15 employees. Each employee earns an hourly wage and a commission on total weekly sales based on the job they perform. The employee can invest in a pre-tax retirement account where they may designate a percentage of earnings to be invested in this retirement account. Employee information is provided in the following table: Table 1.1 Employee Information Employees Job Hours Hourly wage Retirement worked contribution Bob Jones Waiter 20 13 0.00 Susan Hu Waitress 40 22 0.05 Guy Lam Waiter 33 15 0.00 Laura Wilson Waitress 37 18 0.01 Jack Grey Chef 40 26 0.02 Jack JeanJean Chef 38 20 0.04 Ed Hillary Chef 30 15 0.03 Mike Magic Chef 20 25 0.05 Mary Gold Chef 32 15 0.02 Paul Martin Chef 45 30 0.03 Brian Smith Driver 38 20 0.04 Steve Jones Driver 32 15 0.00 Kyle Jones Driver 40 15 0.04 Larry Lo Manager 35 25 0.05 Roger Fed Manager 45 30 0.03 BIZ3189 Management Information Systems - Exam 1 Page 2 of 10Task 1.1: Prepare an Excel spreadsheet application that meets the following specifications Output Requirements: Create an Excel spreadsheet application that Jack and Jill Bakery can use to calculate regular pay, retirement contribution amount, total pay amount, basic tax amount, income tax amount, net pay amount for all employees. Input Parameters and Assumptions: The bakery's weekly sales vary, and the average weekly sales amount is $16,000. The basic tax rate is 6.2% and the income tax rate depends on weekly total pay as follows: Table 1.2 Income Tax Rates 'Weekly Pay After Deductions Tax Rate $0 8% $750 10% $1000 15% $1500 20% $2000 25% $2500 30% Note: For a given row, the tax rate in the second column applies to the weekly pay greater than or equal to (but less than the weekly pay in the row right below) the value in the first column. For example, if the weekly pay after deduction is equal to $1,200; the tax would be 8% of the first $750 plus 10% of $250 (1,000 600) plus 15% of $200 (1,200 1,000). Table 1.3 Commission Rates Job Description Commission Rate Waiter/Waitress 2% Chef 5% Driver 3% Manager 8% Note: Use the information in Table 1.3 to find the commission rate for all employees. You must use NESTED IF function to calculate the commission rate. BI73189 Management Information Systems Exam 1 Page 3 of 10 Formulas to use: The formulas for computing payroll information are given in the following table: Table 1.4 Formulas for Computing Payroll Regular Pay (Hours worked * Hourly wage) + (Commission * Avg. Weekly Sales) Pay After Regular Pay (Retirement Contribution * Regular Pay) Deductions Basic Tax Pay after deductions * Basic Tax Rate Income Tax Income Tax Rate (see calculation above) * Pay after deductions Net Pay (Pay after deductions) (Basic Tax + Income Tax) Note: You must use VLOOKUP function and the Income Tax Rates Table to get the proper tax rate when calculating Income Tax. Jack and Jill Bakery requires the payroll report to be in one Excel worksheet. The Excel workbook you downloaded from LearnUs contains a worksheet named, \"EmployeePayrollReport\" which has payroll report information. Formatting the Report and Entering Input Values The worksheet must contain a title and date to reflect the purpose of the spreadsheet application, as well as column headings and other text labels clarifying the meaning of the numbers in the worksheet. Value-added features such as text formatting and alignment and color shading should be used to increase the readability of this information. Instructions: 1. Open the attached Excel file and modify it as per the instructions below. 2. Merge and Center each title row (1.e., the first three rows) so that each title is placed at the center (you need to merge columns A through K). 3. Use the Now() function to show the current date and time in cell \"B5\" . 4. Apply the percentage format to the commission and retirement contribution values as well as tax and commission rates. 5. Write appropriate formula in the 9" row of columns F, G, H, I, J and K. Then copy these formulas for all the employees (i.e. to row 23). Find the correct commission rate using the information given in Table 1.3, and calculate regular pay, pay after deductions, basic tax, income tax and net pay using the formulas given in Table 1.4. 6. Write formulas for calculating Total, Average, Maximum and Minimum (see rows 24 through 27). 7. Format the results appropriately (two decimal places, dollar sign, etc...). 8. Shade and border the title and other areas (such as assumptions, income tax rates, etc...). BI73189 Management Information Systems Exam 1 Page4of 10 Task 1.2: Use the payroll report and the tools provided in Excel to answer the following questions. Write your answers in the \"B\" column of the worksheet named,\"Part2Answers\". For Questions 6 and 7, take a screenshot once vou see the answer on your screen. Paste these screenshots on the \"ScreenshotsP2\" worksheet. When you take screenshots, select only the area (not the entire screen!) in the worksheet that contains information related to the question. Note: Please see this link if you need help for taking screenshots: http://www.take-a- screenshot.org. You may also use free software for this purpose. Note: Before answering each question, you must reset the worksheet to its original values (i.e. the values in the file that you downloaded from LearnUs). 1. What were the total hours worked and the average net pay for all the emplovees? 2. IfEd Hillary doesn't put any money away in retirement what would be his net pay? 3. If Susan Hu was given a 4% commission, what would be her net pay? 4. Ifsales total for the week was $30,000 what would be Jack Grey's net pay increase? 5. Ifthe hourly wage for Kyle Jones and Steve Jones was increased by $3 and their commission rate is increased to 6%, what impact would this have on their total net pay? 6. Using the Goal Seek tool, what hourly wage does Paul Martin need if he wants to earn a net pay of $2000? Copy the screenshot (showing the Goal Seek result message) for this answer in \"ScreenshotsP1\" worksheet. 7. Using the Goal Seek tool, how many hours does Laura Wilson need to work if she wants to earn $1500 regular pay? Copy the screenshot (showing the Goal Seek result message) for this answer in \"ScreenshotsP1\" worksheet. BI73189 Management Information Systems Exam 1 Page 5of 10 PART III: Developing a DSS for a Remodeling Project Background Jack and Jill Bakery has been doing well in the past year, especially in the restaurant part of business, and decided to remodel their business facility. They are currently operating in a 900 square-foot building, and they decided to add another 400 to 1100 square feet depending on cost of materials for remodel. They would like to change the flooring, install new lighting, tables, chairs, and buy two ovens (one new oven and a wood fire oven). They have several loan amounts and several different bids for the remodel project. They want you to create a spreadsheet application to evaluate different scenarios that are possible. Task 2.1: Prepare the following three worksheets. 1. \"Remodel Existing Space\" Worksheet Jack and Jill Bakery needs a worksheet to view the various costs of the seven remodeling components (Restructure add-on, Flooring, Lighting, Tables, Chairs, New Oven, and Wood Fire Oven) and a total loan amount projected to fund their remodeling project. On the same worksheet, they also want to see the percentage rate for each of the seven cost amounts, and also the price per square foot for the new addition and the flooring. The following table shows the initial estimated square footage for the add-on and the estimates for each of the seven components of the remodel. Table 2.1. Initial Estimates of Square Footage and Costs Square Feet 600 Dollar Amounts for Remodel Re-structure Add-On $ 140,400 Flooring 3 9,600 Lighting $ 8,300 Tables S 7,200 Chairs $ 4,200 New Oven $ 7,500 Wood Fire Oven $ 10,800 Please follow the instructions given below to complete \"RemodelExistingSpace\" worksheet: Instructions: Use the worksheet in the workbook provided to you. Enter appropriate formula to calculate the Total Loan Amount. Enter the appropriate two formulas to calculate the Price Per Square Foot. Enter the appropriate formulas to calculate the Percentage Costs of each of the remodel expenses. B = BI73189 Management Information Systems Exam 1 Page 6 of 10 5. Create a pie chart depicting the percentage of cost per feature; include a title, legend, and percentage labels, in \"RemodelExistingSpace\" worksheet (right under the remodel cost table i.e. below row number 27). Your pie chart should look like the following. Percentage Amount for Remodel 6% B Re-Structure ADD-ON B Flooring Lighting = Tables = Chairs B New Oven Wood Fire Oven 2. \"Loan Information for Remodel\" Worksheet An important managerial use of spreadsheet applications is what-if analysis, a process of changing the values of one or more input cells to see how those changes affect the outcome of one or more formulas. Jack and Jill Bakery requires another worksheet in the same spreadsheet application that can answer the question \"What are the monthly payments for different interest rate and years of payment?\" A blank worksheet for this purpose, named as \"LoanInformationforRemodel\" has already been created in the workbook that you are provided. A B C D E F G H 1 J K 1 Calculate a Monthly Loan Amount 2 3 | Loan Amount 4 | Interest Rate 7.50% 5 | Years of Payment 30 6 Monthly Payment Years of Payment 7 [ 10 [ 15 | 2 | 2 | 30 [ 3 | a | s | so | Yearly Interest Rate BI73189 Management Information Systems Exam 1 Page 7 of 10 Instructions: 1. Enter the data as shown above in the \"LoanInformationforRemodel\" worksheet (which is already created in the workbook). 2. Include the date using NOW( ) function in A2. 3. Inthe cell B4 and BS, enter the interest rates and number of years as shown above. In B3, you should use referencing for the loan principal amount to be picked directly from cell B12 in the \"RemodelExistingSpace\" worksheet. 4. In B6, use PMT( ) function to calculate the monthly payments. You should reference appropriate cells so that the monthly payment automatically changes as loan amount, interest rates, and/or vears of payment change. 5. Use \"Data Table\" feature of Excel to conduct the what-if analysis (i.e. to compute the monthly payments from C8 to K18 -- of projected interest rates and years of payments). 3. Decision Support System (\"DSS\"') Worksheet Finally, Jack and Jill Bakery needs an easy-to-use feature to let them ask whaz-if questions. For example, they would like to evaluate different combinations, or scenarios, of costs for lighting, flooring, and chairs, to immediately see the change in the loan amount. A variation of what-if analysis is to use Excel's Solver tool to search for the best, or optimum, value of a formula by changing the value of one or more input cells, but only within certain limits, or constraints. Solver will show the answer and supporting information on a new worksheet within the spreadsheet application. Jack and Jill Bakery is considering increasing the quality of its remodeling efforts. Assuming that higher price reflects higher quality, the management has asked you to find out the maximum prices (per square foot) that the firm can afford to pay for \"flooring\" and \"re-structure add-on\Decision Support System for Remodel March 23, 2023 2 } I _Price perSq.ft. for ADD-ON ; I 3 T 3 ) | ADD-ON constraint ] 0 |Flooring constraint ] 1 |Total cost constraint _ 2 3 4 5 Re-Structure ADD-ON s -] 6 Flooring s 02020 = | Total Cost (includes all other costs which did not change) 5 38,000.00 o~ Instructions: I. Format the worksheet to make it look like the above. 2. In Independent Variables area, enter a value of zero in the two independent variables cells (price for per sq. ft. for Add-On, price per sq. ft. for flooring), where Solver will return answers after solving. 3. In Dependent Variable area, enter a formula for calculating the final budget for the two items based on the values of the two independent variables. 4. In Constraints area, the maximum allowed budget for the two cost items and total cost. 5. In New Cost Estimates area, enter appropriate formulas to calculate the two costs and the total cost using the numbers from \"RemodelExistingSpace\" worksheet and the numbers resulting from Solver. 6. Use Solver to find solution to the given problem. Enter target cell, changing cells, and constraints and then hit Solve. 7. Inthe Solver Results dialog box: a. Select \"Keep Solver Solution\". b. Under Reports, select \"Answer\". c. Click on OK button. Then one new worksheet will be created. Rename this worksheet as \"DSSReport\" BI73189 Management Information Systems Exam 1 Page 9of 10 PART IV: Analyze Data Background Jack and Jill Bakery keeps track of the sales of their 4 new sales people. Your manager has given you the data (refer to the worksheet named \"SalesData) and asked you to provide answer to the following questions. Insert pivot table in a separate worksheet and name the worksheet \"PivotTable: 1. Fill in the 'Item Name' column using the vlookup function. 2. Who is the best sales person in terms of total gross profit? How much is the profit? 3. Who is the best sales person for Premium Bread in terms of total gross profits? How much is the profit? 4. Which product 1s the strength of sales person named Monica (in terms of generating highest gross profit)? How much is the profit? 5. Whose daily average units sold of cookies is the highest and whose standard deviation of the daily units sold is the highest for cookies? Mention the average and the standard deviation. 6. Between the time period of 5/1/2023 to 5/31/2023, which product provided highest sales revenue (1.e., sales price * units sold) and how much? [Hint: You might want to add a revenue column to the original dataset for this and the next question] 7. Between the time period of 12/1/2023 to 12/31/2023, which salesperson had the highest sales revenue (1.e., sales price * units sold) and how much? 8. In addition, you have been asked to provide any additional (interesting) insights (findings) that you obtained by conducting pivot table analysis. Write your answers in the \"B\" column of the worksheet named, \"Partd Answers\". Take a screenshot for each new pivot table arrangement that you use to get the results of this section. You will paste these screenshots on the \"ScreenshotsP4\" worksheet; indicate clearly which screenshot belongs to which question you've answered. Deliverables: Download the workbook provided on LearnUs and follow the instructions given above to complete it. You should submit your answer to LearnUs. File naming convention: Name your Excel Workbook as follows: \".xlsx\" BI73189 Management Information Systems Exam 1 Page 10 of 10