Gudrun Duplessis is a manager in the Alanis Parks Department. Gudrun is compiling spending data in preparation for a city bond offering for park funding. She wants to use Excel to create charts to illustrate some of her data and to apply a function to calculate the potential monthly and yearly costs to the city of different bond scenarios. Switch to the Spending worksheet. In the range E5:E10, add Conditional Formatting to show Solid Blue Data Bars. In the range F5:F10, add Line sparklines based on the data in the range B5:E10.Apply the Blue, Accent 1, Darker 25% (5th column, 5th row in the Theme Colors palette) sparkline color to the range F5:F10.Gudrun has created a pie chart representing the percentage of the parks' budget that went to each park in 2018. Modify the chart in the range G2:O20 as follows:Enter 2018 Park Spending as Percentage of Total as the chart title.Change the data labels to include the Category as well as the Percentage, and position the labels in the Outside End location.Remove the Legend from the chart.Gudrun would like a pie chart representing the percentage of the parks' budget that would go to each park in 2021.Create a 2-D Pie chart based on the data in the range E5:E9 and using the category labels in the range A5:A9.Resize and reposition the chart so that the upper-left corner is located within cell G21 and the lower-right corner is located within cell O39, and then left-align the chart with the pie chart above it in the range G2:O20.Customize the chart in the range G21:O39 as follows:Enter 2021 Park Spending as Percentage of Total as the chart title.Apply Style 3 to the chart.Gudrun wants a stacked bar chart showing spending per park for each year.Create a Stacked Bar chart based on the data in the range A4:E9.Resize and reposition the chart so that the upper-left corner is located within cell A11 and the lower-right corner is located within cell F39.Customize the chart in the range A11:F39 as follows:Enter Spending per Park 2018-2021 as the chart title.Change the Maximum value to $2,300,000 on the horizontal axis.Apply a shape fill of White, Background 1, Darker 5% (1st column, 2nd row in the Theme Colors palette) to the chart area.Update the Park Spending 2018-2021 combo chart in the range A40:G56 as follows:Move the chart to the Projection worksheet.Resize and reposition the chart so that the upper-left corner is located within cell A4 and the lower-right corner is located within cell K22.For the Park Spending 2018-2021 combo chart, update the chart area as follows:Apply the Monochromatic Palette 4 color scheme to the chart.Add a secondary axis for the Total series. [Mac Hint: Select the Total series and use the format pane to add the axis.]Add Axis Titles to the chart, and then enter Spending per park as the left vertical axis title, and then enter Total spending as the right vertical axis title. Finally, delete the horizontal axis title placeholder.Gudrun wants to compare future payments for three different scenarios for a park's bond offering. Switch to the Bond Offering worksheet. She has already entered formulas in the range B9:D10 to calculate the quarterly and annual payments for each option. In the range E9:E10, add Column sparklines using the data in the range B9:D10. Ungroup the sparklines, and then set the vertical axis maximum value to -15000 for cell E9 and to -60000 for cell E10.Gudrun wants a clustered column chart comparing the costs of the bond options.Create a Clustered Column chart based on the values in the range A9:D10.Resize and reposition the chart so that the upper-left corner is located within cell A11 and the lower-right corner is located within cell D28.Customize the chart in the range A11:D28 as follows:Enter Bond Offering Cost Comparison as the chart title.Add a Data Table With Legend Keys to the chart.Apply a 2 point border to the chart area, and then change the border color to Gold, Accent 4 (8th column, 1st row in the Theme Colors palette).

