Table 4. 4 Formulas for Financial Plan \begin{tabular}{|l|l|l|} \multicolumn{1}{|c|}{ Purpose } & \multicolumn{1}{c|}{ Formula } & Location \\ \hline BudgetedProfitDollars & Budgeted Sales x Profit Percent & F4:F7 \\ \hline Budgeted Sales & Sales Last Year x (1+ Sales Growth) & C4:C7 \\ \hline Total Profit Growth & (Total Budgeted Profit Dollars Total Budgeted Sales) & E8 \\ \hline Total Sales Growth & (TotalBudgetedSales-TotalSalesLastYear)TotalSalesLastYear & D8 \\ \hline Assignment & & \\ \hline \end{tabular} 1. Use the Show Formulas command to audit the formulas on the worksheet. Compare the formulas on the worksheet to the formulas shown on Table 4.4. Record your observations on the AnswerSheet worksheet. 2. The assistant manager intends to use the Financial Plan worksheet to create a few scenarios for the budgeted sales and profit dollars. Change a few values in the Profit Percent column and record your observations on the AnswerSheet worksheet. 3. Look at each value in the Totals row (row 8) on the Financial Plan worksheet. Are there any values that do not make sense? Record your observations on the Answersheet worksheet. 4. Given the information provided for this exercise, make any necessary corrections to improve the integrity of the Financial Plan worksheet. Chapter 4IC Exercise 3.xIsx Difficulty: Level 3 The purpose of this exercise is to conduct a data audit to evaluate the integrity of the mathematical outputs that have been added to a worksheet. Read the scenario below, then open the Excel workbook related to this exercise. Use the AnswerSheet worksheet for any written responses required for this exercise. Scenario You are the manager of a large do-it-yourself hardware store that is part of a national retail chain. Your assistant manager has constructed a financial plan by product category for the upcoming year. The Financial Plan worksheet contains several mathematical outputs used to calculate the expected sales and profit for the company. The following is a list of key elements and calculations used on this worksheet: - Cells shaded in yellow are intended for data entry values. For example, last year sales results in column B are typed into the cells. The expected growth rates in column D and profit percentages in column E are also typed into the cells. These values fluctuate from year to year, and the assistant manager intends to create a few scenarios for the budget by changing the growth rates and expected profit percentages for each product category. - Table 4.4 contains a list of the formulas that should have been used to produce the outputs on the Financial Plan worksheet. Table 4. 4 Formulas for Financial Plan \begin{tabular}{|l|l|l|} \multicolumn{1}{|c|}{ Purpose } & \multicolumn{1}{c|}{ Formula } & Location \\ \hline BudgetedProfitDollars & Budgeted Sales x Profit Percent & F4:F7 \\ \hline Budgeted Sales & Sales Last Year x (1+ Sales Growth) & C4:C7 \\ \hline Total Profit Growth & (Total Budgeted Profit Dollars Total Budgeted Sales) & E8 \\ \hline Total Sales Growth & (TotalBudgetedSales-TotalSalesLastYear)TotalSalesLastYear & D8 \\ \hline Assignment & & \\ \hline \end{tabular} 1. Use the Show Formulas command to audit the formulas on the worksheet. Compare the formulas on the worksheet to the formulas shown on Table 4.4. Record your observations on the AnswerSheet worksheet. 2. The assistant manager intends to use the Financial Plan worksheet to create a few scenarios for the budgeted sales and profit dollars. Change a few values in the Profit Percent column and record your observations on the AnswerSheet worksheet. 3. Look at each value in the Totals row (row 8) on the Financial Plan worksheet. Are there any values that do not make sense? Record your observations on the Answersheet worksheet. 4. Given the information provided for this exercise, make any necessary corrections to improve the integrity of the Financial Plan worksheet. Chapter 4IC Exercise 3.xIsx Difficulty: Level 3 The purpose of this exercise is to conduct a data audit to evaluate the integrity of the mathematical outputs that have been added to a worksheet. Read the scenario below, then open the Excel workbook related to this exercise. Use the AnswerSheet worksheet for any written responses required for this exercise. Scenario You are the manager of a large do-it-yourself hardware store that is part of a national retail chain. Your assistant manager has constructed a financial plan by product category for the upcoming year. The Financial Plan worksheet contains several mathematical outputs used to calculate the expected sales and profit for the company. The following is a list of key elements and calculations used on this worksheet: - Cells shaded in yellow are intended for data entry values. For example, last year sales results in column B are typed into the cells. The expected growth rates in column D and profit percentages in column E are also typed into the cells. These values fluctuate from year to year, and the assistant manager intends to create a few scenarios for the budget by changing the growth rates and expected profit percentages for each product category. - Table 4.4 contains a list of the formulas that should have been used to produce the outputs on the Financial Plan worksheet