Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

need the last 3 please A B C D E F G H Spitzer Company has collected sales forecasts for next year from three people.

need the last 3 please image text in transcribed
A B C D E F G H Spitzer Company has collected sales forecasts for next year from three people. \begin{tabular}{|l|r|r|r|r|} \hline Sources of Sales Estimate & 1st Quarter & 2nd Quarter & 3rd Quarter & \multicolumn{1}{c|}{ 4th Quarter } \\ \hline a Sales Manager & $520,000 & $410,000 & $370,000 & $610,000 \\ \hline b Marketing Consultant & $540,000 & $480,000 & $400,000 & $630,000 \\ \hline c Production Manager & $460,000 & $360,000 & $350,000 & $580,000 \\ \hline \end{tabular} They have estimated that the cost of goods sold is 70 percent of sales. The company tries to maintain 10 percent of next quarter's expected cost of goods sold as the current quarter's ending inventory. The ending inventory of this year is $25,000. For budgeting, the ending inventory of the next year is expected to be $28,000. 70% 10% \begin{tabular}{l|l} $25,000 & $28,000 \\ \hline \end{tabular} Required a. Complete the spreadsheet below to allow the inventory purchases budget to be prepared for each of the estimates above. Spreadsheet Tips 1. This spreadsheet uses a function called vertical lookup. This function can pull the appropriate values from a table. The form of this function is =VLOOKUP(value,table,columntt). In this example, the table is in cells A5 to F7. Three examples of the use of VLOOKUP follow. 2. Cell B23 is =VLOOKUP(A23,A5:F7,2). This function operates by using the letter " c " in cell A23 to look up a value in the table. Notice that the table is defined as A5:F7 and that the function is looking up the value in the second column, which is Production Manager. 3. Cell C26 is =VLOOKUP(A23,A5:F7,3), In this case, the function is looking up the value in the third column, which is 460,000 . Be sure to count empty columns. 4. Cells D26, E26, and F26 also use the VLoOKuP function. 5. After completing the spreadsheet, you can change the value in cell A23 (a-c) to see the effect of choosing a different source of sales estimate. Change the budget by changing the value in cell A23. Inventory Purchases Budget Based on Estimates by: \begin{tabular}{|c|c|c|c|c|c|} \hline c & Production Manager & & & & \\ \hline & & 1st Quarter & 2nd Quarter & 3rd Quarter & 4th Quarter \\ \hline & Expected Sales & $460,000 & $360,000 & $350,000 & $580,000 \\ \hlinex & Cost of Goods Sold \% & 70% & 70% & 70% & 70% \\ \hline & Cost of Budgeted Sales & 322,000 & 252,000 & 245,000r & 406,000 \\ \hline+ & Desired Ending Inventory & 25,200 & 24,500 & 40,600r & 28,000 \\ \hline & Inventory Needed & r & & 1 & \\ \hline & Beginning Inventory & r & r & r & \\ \hline & Amount to Purchase & r & & 7 & \\ \hline \end{tabular} A B C D E F G H Spitzer Company has collected sales forecasts for next year from three people. \begin{tabular}{|l|r|r|r|r|} \hline Sources of Sales Estimate & 1st Quarter & 2nd Quarter & 3rd Quarter & \multicolumn{1}{c|}{ 4th Quarter } \\ \hline a Sales Manager & $520,000 & $410,000 & $370,000 & $610,000 \\ \hline b Marketing Consultant & $540,000 & $480,000 & $400,000 & $630,000 \\ \hline c Production Manager & $460,000 & $360,000 & $350,000 & $580,000 \\ \hline \end{tabular} They have estimated that the cost of goods sold is 70 percent of sales. The company tries to maintain 10 percent of next quarter's expected cost of goods sold as the current quarter's ending inventory. The ending inventory of this year is $25,000. For budgeting, the ending inventory of the next year is expected to be $28,000. 70% 10% \begin{tabular}{l|l} $25,000 & $28,000 \\ \hline \end{tabular} Required a. Complete the spreadsheet below to allow the inventory purchases budget to be prepared for each of the estimates above. Spreadsheet Tips 1. This spreadsheet uses a function called vertical lookup. This function can pull the appropriate values from a table. The form of this function is =VLOOKUP(value,table,columntt). In this example, the table is in cells A5 to F7. Three examples of the use of VLOOKUP follow. 2. Cell B23 is =VLOOKUP(A23,A5:F7,2). This function operates by using the letter " c " in cell A23 to look up a value in the table. Notice that the table is defined as A5:F7 and that the function is looking up the value in the second column, which is Production Manager. 3. Cell C26 is =VLOOKUP(A23,A5:F7,3), In this case, the function is looking up the value in the third column, which is 460,000 . Be sure to count empty columns. 4. Cells D26, E26, and F26 also use the VLoOKuP function. 5. After completing the spreadsheet, you can change the value in cell A23 (a-c) to see the effect of choosing a different source of sales estimate. Change the budget by changing the value in cell A23. Inventory Purchases Budget Based on Estimates by: \begin{tabular}{|c|c|c|c|c|c|} \hline c & Production Manager & & & & \\ \hline & & 1st Quarter & 2nd Quarter & 3rd Quarter & 4th Quarter \\ \hline & Expected Sales & $460,000 & $360,000 & $350,000 & $580,000 \\ \hlinex & Cost of Goods Sold \% & 70% & 70% & 70% & 70% \\ \hline & Cost of Budgeted Sales & 322,000 & 252,000 & 245,000r & 406,000 \\ \hline+ & Desired Ending Inventory & 25,200 & 24,500 & 40,600r & 28,000 \\ \hline & Inventory Needed & r & & 1 & \\ \hline & Beginning Inventory & r & r & r & \\ \hline & Amount to Purchase & r & & 7 & \\ \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

Cost Accounting A Managerial Emphasis

Authors: Charles T Horngren

4th Edition

0131797395, 978-0131797390

More Books

Students also viewed these Accounting questions

Question

What are the general types of interviews? Explain each.

Answered: 1 week ago

Question

6 How can HRM contribute to ethical management and sustainability?

Answered: 1 week ago