Calculating Total Retail Value and Key Metrics
Calculating Total Retail Value
To calculate the Total Retail Value for Category 1001-SS and other categories, use the following formula:
- In the first cell under Total Retail Value (e.g., I5), enter:
=D5*E5 - Drag this formula down to apply it to the other categories.
Calculating Totals
Use the SUM function to calculate totals for Quantity and Total Retail Value:
- In the Quantity column (e.g., D30), enter:
=SUM(D5:D29) - In the Total Retail Value column (e.g., I30), enter:
=SUM(I5:I29)
Today’s Date
- To display today’s date, use the formula:
=TODAY()
Summary Calculations
- Number of Categories:
=COUNTA(A5:A29) - Average Retail Price:
=AVERAGE(E5:E29) - Median Retail Price:
=MEDIAN(E5:E29) - Highest Retail Price:
=MAX(E5:E29) - Lowest Retail Price:
=MIN(E5:E29)
Adding a Footer
- Go to the Insert tab, click Text, then choose Header & Footer.
- Scroll to the bottom of the worksheet and click in the Left Section of the footer.
- Click Header & Footer Tools on the ribbon, select Current Date, and then click Sheet Name.
Formatting
- Background Color: Select cells B4-E6 and apply the “White, Background 1, Darker 5%” fill color.
- Borders: Apply borders around cells B4-E6, column headings, the last row of products, and a double border below the totals row (row 30).
- Indentation: Indent the “Sales” and “Interest” income categories, and the six expense categories two spaces.
Adding “Target for Bonus”
- Insert a blank row above “Bonus %”.
- Type “Target for Bonus” in the appropriate cell and “$35,000” in cell B29.
Calculating Total Sales and Interest
- Total Sales: In cell N7 (or the appropriate cell), enter:
=SUM(B7:M7) - Total Interest: In cell N8 (or the appropriate cell), enter:
=SUM(B8:M8)
Calculating Total Income
- In the January Total Income cell (e.g., B9), enter:
=B7+B8 - Copy this formula across for the remaining months and year total.
Calculating Projected Expenses
- For Product expenses in January (B12), enter:
=B7*$B$25 - Copy this formula across and down for other expense categories and months.
Calculating Bonuses
- In the Bonuses cell for January (e.g., B17), enter:
=IF(B7>=$B$29,B7*$B$30,0) - Copy this formula across for the remaining months.
Calculating Total Expenses
- In column N for each expense category, enter:
=SUM(B12:M12) - In the Total Expenses cell for January (e.g., B18), enter:
=SUM(B12:B17) - Copy this formula across for the remaining months and year total.
Calculating Profit/Loss
- In the Profit/Loss row for January (e.g., B20), enter:
=B9-B18 - Copy this formula across for the remaining months and year total.
What-If Analysis (Option 1)
- Change the Advertising % (B27) to 15%.
- Copy the Total Expenses and Profit/Loss rows and paste them under the “Option 1” heading using Paste Special > Values and Number formats.
- Change the Advertising % back to 10%.