ASSIGNMENT QUESTIONS

Question 1:
You are a financial analyst for a small business. Your task is to create a basic Excel
worksheet to calculate the monthly profit for the business based on its revenue and expenses.
Follow the steps below to complete the worksheet:
1. Set Up the Worksheet:
o Open a new Excel worksheet and label it "Monthly Profit Analysis."
o In column A, enter the following text labels:
 Cell A1: "Month"
 Cell A2: "January"
 Cell A3: "February"
 Cell A4: "March"
 Cell A5: "April"
 Cell A6: "Total"
 Cell B1: "Revenue"
 Cell C1: "Expenses"
 Cell D1: "Profit"
2. Enter Data:
o In column B, enter the following revenue values for each month:
 January: 5000
 February: 4500
 March: 6000
 April: 5500
o In column C, enter the following expense values for each month:
 January: 3000
 February: 2800
 March: 3200
 April: 2900
3. Calculate Monthly Profit:
o In column D, enter a formula in cell D2 to calculate the profit for January as
the difference between revenue and expenses.
o Use the fill handle to copy this formula down to the other months (February to
April).
4. Calculate Total Revenue, Expenses, and Profit:
o In cell B6, enter a formula to calculate the total revenue for all months.
o In cell C6, enter a formula to calculate the total expenses for all months.
o In cell D6, enter a formula to calculate the total profit.
5. Formatting:
o Format the revenue, expenses, and profit cells (B2
) as currency to two decimal places.
o Bold the headers (A1
) and the total row (A6
) to make them stand out.
6. Answer the Following:
o What is the total profit over the four months?
o In which month did the business have the highest profit?
Submission: Save your worksheet as "Monthly_Profit_Analysis.xlsx" and submit it with
your answers to the questions above.

Page 1 of 5
Question 2:
You are an intern working for the Human Resources department of a company, and your
supervisor has asked you to format an employee salary report to make it presentable and
easier to read. Follow the steps below to change the appearance of the worksheet data using
various formatting techniques.
1. Set Up the Worksheet:
o Open a new Excel worksheet and label it "Employee Salary Report."
o In column A, enter the following data labels:
 Cell A1: "Employee ID"
 Cell B1: "Name"
 Cell C1: "Department"
 Cell D1: "Monthly Salary"
 Cell E1: "Bonus (%)"
 Cell F1: "Total Compensation"
o In rows 2 to 6, enter sample data for five employees with unique Employee
IDs, names, departments (e.g., "Sales," "Marketing," "IT"), and monthly salary
values between $2,500 and $5,000.
o In column E, enter bonus percentages ranging from 5% to 15%.
2. Calculate Total Compensation:
o In column F, calculate each employee’s total compensation, factoring in the
bonus percentage.
o Use a formula in cell F2 to calculate the total compensation as: Monthly
Salary + (Monthly Salary * Bonus %).
o Use the fill handle to apply this formula for all employees.
3. Apply Formatting Techniques:
o Header Formatting:
 Make the headers in row 1 bold and change the font size to 14.
 Apply a background fill color (light gray or any color of your choice)
to the header row.
 Center-align the text in the header row.
o Data Formatting:
 Format the "Monthly Salary" and "Total Compensation" columns (D2
and F2) as currency with two decimal places.
 Format the "Bonus (%)" column (E2) as a percentage with one decimal
place.
o Conditional Formatting:
 Apply conditional formatting to the "Total Compensation" column to
highlight cells with values above $5,000 in green.
 Apply another rule to highlight cells below $3,000 in red.
o Borders:
 Add borders around the entire data range (A1) for better readability.
 Use thicker borders for the header row to make it stand out.
o Adjusting Column Width:
 Adjust the column widths to fit the data comfortably, ensuring that all
text is visible.
4. Answer the Following:
o Which employee has the highest total compensation?
o How many employees received a bonus of more than 10%?
Submission: Save your formatted worksheet as "Employee_Salary_Report.xlsx" and submit
it with answers to the questions above.

Page 2 of 5
Question 3:
You are a student assistant in the Academic Records Department, and you have been tasked
with creating an Excel worksheet to analyze the exam scores of students in a particular
course. You will use built-in functions to calculate statistics that will help the instructor
understand the class's performance. Follow the instructions below to complete the worksheet:
1. Set Up the Worksheet:
o Open a new Excel worksheet and label it "Exam Scores Analysis."
o In column A, enter the following labels:
 Cell A1: "Student ID"
 Cell B1: "Name"
 Cell C1: "Score"
o In column C (Score), enter the exam scores for 10 students, with values
ranging between 50 and 100.
2. Calculate Summary Statistics:
o Use the following built-in functions in the specified cells to calculate and
display summary statistics for the scores:
 Average Score: In cell C13, use the AVERAGE function to calculate the
class's average score.
 Highest Score: In cell C14, use the MAX function to find the highest
score in the class.
 Lowest Score: In cell C15, use the MIN function to find the lowest
