Question
In: Accounting
In the green cells calculate total Gross Profit (i.e., Sales - COGS) using the condition(s) as...
In the green cells calculate total Gross Profit (i.e., Sales - COGS) using the condition(s) as specified and without creating a helper column, using Filters, or Pivot Tables.
| Date | Product | Region | SalesRep | Customer | Sales | COGS | Gross Profit | ||
| 4/19/2020 | Product3 | Region3 | SalesRep2 | Customer16 | $ 14,046 | $ 5,337 | All products: | ||
| 4/19/2020 | Product7 | Region4 | SalesRep15 | Customer72 | $ 2,504 | $ 1,703 | Product9 only: | ||
| 4/19/2020 | Product2 | Region4 | SalesRep18 | Customer71 | $ 1,505 | $ 843 | Product3 and SalesRep16 only: | ||
| 4/19/2020 | Product6 | Region4 | SalesRep14 | Customer88 | $ 4,232 | $ 2,793 | |||
| 4/19/2020 | Product3 | Region4 | SalesRep3 | Customer65 | $ 5,947 | $ 3,390 | |||
| 4/19/2020 | Product1 | Region8 | SalesRep6 | Customer100 | $ 5,721 | $ 3,204 | |||
| 4/19/2020 | Product10 | Region8 | SalesRep16 | Customer68 | $ 14,744 | $ 5,308 | |||
| 4/19/2020 | Product7 | Region2 | SalesRep1 | Customer85 | $ 4,018 | $ 2,371 | |||
| 4/19/2020 | Product10 | Region5 | SalesRep6 | Customer6 | $ 6,442 | $ 4,445 | |||
Solutions
Expert Solution
THE ANSWERS HAVE BEEN CALCULATED USING PIVOT TABLE
| Date | Product | Region | SalesRep | Customer | Sales | COGS | Gross Profit | ||
| 4/19/2020 | Product3 | Region3 | SalesRep2 | Customer16 | 14,046 | 5,337 | All products: | 29765 | |
| 4/19/2020 | Product7 | Region4 | SalesRep15 | Customer72 | 2,504 | 1,703 | Product9 only: | 0 | |
| 4/19/2020 | Product2 | Region4 | SalesRep18 | Customer71 | 1,505 | 843 | Product3 and SalesRep16 only: | 0 | |
| 4/19/2020 | Product6 | Region4 | SalesRep14 | Customer88 | 4,232 | 2,793 | |||
| 4/19/2020 | Product3 | Region4 | SalesRep3 | Customer65 | 5,947 | 3,390 | |||
| 4/19/2020 | Product1 | Region8 | SalesRep6 | Customer100 | 5,721 | 3,204 | |||
| 4/19/2020 | Product10 | Region8 | SalesRep16 | Customer68 | 14,744 | 5,308 | |||
| 4/19/2020 | Product7 | Region2 | SalesRep1 | Customer85 | 4,018 | 2,371 | |||
| 4/19/2020 | Product10 | Region5 | SalesRep6 | Customer6 | 6,442 | 4,445 | |||
|
1. All Products |
|||||||||
| All products | |||||||||
| Row Labels | Sum of Sales | Sum of COGS | |||||||
| Product1 | 5721 | 3204 | Gross Profit of all Products | ||||||
| Product10 | 21186 | 9753 | |||||||
| Product2 | 1505 | 843 | Total Sales | 59159 | |||||
| Product3 | 19993 | 8727 | Total COGS | 29394 | |||||
| Product6 | 4232 | 2793 | Gross Profit | 29765 | |||||
| Product7 | 6522 | 4074 | |||||||
| Grand Total | 59159 | 29394 | |||||||
| 2. Product 9 only | |||||||||
| Product | (All) | ||||||||
| Value | |||||||||
| Sum of Sales | Sum of COGS | NO PRODUCT IS THERE | |||||||
| 59159 | 29394 | ||||||||
| 3. Product3 and SalesRep16 only | |||||||||
| Product | Product3 | ||||||||
| SalesRep | SalesRep16 | ||||||||
| All products | |||||||||
| Sum of Sales | Sum of COGS | NO Product3 and SalesRep16 | |||||||
 ekkarill92 answered 1 year agoRelated Solutions1. Calculate Revenues, COGS, Gross Profit and Gross Margin in year 2 based on the following:...1. Calculate Revenues, COGS, Gross Profit and Gross Margin in
