Answered step by step
Verified Expert Solution
Question
1 Approved Answer
ABC Company has the following data for its monthly sales. Complete the % of Annual Sales row. Jan- Dec = 575,000 Using the same data
- ABC Company has the following data for its monthly sales. Complete the % of Annual Sales row.
Jan- Dec = 575,000
- Using the same data as in Problem 1, assume that ABC Company expects a 10% increase in sales in the coming year (10% more than the $575,000 it had in the past year). Prepare its sales forecast, assuming the company breaks its sales down by month using the same percentages as the actual sales from the past year, which you calculated in the first problem.
10% times each month then add to each month (40,000*10=4,000) =44,000 Jan
Jan- Dec = 575,000
Jan | fed | mar | apr | may | jun | jul | aug | oct | nov |
44,000 | 46,200 | 51,700 | 61,600 | 66,000 | 78,100 | 58,000 | 58,000 | 40,7000 | 42,900 |
6.96% | 7.30% | 8.17% | 9.74% | 10.43% | 12.35% | 9.22% | 9.22% | 8.00% | 6.43% |
Dec 34,000= 5.39%
Jan= 40,000/575,000= 6.96$, Feb= 42000/575000=7.30, Mar = 47000 /575000=8.17, Apr+ 56000/575000= 9.74, May= 60000/575000=10.43, Jun =71000/575000=12.35, Jul=53000/575000=9.22, Aug= 53000/575000=9.22, Sep= 46000/57500,=8.00, Oct=37000/6.43 Dec 31000/575000= 5.39
- ABC Company anticipates its sales being a bit lower than normal in January and February of the coming year due to major road construction on the street where it is located, which will draw away foot traffic from the store. The company anticipates that this will reduce its sales in these two months by 5%. Use the information from Problems 12 to update the sales forecast.
- ABC Companys cost of goods sold last year was 60%. It anticipates that this will be the same in the coming year. Its sales returns and allowances are small, normally 1% of sales. Use the information from Problems 13 to estimate the companys sales returns and allowances, net sales, and cost of goods sold and calculate its gross margin.
- Use the partial income statement generated in Problem 4 along with the following additional information to complete ABC Companys forecasted income statement in Excel.
- Rent expense is $1,000 per month. However, the landlord has indicated that rent will go up to $1,250 in the fourth quarter.
- Depreciation expense is $2,250 per month and does not change throughout the year. Salaries expense is $1,500 per month and is expected to go up by 10% in the second half of the year, when a new compensation plan will be implemented.Utilities expense is $5,000 for the entire year and should be allocated to each month based on that months percentage of annual sales. Interest expense is $500 per month. tax is 25% of operating income less interest expense.
ABC Company Monthly Income Statements \begin{tabular}{|l|l|l|l|l|l|l|l|l|l|l|l|l|l|} \hline & Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total \\ \hline Gross Sales & & & & & & & & & & & & & \\ \hline% of Annual Sales & & & & & & & & & & & & & \\ \hline \end{tabular} ABC Company Monthly Income Statements \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Jan & Feb & Mar & Apr & May & Jun & Jul & Aug & Sep & Oct & Nov & Dec & Total \\ \hline \begin{tabular}{l} Gross Sales \\ % of Annual Sales \end{tabular} & $40,000 & $42,000 & $47,000 & $56,000 & $60,000 & $71,000 & $53,000 & $53,000 & $46,000 & $37,000 & $39,000 & $31,000 & $575,000 \\ \hline \end{tabular} ABC Company Monthly Income Statements \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Jan & Feb & Mar & Apr & May & Jun & Jul & Aug & Sep & Oct & Nov & Dec & Total \\ \hline Gross Sales & $41,800 & $43,890 & $51,700 & $61,600 & $66,000 & $78,100 & $58,300 & $58,300 & $50,600 & $40,700 & $42,900 & $34,100 & $627,990 \\ \hline% of Annual Sales & 7.0% & 7.3% & 8.2% & 9.7% & 10.4% & 12.3% & 9.2% & 9.2% & 8.0% & 6.4% & 6.8% & 5.4% & 100.0% \\ \hline \begin{tabular}{l} Sales Returns \& \\ Allowances \end{tabular} & $418 & $439 & $517 & $616 & $660 & $781 & $583 & $583 & $506 & $407 & $429 & $341 & $6,280 \\ \hline Net Sales & 41,382 & 43,451 & 51,183 & 60,984 & 65,340 & 77,319 & 57,717 & 57,717 & 50,094 & 40,293 & 42,471 & 33,759 & 621,710 \\ \hline Cost of Goods Sold & 24,829 & 26,071 & 30,710 & 36,590 & 39,204 & 46,391 & 34,630 & 34,630 & 30,056 & 24,176 & 25,483 & 20,255 & 373,026 \\ \hline Gross Margin & 16,553 & 17,380 & 20,473 & 24,394 & 26,136 & 30,928 & 23,087 & 23,087 & 20,038 & 16,117 & 16,988 & 13,504 & 248,684 \\ \hline Rent Expense & & & & & & & & & & & & & \\ \hline Depreciation Expense & & & & & & & & & & & & & \\ \hline Salaries Expense & & & & & & & & & & & & & \\ \hline Utility Expense & & & & & & & & & & & & & \\ \hline Operating Income & & & & & & & & & & & & & \\ \hline Interest Expense & & & & & & & & & & & & & \\ \hline Income Tax Expense & & & & & & & & & & & & & \\ \hline Net Income & & & & & & & & & & & & & \\ \hline \end{tabular} ABC Company Monthly Income Statements \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Jan & Feb & Mar & Apr & May & Jun & Jul & Aug & Sep & Oct & Nov & Dec & Total \\ \hline Gross Sales & $41,800 & $43,890 & $51,700 & $61,600 & $66,000 & $78,100 & $58,300 & $58,300 & $50,600 & $40,700 & $42,900 & $34,100 & $627,990 \\ \hline% of Annual Sales & 7.0% & 7.3% & 8.2% & 9.7% & 10.4% & 12.3% & 9.2% & 9.2% & 8.0% & 6.4% & 6.8% & 5.4% & 100.0% \\ \hline \begin{tabular}{l} Sales Returns \& \\ Allowances \end{tabular} & & & & & & & & & & & & & \\ \hline Net Sales & & & & & & & & & & & & & \\ \hline Cost of Goods Sold & & & & & & & & & & & & & \\ \hline Gross Margin & & & & & & & & & & & & & \\ \hline \end{tabular} ABC Company Monthly Income Statements \begin{tabular}{|l|l|l|l|l|l|l|l|l|l|l|l|l|l|} \hline & Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total \\ \hline Gross Sales & & & & & & & & & & & & & \\ \hline% of Annual Sales & & & & & & & & & & & & & \\ \hline \end{tabular} ABC Company Monthly Income Statements \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Jan & Feb & Mar & Apr & May & Jun & Jul & Aug & Sep & Oct & Nov & Dec & Total \\ \hline \begin{tabular}{l} Gross Sales \\ % of Annual Sales \end{tabular} & $40,000 & $42,000 & $47,000 & $56,000 & $60,000 & $71,000 & $53,000 & $53,000 & $46,000 & $37,000 & $39,000 & $31,000 & $575,000 \\ \hline \end{tabular} ABC Company Monthly Income Statements \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Jan & Feb & Mar & Apr & May & Jun & Jul & Aug & Sep & Oct & Nov & Dec & Total \\ \hline Gross Sales & $41,800 & $43,890 & $51,700 & $61,600 & $66,000 & $78,100 & $58,300 & $58,300 & $50,600 & $40,700 & $42,900 & $34,100 & $627,990 \\ \hline% of Annual Sales & 7.0% & 7.3% & 8.2% & 9.7% & 10.4% & 12.3% & 9.2% & 9.2% & 8.0% & 6.4% & 6.8% & 5.4% & 100.0% \\ \hline \begin{tabular}{l} Sales Returns \& \\ Allowances \end{tabular} & $418 & $439 & $517 & $616 & $660 & $781 & $583 & $583 & $506 & $407 & $429 & $341 & $6,280 \\ \hline Net Sales & 41,382 & 43,451 & 51,183 & 60,984 & 65,340 & 77,319 & 57,717 & 57,717 & 50,094 & 40,293 & 42,471 & 33,759 & 621,710 \\ \hline Cost of Goods Sold & 24,829 & 26,071 & 30,710 & 36,590 & 39,204 & 46,391 & 34,630 & 34,630 & 30,056 & 24,176 & 25,483 & 20,255 & 373,026 \\ \hline Gross Margin & 16,553 & 17,380 & 20,473 & 24,394 & 26,136 & 30,928 & 23,087 & 23,087 & 20,038 & 16,117 & 16,988 & 13,504 & 248,684 \\ \hline Rent Expense & & & & & & & & & & & & & \\ \hline Depreciation Expense & & & & & & & & & & & & & \\ \hline Salaries Expense & & & & & & & & & & & & & \\ \hline Utility Expense & & & & & & & & & & & & & \\ \hline Operating Income & & & & & & & & & & & & & \\ \hline Interest Expense & & & & & & & & & & & & & \\ \hline Income Tax Expense & & & & & & & & & & & & & \\ \hline Net Income & & & & & & & & & & & & & \\ \hline \end{tabular} ABC Company Monthly Income Statements \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Jan & Feb & Mar & Apr & May & Jun & Jul & Aug & Sep & Oct & Nov & Dec & Total \\ \hline Gross Sales & $41,800 & $43,890 & $51,700 & $61,600 & $66,000 & $78,100 & $58,300 & $58,300 & $50,600 & $40,700 & $42,900 & $34,100 & $627,990 \\ \hline% of Annual Sales & 7.0% & 7.3% & 8.2% & 9.7% & 10.4% & 12.3% & 9.2% & 9.2% & 8.0% & 6.4% & 6.8% & 5.4% & 100.0% \\ \hline \begin{tabular}{l} Sales Returns \& \\ Allowances \end{tabular} & & & & & & & & & & & & & \\ \hline Net Sales & & & & & & & & & & & & & \\ \hline Cost of Goods Sold & & & & & & & & & & & & & \\ \hline Gross Margin & & & & & & & & & & & & & \\ \hline \end{tabular}
Step 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