Exam 1 Su 22b
Exam 1 Su 22b
Project Description:
Apply all of the skills that you have learned in CS 235 so far by Communicating Information and Converting Data into Information using the data from Happy Place Incorporated and Excel.
Start Excel. Download and open the file named Exam1.xlsx. Remember to SAVE OFTEN (after every step is suggested). Save where you can find it (DO NOT SAVE in DOWNLOADS). Goodluck!
On the New Location worksheet, merge and center the Loan Information Sheet title in A1 through the range A1:G1. Next, merge and center the date in A2 through the range A2:G2.
To the title in A1, apply Light Orange, 60% – Accent2 cell style, apply bold, and change the font size to 22 (in that order).
Apply Note cell style to the date in A2.
On the New Location worksheet, in the range A4:A6, wrap the text, apply Right alignment, apply Orange, Accent 2, Darker 25% fill color, and apply White, Background 1 font color.
Change the column width of A to 15.
Center align the Potential Monthly Payments (Q1-Q2) subtitle in cell B16 and apply Heading 4 cell style.
On the New Location worksheet, apply the Accounting format with two decimal places to the range C18:H25.
Create a conditional formatting rule that will display cells in the range C18:H25 in the Green – Yellow – Red Color Scale.
Create a conditional formatting rule that will apply Red text to the top 3 values in the range C18:H25.
On the New Location worksheet, in cell F7, enter a payment function that calculates the monthly payment of the new location using the information provided in B4:B6, E7:E9, F6:H6 and appropriate cell referencing. Be sure that the monthly payment is a positive value.
Copy the function through F7:H9.
On the Quarterly Location Overhead worksheet, apply the Number format with a comma and 0 decimal places to the range C5:F11.
In the range C15:C18, enter functions that will appropriately calculate the Total, Maximum, Minimum, and Average for the 1st Qtr.
Copy the functions to D15:F18 to calculate all values for the remaining quarters.
On the Quarterly Location Overhead worksheet, apply a bottom border to C11:F11.
Apply Currency Format with zero decimal places to the range C15:F18.
In cell B21, use a function to count the number of stores in column B beginning in row 5 and ending in row 11.
On the Product Info worksheet, select cell F4. Insert an if function to see if there will be a tax on the swim apparel based on the unit price. If the unit price is greater than or equal to $25, then display the dollar amount of the tax (.065 times the Unit Price). If the unit price is less than $25, then there will not be any tax, display a 0. Use cell reference G2 within your function. Use appropriate cell referencing and copy the function to
F4:F8.
On the Product Info worksheet, in cell C15, enter a date function that returns today's date. Format C15 to Long Date.
In, cell F18, insert a lookup function that looks up the Item Code, compares it to the Product List range in column C, D, and E; and returns the Unit Price using appropriate cell referencing. Copy the function down through the range F19:F22.
On the Product Info worksheet, select G18 and enter a formula that will multiply the quantity (D18) and the Unit Price (F18). Copy that formula down through the range (G19:G22).
On the Product Info worksheet, select Landscape orientation, set a 1.1-inch bottom margin, and center the worksheet data horizontally and vertically on the page.
On the Product Info worksheet, in the range G29:G35, insert Line Sparklines to show the customer trends for each location over the five-year period. Apply Dark Green, Sparkline Style Colorful #4.
Show the High Point and Low Point.
Change the color of the High Point marker to Blue.
On the Product Info worksheet, select the range G29:G35, apply Same for All Sparklines for the vertical axis maximum.
On the Product Info worksheet, insert a 2-D Pie Chart using the ranges A28:F28 and A29:F29. Edit the Chart Title to Customers by Location.
Cut and paste the chart to A39.
Apply Style 5 chart style to the pie chart.
Explode the 2017 data point by 15%.
Change the chart height to 4.5 inches and the chart width to 4.5 inches.
Add Alt Text College Cafe first quarter customer attendance by location (2017-2021). (include period)
On the Product Info worksheet, select the ranges A28:F29 and A33:F33 and create a stacked column chart.
Move chart to a new sheet named Column Chart.
Do not show the Chart Title.
Apply Style5 chart style to the column chart.
Apply the Light Gradient – Accent 3 Fill to the plot area.
Change the Legend font size to 10.
On the Column Chart worksheet, add a Value Vertical Axis Title Customers, then set the Maximum bound to 20000, and the Minimum bound to 0.
Change the value axis display units to Thousands.
Add Data Labels to the Center and show ONLY the Value and Show Leader Lines.
For the West Lafayette Data Series Data Labels, format to Number format with 1 decimal place.
Click the College Cafe Revenue tab, convert the data to a table named Revenue.
Apply Green, Table Style Medium 7.
Change the row 1 height to 18.
Create a custom sort so that the table is sorted by Month: January, February, March.
Add another Sort level to sort the data by Location in alphabetical order and then by Category in alphabetical order, then by Amount in descending order (largest to smallest).
On the College Cafe Revenue worksheet, add a total row to display the average of the Amount column. Apply a filter to display only Amounts that are greater than 2000.
Freeze the first row of the Revenue table.
Set the range A1:E142 (Revenue table) as a print area.
Set row 1 to repeat when printed.
In the College Cafe Revenue sheet, create a PivotTable named Revenue using the data range A1:E142, and place it on the same worksheet, in cell G31.
Add Amount to the Values and Product to the Rows.
Change the Pivot Table style to Dark Grey Pivot Style Dark 4.
Type Product in cell G31.
In the College Cafe Revenue sheet in the Revenue PivotTable, modify the Sum of Amount Field to summarize value by Average instead of Sum. Next change the custom name to Amounts. Change the number format to Accounting with 0 decimal places.
In the College Cafe Revenue sheet, create a calculated field (using the default name) that multiples Amount by 0.57 to find estimated cost.
After the field is created, change its custom name to Estimated Cost.
Change the number format to Currency with two decimal places.
In the College Cafe Revenue sheet, on the PivotTable, Insert a Slicer for Location field.
Change style to Light Orange, Slicer Style Light 2.
Set Slicer height to 3. Set Button width to 1.8.
Cut and paste to J32.
Use the slicer to include only West Lafayette and Bloomington to be included in the PivotTable.
In the College Cafe Revenue sheet, create a clustered column PivotChart named Products from the Revenue PivotTable.
Cut and paste the PivotChart to G43.
Hide all field buttons in the PivotChart.
In the College Cafe Revenue sheet, on Products PivotChart, add a chart title above the chart and type Estimated Margin.
Change the fill color for Estimated Cost data series only to Green, Accent 6.
Move legend to bottom of chart.
Add a Linear Trendline to Estimated Cost.
In the Subtotals worksheet, sort the data by Product in alphabetical order. Use the Subtotal feature to calculate the average Amount, by Product.
Display only the averages and grand average.
Expand the details for Plain Bagel Average.
Switch to the Gala Catering sheet, load the Solver add-in if it is not already loaded. Set the objective cell to calculate the Balance of $11,000 for Fundraiser catering.Use Tickets Sold and Price of Ticket as changing variable cells.
Use the Constraint section A10:B13 to set constraints for minimum ticket price, maximum ticket price, and ticket sales.
Number of tickets sold must be an integer.
Total tickets sold must not be greater than the Venue capacity.
Price of Ticket must not be greater than the Maximum Ticket Price and must not be less than the Minimum Ticket Price.
Solve the problem. Generate the Answer Report and Keep Solver Solution.
On the Gala Catering sheet, create an appropriate range name for Meal (cell B7).
Edit the existing name range Meal to Meal_Cost.
Use the newly created range name to create a formula to calculate Total Meal Cost (cell B8) by multiplying sold tickets and Meal_Cost.
Switch to the Payment worksheet, beginning in cell E3, complete the series of loan amount values in column E ranging from $40,000 to $80,000 in $5000 increments.
Beginning in cell F2, complete the series from 2.5% to 5.5% in .50% increments for row 2.
In cell E2, reference the cell that contains the Monthly Payment.
Apply a custom number format to display Monthly Payment.
Complete the two-variable data table using the Interest Rate and Amount of Loan as the input cells.
On the Payment worksheet using the data in A15:B40, create a scenario named Best Case, using Tickets Sold and Price of Ticket. Enter these values for the scenario: 445 and 195.
Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 100 and 80.
Generate a scenario summary report using Total Income and Balance.
Save your file as Exam 1 Complete_Your Name and close the Excel file. Upload your completed Exam 1 Complete_Your Name file to MyITLab.
Please be sure that you are uploading the CORRECT completed exam file. You only have 1 submission. If you submit the starting file by accident, you will not receive any points for exam 1 (no exceptions). This is why we had you change the name of your completed file at the end, so this won't happen. Again, please please make sure to upload Exam 1 Complete_Your Name.xlsx (your completed exam) for grading. Check your submission to make sure it has been submitted.
If anything goes wrong, notify the course staff ASAP. Once any time has passed, the file that you submit is final and there is nothing we can do. PLEASE DOUBLE CHECK !!