year 2 based on the
following:
Yr. 1
Revenues 500
COGS 400
Gross Profit 100
Gross Margin 20%
Sales rise 5%, 3% due to increase in volume and 2% due to
increase in price. COGS is 80% variable.
2. What is the primary driver of sales growth for the following
company? Explain.
Assume COGS is 60% variable.
Yr. 1 Yr. 2
Revenues 800 850
COGS 500 519
Gross Profit...
If Gross Profit Margin is 55%, what is COGS %?If Gross Profit Margin is 55%, what is COGS
%?
Figure out how to calculate ending inventory (costs assigned to ending inventory and COGS) Also COGS (gross profit) using LIFO periodic inventory system.
Figure out how to calculate ending inventory (costs assigned to ending inventory and COGS) Also COGS (gross profit) using LIFO periodic inventory system.
Jan1/ Beginning inventory 160 @2= 320
Jan3 sales 110 units @$8
Feb14 purchase 246 units @ $3 = 738
Feb15 Sales 180 units @ $8
Jun30 purchase 210 units @ $4 = $840
Nov6 Sales 158 units @ 8
Nov19 Purchase 68units @ $5 = $340
total 684 units $2,238 448 units
Using LIFO, calculate ending inventory, cost of goods sold, sales revenue, and gross profit.Exercise 6-4A Calculate inventory amounts when costs are rising
(LO6-3)During the year, TRC Corporation has the following inventory
transactions.DateTransactionNumber of UnitsUnit CostTotal CostJan.1Beginning inventory52$44$2,288Apr.7Purchase132466,072Jul.16Purchase202499,898Oct.6Purchase112505,600498$23,858For the entire year, the company sells 432 units of inventory
for $62 each.Exercise 6-4A Part 22. Using LIFO, calculate ending inventory, cost
of goods sold, sales revenue, and gross profit.
1. Using FIFO, calculate ending inventory, cost of goods sold, sales revenue, and gross profit.
During the year, TRC Corporation has the following inventory transactions. Date Transaction Number of Units Unit Cost Total Cost Jan. 1 Beginning inventory 60 $ 52 $ 3,120 Apr. 7 Purchase 140 54 7,560 Jul. 16 Purchase 210 57 11,970 Oct. 6 Purchase 120 58 6,960 530 $ 29,610 For the entire year, the company sells 450 units of inventory for $70 each.
1. Using FIFO, calculate ending inventory, cost of goods sold, sales revenue, and gross profit.
2. Using...
The Brenmar Sales Company had a gross profit margin (gross profit/sales) of 28% and sales of...The
Brenmar Sales Company had a gross profit margin (gross
profit/sales) of 28% and sales of $8.6 million last year. 70% of
the firm’s sales are in credit, and the remainder are cash sales.
Brenmar’s current assets equal $1.4 million, its current
liabilities equal $299,000, and it has $106,000 in cash plus
marketable securities.
A. If Brenmar’s accounts receivable equal $562,600, what is
its average collection period?
B. If Brenmar reduces its average collection period is 25
days, what will...
Please format answer into a table Calculate gross sales, variable costs, and gross margin (profit) for...Please format answer into a table
Calculate gross sales, variable costs, and gross margin
(profit) for each product and for an entire company.
Calculate break-even sales dollars for a company.
Determine the relevant cash flows for a planned asset purchase.
Madison Company produces four lines of accessories for major
U.S. combine manufacturers. The lines are known by the code letters
A, B, C, and D. The current sales mix for Madison Company and the
contribution margin ratio for these product...
EXCEL: In cell I4 insert formula(s) that will calculate the sum of Sales where COGS is...EXCEL: In cell I4 insert formula(s) that will calculate the sum
of Sales where COGS is greater than or equal to its average.
Date
Product
Region
SalesRep
Customer
Sales
COGS
Sum of Sales:
4/19/2020
Product3
Region3
SalesRep2
Customer16
$
14,046
$
5,337
4/19/2020
Product7
Region4
SalesRep15
Customer72
$
2,504
$
1,703
4/19/2020
Product2
Region4
SalesRep18
Customer71
$
1,505
$
843
4/19/2020
Product6
Region4
SalesRep14
Customer88
$
4,232
$
2,793
4/19/2020
Product3
Region4
SalesRep3
Customer65
$
5,947
$
3,390
4/19/2020
Product1...
Distinguish between gross profit as a percentage of cost and gross profit as a percentage of sales price.Distinguish between gross profit as a percentage of cost and
gross profit as a percentage of sales price. Convert the following
gross profit percentages based on cost to gross profit percentages
based on sales price: 25% and 33 1 /3%. Convert the following gross
profit percentages based on sales price to gross profit percentages
based on cost: 33 1 /3% and 60%.
On the Movies worksheet, in cell B1,(Total Gross Sales) enter a function that will calculate the...
On the Movies worksheet, in cell B1,(Total Gross Sales) enter a
function that will calculate the total gross sales for records
visible in the table. Format as Accounting. In cell B2(Average
Gross Sales), enter a function that will calculate the average
gross sales for records visible in the table. Format as Accounting.
In cell B3 (Total Movies) enter a function that will calculate the
number of movies visible in the table, using the MovieID field.
Total Gross Sales
(filtered)
Average...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|