score in the class.
 Number of Students Passed: In cell C16, use the COUNTIF function to
count the number of students who scored 60 or above (considered a
passing score).
 Standard Deviation: In cell C17, use the STDEV.P function to
calculate the standard deviation of the scores, providing insight into
score variability.
3. Use a Conditional Function:
o In column D (Status), use the IF function to create a formula in cell D2 that
displays "Pass" if the student’s score is 60 or above and "Fail" if it is below
60.
o Use the fill handle to apply this formula to the rest of the students in column
D.
4. Formatting:
o Format the "Score" column (C2
) to show numbers with no decimal places.
o Format the summary statistics results (C13
) to show numbers with two decimal places.
o Bold the headers (A1
) and apply a background color to make them stand out.
5. Answer the Following:
o What is the average score of the class?
o How many students passed the exam?
o What is the highest score, and who achieved it?
Submission: Save your worksheet as "Exam_Scores_Analysis.xlsx" and submit it along with
your answers to the questions above.

Page 3 of 5
Question 4:
You are working in the Student Affairs Department, and you have been provided with a
dataset of students enrolled in various extracurricular activities. Your task is to organize and
analyze this data using filtering and sorting techniques in Excel to make it easier to identify
student participation patterns and prioritize outreach. Follow the instructions below to
complete the worksheet.
1. Set Up the Worksheet:
o Open a new Excel worksheet and label it "Student Activities."
o In columns A to D, enter the following headers:
 Cell A1: "Student ID"
 Cell B1: "Name"
 Cell C1: "Activity"
 Cell D1: "Participation Hours"
o Populate the worksheet with data for 15 students, including a variety of
activities (e.g., "Basketball," "Debate Club," "Music Band," "Volunteering")
and participation hours ranging from 5 to 30.
2. Apply Sorting:
o Sort the data by Activity in alphabetical order to organize students by the
activity they participate in.
o Then, within each activity group, sort by Participation Hours in descending
order to see which students have contributed the most hours to each activity.
3. Apply Filters:
o Add a filter to the header row (A1
) to allow dynamic filtering of the data.
o Use the filter to display only students with more than 15 participation hours.
o Further refine the filter to show only those students who participate in
"Volunteering" or "Debate Club."
4. Answer the Following:
o How many students have more than 15 hours of participation in
"Volunteering" or "Debate Club"?
o Who is the student with the highest number of participation hours in
"Basketball"?
5. Bonus - Custom Sort:
o Reset the filters, and create a custom sort that orders the data first by
Participation Hours in descending order and then by Activity in alphabetical
order.
o Describe what insights you can gather from this ordering in your submission.
Submission: Save your worksheet as "Student_Activities.xlsx" and submit it along with your
answers to the questions above.

Page 4 of 5
Question 5:
You are a data analyst for the University Admissions Office. You have been asked to create a
visual report showing the admission trends over the past year to identify which programs are
most popular and how student demographics influence program choices. Follow the
instructions below to create and modify charts that effectively display the data.
1. Set Up the Worksheet:
o Open a new Excel worksheet and label it "Admissions Data."
o In columns A to D, enter the following headers:
 Cell A1: "Program"
 Cell B1: "Male Applicants"
 Cell C1: "Female Applicants"
 Cell D1: "Total Applicants"
o Enter data for six programs (e.g., "Engineering," "Business," "Law," "Arts,"
"Sciences," "Education") with values ranging from 100 to 500 for male and
female applicants. In column D, calculate the Total Applicants for each
program using a formula.
2. Create a Basic Chart:
o Select the data range (A1
) and create a Clustered Column Chart that shows the number of male and
female applicants for each program.
o Format the chart title as "Admission Trends by Program and Gender."
o Label the X-axis as "Programs" and the Y-axis as "Number of Applicants."
3. Modify the Chart:
o Change the color scheme of the columns to distinguish between male and
female applicants clearly.
o Add Data Labels to show the exact number of male and female applicants
above each bar.
o Adjust the chart’s legend to display "Male Applicants" and "Female
Applicants" in a way that makes it easy to interpret.
4. Add a Pie Chart:
o Create a Pie Chart to represent the total applicants for each program (column
D).
o Format the chart title as "Percentage of Total Applicants by Program."
o Display Data Labels on the pie chart to show each program’s percentage
share of total applicants.
5. Use a Line Chart to Show Trends:
o Copy the admissions data to a new sheet labeled "Monthly Trends" and enter
data for each month from January to December for a single program of your
choice (e.g., "Engineering").
o Plot the monthly data as a Line Chart to show the trend in the number of
applicants over the year.
o Label the chart "Monthly Admissions Trend for Engineering."
6. Answer the Following:
o Which program has the highest total number of applicants?
o What is the gender distribution in the program with the most applicants?
o What trend does the line chart show for the chosen program over the year?
Submission: Save your workbook as "University_Admissions_Report.xlsx" with all three
charts and answers to the questions above.

Page 5 of 5