Scribd
Upload
50 views4 pages

QUIZ

The document contains 4 problems involving spreadsheet calculations and analysis. Problem 1 involves calculating loan amounts, monthly payments, and gross profits based on computer sales and rental data. It asks to create a two-input data table and use goal seek to find the number of computers for a target gross profit. Problem 2 involves calculating student grades based on assignments, exams, attendance and using formulas and conditional formatting. Problem 3 involves filling in employee salary data by looking up department and bonus information. Problem 4 asks to model a new restaurant business plan across the year and calculate its expected impact on annual profit.

Uploaded by

kanekitoka10
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
50 views4 pages

QUIZ

The document contains 4 problems involving spreadsheet calculations and analysis. Problem 1 involves calculating loan amounts, monthly payments, and gross profits based on computer sales and rental data. It asks to create a two-input data table and use goal seek to find the number of computers for a target gross profit. Problem 2 involves calculating student grades based on assignments, exams, attendance and using formulas and conditional formatting. Problem 3 involves filling in employee salary data by looking up department and bonus information. Problem 4 asks to model a new restaurant business plan across the year and calculate its expected impact on annual profit.

Uploaded by

kanekitoka10
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

QUIZ

Problem 1.

- Fill the Computer Price in cell B8 by using data in range D3:E7


- Fill the House Rent Payment in cell B12 by using data in range D9:E13
- Loan is calculate by the following formula
Loan = number of computers * computer price

- Monthly payment is calculated by the following formula


Monthly Payment = Loan/(Months) + Loan*Interest/12

- Gross profit/month is calculated by the formula


Gross profit/month=total profit of all computers - House Rent Payment –Monthly Payment-Others

- Create a two-input data table


Using what-if analysis to fill the above table which shows the Gross Profit/ Month values
according to the Number of Computers and Interest change.
- Create a copy of sheet2 and rename as sheet2_1, then using goal seek to find the number of
computers to have Gross Profit/ Month is $6000

Problem 2.

- Open the sheet Problem2


- Calculate the In-class Avg in that the items HW1-HW3 got weight 1 (x1) and QU1-QU3 got
weight 2 (x2). Display the result with 1 decimal place, i.e.: 55.5
- Fill in the Bonus/Deduction for each student based on their class attendance with the following
rules:
• Attend 8 lectures: +4 points
• Attend 7 lectures: +2 points
• Attend 5-6 lectures: 0 point
• Attend less than 5 lectures: -2 points
- Calculate the Course Grade (column O) using the given formula, display the result with 1
decimal place also:
Course Grade = In-class Avg*0.3 + Midterm Exam*0.2 + Final Exam*0.5 + Bonus

- Use Conditional Formatting to highlight (Light Purple Fill with Red Accent-2 Text) all the
students who got the Course Grade greater than or equal to 80.0
- Determine the number of student who got grade greater than or equal to 80.0
- Find the student who got the highest course grade
- Use lookup functions (IF or VLOOKUP) to fill the Pass /Fail based on the following rules:
• If Course Grade >= 50 fill with the text “Pass”
• If Course Grade <50 fill with the text “Fail”
- Determine the percentage of students who failed the course.
- Fill the Letter Grade column by using VLOOKUP and applying the following criteria

Grade Point Letter Grade


0 F
30 D
50 C
60 C+
70 B
80 B+
90 A
100 A+

Problem 3.

- Sheet Problem3
- Fill the following data
- Depending on the first letter in Code (Salary Table) and Code Table, fill the name of
Department for each employee in Salary Table.
- Based on Bonus Table, find Bonus in Salary Table.
- Calculate Total Salary, Total Salary = Bonus + Basic Salary * Coefficient, where Coefficient
is the last letter in Code (Salary Table)
- Calculate the Total Salary by Department for each department?
Problem 4.

- Open the file Problem4.xlsx (sheet Expected Income)


- The manager of the restaurant wants to apply a new business plan in order to increase the annual
profit of the restaurant as follows :
- In the first four months (Jan-Apr): Eat-In Orders = 10*To-Go Orders
1
- In the next three months (May-Jul): To-Go Orders = 7*Eat-In Orders
- In the next three months (Aug-Oct): Eat-In Orders = 20*To-Go Orders
1
- In the last two months (Nov-Dec): To-Go Orders = 14*Eat-In Orders
- Create a scenario to see how this business operation would affect the annual profit (create a
summary )

You might also like