Table 6: Shoe prices and estimated sales volumes From your earlier analysis of material and labor costs, and with new information provided, you know the following about the original option: Material costs total $50.15 per shoe. Labor costs total $7.33 per shoe. Overhead costs are calculated as 25% of the direct labor costs. Selling expense is calculated at $10 per pair of shoes, or $5.00 per shoe. Organizing the Workbook With the necessary information in hand, how should you organize the new budget workbook? Consider the Options worksheet created earlier (Figure 16). In this worksheet, all the inputs and outputs for the three design options are displayed on the same worksheet. This layout is easy to read and implement because there are a limited number of options and relatively simple calculations. But what if each design option had a completely different set of data inputs, or the values of the inputs varied from option to option? In such a case, it might be easier to create separate worksheets for each design option, or put all the inputs together on one worksheet and the outputs together on another. For the budget workbook, you need to organize data for four quarters, with each quarter containing data for the three different pricing alternatives. If you placed all the data on one worksheet, it would contain 12 separate sets of calculations and inputs. Although this is possible, such an organization would make the worksheet cumbersome to manipulate. Figure 18 illustrates and compares several possible ways to organize the budget workbook. Note the following: The first organization places all the inputs on one worksheet and all the outputs on another. This is a simple organization but, again, might be difficult to work with and could cause confusion, because it would be difficult to ascertain which set of inputs are associated with a particular output. The second organization is by quarter. The inputs and outputs are placed on a single worksheet, with each worksheet containing all three pricing alternatives for a specific quarter. The third organization is by pricing alternative. The inputs and outputs are placed on a single worksheet, with each worksheet containing all four quarters for a specific pricing alternative. Figure 18: Possible workbook designs
Table 6: Shoe prices and estimated sales volumes From your earlier analysis of material and labor costs, and with new information provided, you know the following about the original option: Material costs total $50.15 per shoe. Labor costs total $7.33 per shoe. Overhead costs are calculated as 25% of the direct labor costs. Selling expense is calculated at $10 per pair of shoes, or $5.00 per shoe. Organizing the Workbook With the necessary information in hand, how should you organize the new budget workbook? Consider the Options worksheet created earlier (Figure 16). In this worksheet, all the inputs and outputs for the three design options are displayed on the same worksheet. This layout is easy to read and implement because there are a limited number of options and relatively simple calculations. But what if each design option had a completely different set of data inputs, or the values of the inputs varied from option to option? In such a case, it might be easier to create separate worksheets for each design option, or put all the inputs together on one worksheet and the outputs together on another. For the budget workbook, you need to organize data for four quarters, with each quarter containing data for the three different pricing alternatives. If you placed all the data on one worksheet, it would contain 12 separate sets of calculations and inputs. Although this is possible, such an organization would make the worksheet cumbersome to manipulate. Figure 18 illustrates and compares several possible ways to organize the budget workbook. Note the following: The first organization places all the inputs on one worksheet and all the outputs on another. This is a simple organization but, again, might be difficult to work with and could cause confusion, because it would be difficult to ascertain which set of inputs are associated with a particular output. The second organization is by quarter. The inputs and outputs are placed on a single worksheet, with each worksheet containing all three pricing alternatives for a specific quarter. The third organization is by pricing alternative. The inputs and outputs are placed on a single worksheet, with each worksheet containing all four quarters for a specific pricing alternative. Figure 18: Possible workbook designs
Excel%20Lab%201.docx