Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Hey! I need help with the following three steps of this excel assignment. One where we have to find the advertising costs for the following
Hey! I need help with the following three steps of this excel assignment. One where we have to find the advertising costs for the following years, and the cost of goods sold with the tax rate. Thank you!
\begin{tabular}{|c|c|c|c|c|c|c|c|} \hline Imput variables & 2020 & 2021 & 2022 & 2023 & 2024 & 2025 & 2026 \\ \hline \multicolumn{8}{|l|}{ REVENUE } \\ \hline Net Sales/Sales Revenue & $44,000 & $47,800 & $53,800 & $59,503 & $65,810 & $72,786 & $80,501 \\ \hline Cost of Goods Sold (CGS) & 18,000 & 25,500 & 33,500 & $36,892 & $40,802 & $45,127 & $49,911 \\ \hline Gross Profit & $26,000 & $22,300 & $20,300 & $22,611 & $25,008 & $27,659 & $30,590 \\ \hline \multicolumn{8}{|l|}{ EXPENSE } \\ \hline Selling, General, and Administrative (SG\&A) & 1,900 & 2,200 & 3,400 & 4,596 & 6,212 & 8,397 & 11,350 \\ \hline Advertising & 1,900 & 1,700 & 2,750 & & & & \\ \hline Depreciation and Amortization & 5,100 & 5,400 & 5,500 & 5,713 & 5,934 & 6,164 & 6,402 \\ \hline Rent Expenses & 2,850 & 2,850 & 2,850 & 2,850 & 2,850 & 2,850 & 2,850 \\ \hline Other Expenses & 4,800 & 4,900 & 5,000 & 5,103 & 5,208 & 5,315 & 5,425 \\ \hline Total Expenses & $16,550 & $17,050 & $19,500 & & & & \\ \hline & & & & & & & \\ \hline Earnings before Taxes (EBT) & $9,450 & $5,250 & $800 & & & & \\ \hline Tax Expense & $3,265 & $2,035 & $120 & & & & \\ \hline Net Income & $6,185 & $3,215 & $680 & & & & \\ \hline \end{tabular} Assume that the Cost of Goods Sold (CGS) as a percentage of Net Sales/Sales Revenue (i.e., the ratio of CGS to Net Sales) will be constant in the years 2022 through 2026 and equal to the percentage in 2022. You will need to calculate that percentage (ratio). Assume that the tax rate will be constant in the years 2022 through 2026 and equal to the tax rate in 2022. You will need to calculate that value (i.e., the tax expense as a percentage of the EBT). Starting with 2023 and beyond, assume that Advertising will change by the same dollar amount (not the same percentage) from one year to the next-e.g., from 2022 to 2023 . That amount equals the Average Annual Change (in dollars) between 2020 and 2022Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started