Sem: October-2025 Maximum Marks: 50 Examination: End Exam Date: Duration: 2.5hrs
Programme code: 18 Programme: MBA for Working Executive	Class: FY	Semester: I- Batch-5- 2024-25
College: K. J. Somaiya Institute of Management	Name of the department/Section/Center: DST
Course Code:	Name of the Course: Spreadsheet Modelling
Instructions: Use SM Data Sheet Excel for solving all questions. Create new worksheet in the same file. Answer any 5 Questions out of 6

Question No.	Max. Marks
Q. 1	Product.xlsx contains the portfolio information of various companies. Add a column which display ‘High Value’ if the amount is greater than 5000 and display ‘Low Value’ if it is less than or equal to 5000. Count the number of orders under each category. Highlight details of order according to the following rules. Amount<=1000 - Red 1000<Amount<=3000 -Pink 3000<Amount<=5000 -Yello Amount>5000 -Green Plot a graph to show the product wise sales. Plot a graph to show the country wise sales. Find out total amound under each Category (Fruit and Vegetable) Find out Total Number of orders under each Category (Fruit and Vegetable)	[10]
Q. 2	The budget for a concert is created using the worksheet. Concert and use the following formulas to complete the spreadsheet and calculate the revenue generated. Cell Formula Total costs =total of B6 to B12 Ticket sales =No. of seats X Price/ticket Merchandising =5 X No. of seats Food & beverage =15 X No. of seats Total revenue = Ticket sales + Merchandising+ Food & beverage Profit or loss = total revenue - total costs Create scenarios to find out the values of total cost, total revenue and Profit for various number of seats 350, 400, 450 and price/ticket 40,50,60.	[10]
Q. 3	[A] Given excel worksheet contains details players and their scores. You need to generate following information from the same using Pivot Tables and Charts. Use visualization wherever needed. [Q3A sheet] What are the averages for yards in each team? Find the team wise position wise catches. Find the number of players in various conferences. Find the team with the highest catches. Find the conference wise yards for each team. [B] Given excel worksheet contains the invoice data of stationary items. The manager needs to quickly have the respective details of the bill when the invoice number and the required detail name is entered. If he enters “12347” and “Total”, it will return 99.8. [Q. 3 B Sheet]	[10]
Q. 4	Use Mesho Worksheet and create following reports 1. find out total GST paid for the orders the price greater than 1500 2. Find out toal shipping_charges for orders where the order_status is “Delivered”. 3. Find out total price for orders from “Odisha” State 4. find out total gst_amount for orders which are Delivered and shipping_charges_total is greater than 100 find out total price amount for orders which are “shipped”	[10]
Q. 5	Use file sales Worksheet file and answer 1) Using pivot table find out yearly and within yearly quarterly revenue 2) use conditional formatting to highlight the quarter with the highest revenue 3) find out yearwise and region wise revenue 4) create the pivot chart for revenue salesperson wise 5) find out the sales person who gave us highest revenue 6) find out which product is getting sold highest 7) find out the product wise revenue and which product gave highest revenue	[10]
Q. 6	The Worksheet Students_Grades contains student’s grades on an exam. The table for assigning grades is as follows: Score Grade Below 60 D 60-79 C 80-89 B 90 and above A Use Excel functions to return each student’s letter grade on this exam.	[10]