This set of links will help you with the next portion of your SAS exercise. 1. First, you need to register with SAS, if you have not already done so. To do that, follow this link: https://odamid.oda.sas.com/SASODAControlCenter/enroll.html?enroll=edd990e3-f7a9-4d72-a448-34d0459dcd22 (Links to an external site.). This is the sign in page for SAS, so you may want to bookmark it so you can easily return later. If you have previously registered, then you may log in from here. Otherwise, click on "Register for an account" and register. Once you have registered, SAS will send you a user ID, which will probably be your email address (before the @) and a number (usually zero). 2. If you save the above bookmark and go there to sign in each time, SAS may ask you for a course code. It is not necessary to enter one - simply click on "Return to the Dashboard". If you would like to avoid that each time, once you have registered, simply click on this link: https://odamid.oda.sas.com/SASLogon/login?service=https%3A%2F%2Fodamid.oda.sas.com%2FSASODAControlCenter%2Fj_spring_cas_security_check 3. At this point, you will simply click on "SAS Studio" and you are then ready for the exercise. 4. Below are links to two videos developed by Dr. Jeff Bohler for IS3310. The first one includes some exercises you do not need to do for this class, but you may find it helpful in learning to navigate SAS. It explains how to enroll in SAS and it also shows you how to build a scatterplot from an existing database. Here is that link: https://web.microsoftstream.com/video/24bc9551-a073-4bb9-bf1b-8f90612bc932 (Links to an external site.) 5. The next video is where Dr. Bohler is explaining how to upload a database. YOU NEED THIS! This is where you will learn how to upload the Excel spreadsheet with the Real Estate Data in order to complete the Critical Thinking Exercise for QM3345. Link to Dr. Bohler's video on uploading a database: https://web.microsoftstream.com/video/d4551f6c-cbc4-402d-bd77-3edcd410cea5 (Links to an external site.) Complete SAS Registration The Maddox & Johnson examples are part of Chapter 4 and here is an explanation of how to use that spreadsheet. Please feel free to use the spreadsheet to practice your Regression using Excel. This is for practice only and does not count as part of your course grade. The first worksheet shows you an example of an uncorrelated Independent Variable, which is plausible. Do a scatterplot first and you will see that, and then confirm it with a Regression Analysis. The next two show a progressively better correlation with a higher R2. Then, with the final tab, run a Regression and you will find the highest R2 of the Regressions. However, is it really significantly better enough to justify using all three Independent Variables, or would you be almost as well served using only Years of Experience? No grades or points associated with this one, but worth your time to practice. SAS This is the week we work on your special Critical Thinking (SAS) project. First, you should download and familiarize yourself with the nature of the assignment. Next, you should watch the videos by Dr. Jeff Bohler explaining how to register for SAS and showing you how to upload the "Real Estate" database I have provided to you in this week's module into SAS Studio. The assignment is to perform some functions in Excel where you will produce a series of Scatterplots and also perform a Regression Analysis on the Real Estate database using a specific set of variables I provide. In Excel, you will need to add the "Add In" for "Data Analysis". If you have not previously done this, here are the steps: 1. Go to the "File" tab. 2. Select "Options" (near the bottom) 3. Select "Add-ins" (also near the bottom) 4. At the bottom (by "Manage") click "Go" 5. Check "Analysis ToolPak" and "Analysis ToolPak - VBA" 6. Click "OK" This will add "Data Analysis" to your "Data" tab in Excel. Then you can click on "Data Analysis" and perform the necessary Regression Analysis. Then, again following Dr. Bohler's instructions, you will go into SAS and perform a Regression Analysis on the Real Estate Database you previously uploaded into SAS. One critical thing to remember - you must correctly categorize your data types. Nominal data goes into the "Classification" variables when building your model, and all numeric data types going into "Continuous". You will need to print or edit the Word document provided and then save/scan it to submit in Week 4, along with the Excel spreadsheet and the SAS Output. Based on common questions from students, there is always a little confusion on uploading the Excel file (Real Estate - Base.xlsx) into SAS. So, please let me clarify. You should upload the pure, base file into SAS - NOT the one that you manipulate to make the Scatterplots and requested Excel Regression model(s). The point of this exercise is for you to see the difference between how you create Regression models in Excel and SAS, so we definitely want to start with the same database. In order to do that, you need to load the base file in SAS in order to set up your data for the Regression. I hope that is clear! Please let me know if you still have any questions! Primary work on SAS Project - Due next week 1. As described above, you must upload the Real Estate Database into SAS. 2. You must perform the described analysis in Excel and submit the Spreadsheet. 3. You must perform the described analysis in SAS and submit a PDF. 4. You must complete the Word document (answers from 2 and 3 above) and submit. For the SAS (Critical Thinking) project, I always want to add clarity regarding the output from Excel Regression (see below). The Output gives you what you need to complete the Critical Thinking Project form for the Excel part. You will see below that I highlighted "R" (called Multiple R here and Correlation Coefficient in my slides) in Yellow. R2 is highlighted in Blue (that is the Coefficient of Determination). Remember, if you multiply it by 100, you get the percentage of the change in the Dependent Variable that is explained by the Independent Variable(s) - in this case 92.6%. Finally, you need to know how to build your Regression model from the output below. The formula coefficients are highlighted below in Green. This is the output from the Nodel Construction example you find in the PowerPoint slides for Chapter 4. If Sales is "Y," Payroll is X1, and Interest Rate is X2, then your Regression model would read as follows: Y = 5.214 - 0.017(X1) - 30.155(X2) SUMMARY OUTPUT Regression Statistics Multiple R 0.96207063 R Square 0.925579897 Adjusted R Square 0.875966495 Standard Error 0.222740661 Observations 6 ANOVA df Regression 2 Residual 3 Total 5 Coefficients Intercept 5.213917526 Payroll -0.016752577 Interest Rate -30.15463918 If you have properly completed your SAS project you will upload the following three items: 1. The DOCX file with the original assignment and rubric (all fields completed). 2. The XLSX file you downloaded with the addition of the tab with the Scatterplots on it and the Regression Output tab for the regression of Price with the three independent variables. 3. A PDF file you produce from SAS that shows the output of your final regression (with a higher R2 than we had with the original model).