Janice Smith opened a small clothing store three years ago. She is setting her budget for the fourth year, but needs to determine certain key amounts from the previous three years' in order to do this. Below is the information from her first three years of business.
Projected Year 1 Year 2 Year 3 Year 4 Sales $105,000 $132,000 $165,500 Administrative Expenses 18,500 28,300 40,730 Selling Expenses 13,300 15,400 22,780 Other expenses 3,540 3,870 6,350 Net Income
Percentage increase in sales: Increase Increase over Year 1 over Year 2 Required: 1. Using Excel formulas, determine the Net Income for years 1 through 3. (Note: to find net income, take "Sales" and deduct all of your expenses. 2.
Using Excel formulas, determine the percentage increase in Sales each year. (To do this, take the current years' sales, subtract the previous years' sales and divide this amount by the previous years' sales.) Make sure to show the percentage signs in your answer and round to two decimal places. 3 Janice is trying to predict his income for year 4. She expects that her sales will be 22% higher than they were in year 3, her Administrative Expenses will be $12,000 higher than in year 3, her Selling expenses will be $10,000 higher than in year 3 and her "Other" expenses will be $7,100 total. Using Excel formulas, determine the projected sales, expenses and net income for year 4. (Note: You must show Excel formulas for the new projected sales and expenses, as well as the new net income.) 4. Using the "Fill color" feature, add color to the cells with the Net Income amounts. 5. Place "$" signs in the Projected Sales and Net Income cells.
The answer is as follows:
Formulae used are as follows:
Use fill color option to fill color in Net Income Row
Janice Smith opened a small clothing store three years ago. She is setting her budget for...
Income statement data for Boone Company for two recent years ended December 31, are as follows: Sales Cost of goods sold Gross profit Selling expenses Administrative expenses Total operating expenses Income before income tax Income tax expenses Net income Current Year $844,800 728,000 $116,800 $35,380 32,500 $67,880 $48,920 19,600 $29,320 Previous Year $640,000 560,000 $80,000 $29,000 25,000 $54,000 $26,000 10,400 $15,600 a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease) for the current year when compared...
Horizontal Analysis of the Income Statement Income statement data for Winthrop Company for two recent years ended December 31, are as follows: Current Year Previous Year Sales Cost of goods sold Gross profit Selling expenses Administrative expenses $541,200 456,000 585,200 $440,000 380,000 $23,730 $60,000 $21,000 18,000 $39,000 21,780 Total operating expenses $45,510 539,690 15,900 Income before income tax Income tax expenses $21,000 8,400 Net income $23,790 $12,600 a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease)...
Horizontal Analysis of the Income Statement Income statement data for Winthrop Company for two recent years ended December 31, are as follows: Current Year Previous Year Sales $496,100 $410,000 Cost of goods sold 401,200 340,000 Gross profit $94,900 $70,000 Selling expenses $26,640 $24,000 Administrative expenses 23,800 20,000 Total operating expenses $50,440 $44,000 Income before income tax $44,460 $26,000 Income tax expenses 17,800 10,400 Net income $26,660 $15,600 a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease)...
Horizontal Analysis of the Income Statement Income statement data for Winthrop Company for two recent years ended December 31, are as follows: Current Year Previous Year Sales $624,000 $480,000 Cost of goods sold 512,000 400,000 Gross profit $112,000 $80,000 Selling expenses $33,600 $28,000 Administrative expenses 30,720 24,000 Total operating expenses $64,320 $52,000 Income before income tax $47,680 $28,000 Income tax expenses 19,100 11,200 Net income $28,580 $16,800 a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease)...
Horizontal Analysis of the Income Statement Income statement data for Winthrop Company for two recent years ended December 31, are as follows: Current Year Previous Year Sales $460,800 375,000 $360,000 300,000 Cost of goods sold Gross profit $85,800 $60,000 Selling expenses $24,780 $21,000 Administrative expenses 22,680 18,000 Total operating expenses $47,460 $39,000 Income before income tax $21,000 $38,340 15,300 Income tax expenses 8,400 Net income $23,040 $12,600 a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease)...
Income statement data for Winthrop Company for two recent years ended December 31, are as follows: Current Year Previous Year Sales $701,800 $580,000 Cost of goods sold 590,000 500,000 Gross profit $111,800 $80,000 Selling expenses $31,080 $28,000 Administrative expenses 27,370 23,000 Total operating expenses $58,450 $51,000 Income before income tax $53,350 $29,000 Income tax expenses 21,300 11,600 Net income $32,050 $17,400 a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease) for the current year when compared...
Horizontal Analysis of the Income Statement Income statement data for Winthrop Company for two recent years ended December 31, are as follows Current Year $799,800 685,800 $114,000 $34,510 30,480 $64,990 $49,010 19,600 $29,410 Previous Year $620,000 540,000 $80,000 $29,000 24,000 $53,000 $27,000 10,800 $16,200 Sales Cost of goods sold Gross profit Selling expenses Administrative expenses Total operating expenses Income before income tax Income tax expenses Net income a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease)...
Income statement data for Winthrop Company for two recent years ended December 31, are as follows: Current Year Previous Year Sales $762,000 $600,000 Cost of goods sold 650,000 520,000 Gross profit $112,000 $80,000 Selling expenses $32,760 $28,000 Administrative expenses 30,000 24,000 Total operating expenses $62,760 $52,000 Income before income tax $49,240 $28,000 Income tax expenses 19,700 11,200 Net income $29,540 $16,800 a. Prepare a comparative income statement with horizontal analysis, indicating the increase (decrease) for the current year when compared...
Horizontal Analysis of the Income Statement
Income statement data for Boone Company for two recent years
ended December 31, are as follows:
Horizontal Analysis of the Income Statement Income statement data for Boone Company for two recent years ended December 31, are as follows Sales Cost of goods sold Gross profit Selling expenses Administrative expenses Total operating expenses Income before income tax Income tax expenses Net income Current Year $825,500 706,800 $118,700 $35,100 31,250 $66,350 $52,350 20,900 $31,450 Previous Year...
Horizontal Analysis of the Income Statement Income statement
data for Winthrop Company for two recent years ended December 31,
are as follows: Current Year Sales Cost of goods sold Gross profit
$665,600 567,000 Previous Year $520,000 450,000 $98,600 $70,000
Selling expenses$29,500 Administrative expenses 26,460 $25,000
21,000 $ 46,000 Total operating expenses$55,960 Income before
income tax$42,640 $ 24,000 Income tax expenses 17,1009,600 Net
income$ 25,540$ 14,400
Horizontal Analysis of the Income Statement Income statement data for Winthrop Company for two recent...