Use a custom sort to sort the table by Weekday, then by Meal, and then by Location Code.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

CSCI 48 Assignment 2

Fall 2021

Please use the Excel file on Blackboard to complete the following. Each exercise has its own sheet in the excel file. If there are any questions, please feel free to email me.

EX1

• Use a custom sort to sort the table by Weekday, then by Meal, and then by Location Code.

• Create Subtotal rows that give you the average Revenue for each weekday.

• Create a Subtotal Row that gives you the average Revenue  for each meal

 EX2

 • Use the table in EX2 to create a pivot table. Move it to a new sheet and rename the sheet EX2PTABLE

• Apartments Complex and # Bed fields in rows and the Rental Price field in Values.

• Format the Sum of Rent for Accounting Number with 0 decimal places.

• Add a filer for Occupied Field.

• Rename the Pivot Table Rental Revenue.

• Insert a Slicer for # Bed.

 

EX3

 

 

• Create Range names based on the values in B15:B16

• Apply Total Profit and Total_Building_Expense range names to cells C15:C16 the worksheet.

• Load the solver add in

• Set the objective cell to Max Net Profit (cell C17)

• Total Contracts as Changing Variable Cells

• Add constraints to ensure contracts are whole numbers

• Add constraints to ensure raw materials never exceed the inventory

• Set a constraint to ensure the Total Contracts are greater or equal to the existing contracts in C7:E7

• Create an answer report to outline your findings. Rename the Report Sheet EX3ReportSheet

 EX 4

• Use the Weekday function starting in C11 to extract the weekday by serial number starting in B11.

• Use the Switch function starting in D11 to get the the Weekdays.

• Use the IF function to calculate the down payment starting in H11. If type=Finance, then the down payment is 10% of the amount. Else, it is equal to the amount.

 

• Use a nested function to calculate Last PMT Date. If there is a Balance, then the date is 48 months after the date in column B (Use EDATE for this function). Else, there is no date.

• Use countif to calculate the number of paid in full transactions in J2.

• Use sumif to calculate the total dollar amount of the paid in full transactions in J3.

• Use averageif to calculate the average dollar amount of the paid in full transactions in J4.

• Use averageifs to calculate the average dollar amount of the paid in full transactions over $4000 in J5.

• Use maxifs to calculate the Highest Paid-in-Full Transaction Amount.

 

 

EX5

 

 

• Create a Scatter Chart using the table that begins in B2.

• Move the new chart to begin at cell E9

• Add a Trendline, Linear Equation, and R-squared value.

• Calculate the Variance, Intercept, Slope, R-square, and Standard Error in E4:I4

• Calculate the Forecast (FORCAST.LINEAR) for E7 in F7.

• Do a check for the forecast in G7 using E7, G4, and F4.

• Use I4 to calculate the high and low for the forecast in H7 and I7.

• Format F7:I7 with accounting number format.

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of