Answered step by step
Verified Expert Solution
Link Copied!

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

  1. ABC Company has the following data for its monthly sales. Complete the % of Annual Sales row.

image text in transcribed

Jan- Dec = 575,000

  1. 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.
  • image text in transcribed

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

  1. 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.
    1. 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. image text in transcribed
    2. Use the partial income statement generated in Problem 4 along with the following additional information to complete ABC Companys forecasted income statement in Excel.
  2. Rent expense is $1,000 per month. However, the landlord has indicated that rent will go up to $1,250 in the fourth quarter.
  3. 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. image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Oxford Handbook Of Computational Economics And Finance

Authors: Shu-Heng Chen, Mak Kaboudan, Ye-Rong Du

1st Edition

0199844372, 978-0199844371

More Books

Students also viewed these Finance questions