[Solved] How do I write an IF function to determin

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 15, 2024

How do I write an IF function to determine whether the intercept (iii) and slope (iii) are fixed, variable or mixed cost? The attached images

How do I write an IF function to determine whether the intercept (iii) and slope (iii) are fixed, variable or mixed cost? The attached images are of the data and where I need to plug in the functions.

image text in transcribed

\begin{tabular}{|c|c|c|c|c|c|c|c|} \hline Quarterly Expenses & Type & Q1 & Q2 & Q3 & Q4 & & Annual Total \\ \hline Advertising Stick-ons & Direct Materials & 4,352.50 & 5,097.50 & 6,028.75 & 7,146.25 & $ & 22,625.00 \\ \hline Apprentice Carpenter Labor & Direct Labor & 22,462.50 & 27,387.50 & 33,543.75 & 40,931.25 & $ & 124,325.00 \\ \hline Carpenter Labor & Direct Labor & 35,332.50 & 42,817.50 & 52,548.75 & 64,225.75 & $ & 194,924.50 \\ \hline Cleaning Supplies & Man Overhead & 5,141.25 & 6,013.75 & 7,004.38 & 8,113.13 & $ & 26,272.51 \\ \hline Depreciation & Man Overhead & 91,275.00 & 91,275.00 & 91,275.00 & 91,275.00 & $ & 365,100.00 \\ \hline Painting/finishing Supplies & Direct Materials & 88,052.50 & 107,397.50 & 131,578.75 & 154,596.25 & $ & 481,625.00 \\ \hline General Supplies & Man Overhead & 9,115.00 & 9,115.00 & 9,115.00 & 10,115.00 & $ & 37,460.00 \\ \hline Hardware & Direct Materials & 21,552.50 & 25,197.50 & 31,378.75 & 38,796.25 & $ & 116,925.00 \\ \hline Indirect Salaries & Man Overhead & 118,500.00 & 118,500.00 & 118,500.00 & 118,500.00 & $ & 474,000.00 \\ \hline Information Stickers & Man Overhead & 8,426.25 & 10,298.75 & 12,639.38 & 15,448.13 & $ & 46,812.51 \\ \hline Insurance & Man Overhead & 3,115.00 & 3,115.00 & 3,115.00 & 3,115.00 & $ & 12,460.00 \\ \hline Master Carpenter Labor & Direct Labor & 21,528.75 & 25,946.25 & 31,843.13 & 38,919.38 & $ & 118,237.51 \\ \hline Finished Goods Packaging & Direct Materials & 3,701.25 & 4,523.75 & 5,551.88 & 6,776.63 & $ & 20,553.51 \\ \hline Parts and Repairs & Direct Materials & 4,291.25 & 4,969.75 & 5,326.38 & 5,685.13 & $ & 20,272.51 \\ \hline Shipping Costs & Man Overhead & 33,378.75 & 40,796.25 & 50,068.13 & 61,194.38 & $ & 185,437.51 \\ \hline Utilities & Man Overhead & 11,287.00 & 15,112.00 & 12,451.00 & 21,610.00 & $ & 60,460.00 \\ \hline Warehouse Rent & Man Overhead & 19,275.00 & 19,275.00 & 19,275.00 & 19,275.00 & $ & 77,100.00 \\ \hline Wood & Direct Materials & 543,262.50 & 663,987.50 & 814,893.75 & 995,981.25 & & 3,018,125.00 \\ \hline Quarterly Total & & $1,044,049.50 & 1,220,825.50 & $1,436,136.78 & $1,701,703.78 & & 5,402,715.56 \\ \hline \end{tabular} Direct Labor Hours 6544.0 7996.0 9806.5 11965.5 36312.0 \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline 2b & (i) & (ii) & (iii) & (iii) & (iv) & (iv) & (iv) & (iv) & (v) \\ \hline \multirow[t]{2}{*}{ Description } & \multirow[t]{2}{*}{ Annual Cost } & Fixed, Variable or Mixed? & Intercept & Slope & Fixed? & Variable? & Mixed? & Change from (ii)? & Annual Cost Equation \\ \hline & & & (Fixed cost/qrtr) & \multicolumn{2}{|l|}{ Cost per DLH } & & & & \\ \hline Advertising Stick-ons & 22,625.00 & Variable & 978.3 & 0.52 & & & & & \\ \hline Painting/finishing Supplies & 481,625.00 & Variable & 8,492.3 & 12.33 & & & & & \\ \hline Hardware & 116,925.00 & Variable & (2.0) & 3.22 & & & & & \\ \hline Information Stickers & 46,812.51 & Variable & (54.5) & 1.30 & & & & & \\ \hline Finished Goods Packaging & 20,553.51 & Variable & (11.8) & 0.57 & & & & & \\ \hline Wood & 3,018,125.00 & Variable & (3,511.3) & 83.50 & & & & & \\ \hline Total Direct Materials & 3,706,666.02 & & 5,891.0 & 101.43 & & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|} \hline Quarterly Expenses & Type & Q1 & Q2 & Q3 & Q4 & & Annual Total \\ \hline Advertising Stick-ons & Direct Materials & 4,352.50 & 5,097.50 & 6,028.75 & 7,146.25 & $ & 22,625.00 \\ \hline Apprentice Carpenter Labor & Direct Labor & 22,462.50 & 27,387.50 & 33,543.75 & 40,931.25 & $ & 124,325.00 \\ \hline Carpenter Labor & Direct Labor & 35,332.50 & 42,817.50 & 52,548.75 & 64,225.75 & $ & 194,924.50 \\ \hline Cleaning Supplies & Man Overhead & 5,141.25 & 6,013.75 & 7,004.38 & 8,113.13 & $ & 26,272.51 \\ \hline Depreciation & Man Overhead & 91,275.00 & 91,275.00 & 91,275.00 & 91,275.00 & $ & 365,100.00 \\ \hline Painting/finishing Supplies & Direct Materials & 88,052.50 & 107,397.50 & 131,578.75 & 154,596.25 & $ & 481,625.00 \\ \hline General Supplies & Man Overhead & 9,115.00 & 9,115.00 & 9,115.00 & 10,115.00 & $ & 37,460.00 \\ \hline Hardware & Direct Materials & 21,552.50 & 25,197.50 & 31,378.75 & 38,796.25 & $ & 116,925.00 \\ \hline Indirect Salaries & Man Overhead & 118,500.00 & 118,500.00 & 118,500.00 & 118,500.00 & $ & 474,000.00 \\ \hline Information Stickers & Man Overhead & 8,426.25 & 10,298.75 & 12,639.38 & 15,448.13 & $ & 46,812.51 \\ \hline Insurance & Man Overhead & 3,115.00 & 3,115.00 & 3,115.00 & 3,115.00 & $ & 12,460.00 \\ \hline Master Carpenter Labor & Direct Labor & 21,528.75 & 25,946.25 & 31,843.13 & 38,919.38 & $ & 118,237.51 \\ \hline Finished Goods Packaging & Direct Materials & 3,701.25 & 4,523.75 & 5,551.88 & 6,776.63 & $ & 20,553.51 \\ \hline Parts and Repairs & Direct Materials & 4,291.25 & 4,969.75 & 5,326.38 & 5,685.13 & $ & 20,272.51 \\ \hline Shipping Costs & Man Overhead & 33,378.75 & 40,796.25 & 50,068.13 & 61,194.38 & $ & 185,437.51 \\ \hline Utilities & Man Overhead & 11,287.00 & 15,112.00 & 12,451.00 & 21,610.00 & $ & 60,460.00 \\ \hline Warehouse Rent & Man Overhead & 19,275.00 & 19,275.00 & 19,275.00 & 19,275.00 & $ & 77,100.00 \\ \hline Wood & Direct Materials & 543,262.50 & 663,987.50 & 814,893.75 & 995,981.25 & & 3,018,125.00 \\ \hline Quarterly Total & & $1,044,049.50 & 1,220,825.50 & $1,436,136.78 & $1,701,703.78 & & 5,402,715.56 \\ \hline \end{tabular} Direct Labor Hours 6544.0 7996.0 9806.5 11965.5 36312.0 \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline 2b & (i) & (ii) & (iii) & (iii) & (iv) & (iv) & (iv) & (iv) & (v) \\ \hline \multirow[t]{2}{*}{ Description } & \multirow[t]{2}{*}{ Annual Cost } & Fixed, Variable or Mixed? & Intercept & Slope & Fixed? & Variable? & Mixed? & Change from (ii)? & Annual Cost Equation \\ \hline & & & (Fixed cost/qrtr) & \multicolumn{2}{|l|}{ Cost per DLH } & & & & \\ \hline Advertising Stick-ons & 22,625.00 & Variable & 978.3 & 0.52 & & & & & \\ \hline Painting/finishing Supplies & 481,625.00 & Variable & 8,492.3 & 12.33 & & & & & \\ \hline Hardware & 116,925.00 & Variable & (2.0) & 3.22 & & & & & \\ \hline Information Stickers & 46,812.51 & Variable & (54.5) & 1.30 & & & & & \\ \hline Finished Goods Packaging & 20,553.51 & Variable & (11.8) & 0.57 & & & & & \\ \hline Wood & 3,018,125.00 & Variable & (3,511.3) & 83.50 & & & & & \\ \hline Total Direct Materials & 3,706,666.02 & & 5,891.0 & 101.43 & & & & & \\ \hline \end{tabular}