# Exam 1 su 22b | Computer Science homework help

Get your original paper written from scratch starting at just \$10 per page with a plagiarism report and free revisions included!

4.8

SiteJabber

4.9

ResellerRatings

4.9

Reviews.io

Hire A Writer

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.