Question

Create an Activity Report (Weekly) for a Sales Representative working in a reputed home appliances manufacturing company. Details recorded should contain Date of Visit, Day of Visit, Name of Shop/Dealer Visited, Address, Phone Number, Name of Product (Dealing), Type of Response (by the Dealer), Demand of Product and Duration Spent (in hrs)..

1. Fill data in Date of Visit and Day of Visit using Fill Series.
2. Name the worksheet created above as Weekly Visit Report.
3. Create a product-wise, Dealer-wise Monthly Report which should include Total Hours Spent.
4. Count the total number of dealers visited and dealers who gave a positive response.

Create a worksheet to record sales of home appliances sold by M/s Home Maker Ltd. In the following format:

Date of Sales	Name of Customers	Name of Products	Make	Quantity	Sales Amount

The product lists includes Television sets, Refrigerators, Micro wave ovens, Water Coolers, Air Coolers, Geezers and Air conditioners of different Makes (and models). The cost of price of television is ranging from Rs. 10,000 to Rs. 56,000; refrigerator is Rs. 13,000 to Rs. 45,000, micro wave ovens, water coolers, geezers and air coolers are from Rs. 8,000 to Rs. 25,000 and Air Conditioners are from Rs. 18,000 to Rs. 55,000. The shopkeeper sells these products, adding 17.25% more on the cost price. He provides a discount of 4.35% on the total amount if any customer purchases two products on the same date. Enter 30 records of different dates (for a month) and different customers accordingly. Calculate the following:

1. Product-wise weekly sales and discount.
2. Calculate the profit of the shopkeeper.
3. Product-wise total sales of the month and discount offered.

Answer 1

Weekly Activity Report:

Tabular Layout Setup

Date of Visit	Day of Visit	Dealer Name	Address	Phone Number	Product Name	Type of Response	Demand (Units)	Duration (Hours)
03 08-2026	Monday	Vijay Sales	Mumbai	9876543210	Refrigerator	Positive	15	2.5
04-08-2026	Tuesday	Croma Retail	Pune	9876543211	Television	Negative	0	1.0
05-08-2026	Wednesday	Kohinoor Elec.	Thane	9876543212	Air Cooler	Positive	25	3.0
06-08-2026	Thursday	Reliance Digital	Nashik	9876543213	Micro wave	Positive	10	1.5
07-08-2026	Friday	Next Retail	Nagpur	9876543214	Water Cooler	Neutral	5	2.0
08-08-2026	Saturday	Ezone Hub	Kolhapur	9876543215	Geezer	Positive	30	4.0
09-08-2026	Sunday	Great Eastern	Solapur	9876543216	Air Conditioner	Negative	0	1.0

• Task a (Fill Data Sequences):
  • Type 03-08-2026 in cell A2. Click and drag its bottom-right AutoFill handle down to cell A8 to generate sequential dates.
  • Type Monday in cell B2. Click and drag its bottom-right AutoFill handle down to cell B8 to generate sequential days.
• Task b (Rename Sheet Tab): Right-click the worksheet tab named Sheet1 at the bottom left, click Rename, type Weekly Visit Report, and press [Enter].
• Task c (Total Hours Summary Formula): Enter =SUM(I2:I8) in any empty cell to aggregate total duration spent.
• Task d (Statistical Counts Formulas):
  • Total number of dealers visited: =COUNTA(C2:C8)
  • Dealers who gave a positive response: =COUNTIF(G2:G8, “Positive”)

M/s Home Maker Ltd. Report:

• Cost Prices are picked from inside the exact ranges printed in your book text.
• Sales Amount incorporates the 17.25% markup on cost price: Quantity * Cost Price * 117.25%.
• Discount Offered applies a 4.35% deduction only if the customer purchased two items on the same date (e.g., Amit Shah on 05-08-2026).

Master Ledger Database Layout

Date of Sales	Name of Customer	Name of Products	Make	Quantity	Cost Price (Rs.)	Sales Amount (Rs.)	Discount Offered (Rs.)
01-08-2026	Ramesh Kumar	Television	LG	1	20,000	=E2*F2*117.25%	0
05-08-2026	Amit Shah	Refrigerator	Samsung	1	15,000	=E3*F3*117.25%	=G3*4.35%
05-08-2026	Amit Shah	Micro wave	Samsung	1	10,000	=E4*F4*117.25%	=G4*4.35%
12-08-2026	Sneha Patil	Air Cooler	Symphony	1	12,000	=E5*F5*117.25%	0
18-08-2026	Vikas Joshi	Air Conditioner	Voltas	1	35,000	=E6*F6*117.25%	0

• Task a (Product-wise Weekly Sales & Discount):
  • Highlight table range A1:H6, click Insert \(\rightarrow \) PivotTable.
  • Drag the Date and Name of Products fields into the Rows panel area.
  • Drag Sales Amount and Discount Offered into the Values area (both set to Sum).
• Task b (Shopkeeper’s Total Profit Calculation Formula): Enter the following equation into an empty summary cell to compute net operating profits:
  =SUM(G2:G6) - SUM(H2:H6) - SUMPRODUCT(E2:E6, F2:F6)
• Task c (Product-wise Monthly Sales & Discount Summary):
  • Create a separate PivotTable using range A1:H6.
  • Drag Name of Products field exclusively into the Rows area.
  • Drag Sales Amount and Discount Offered into the Values panel (both set to Sum).