The first two images are the instruction, just do whatever is being asked in it, I am also uploading pages other than 201 (7th, 8th and 9th image) to help you better understand the context.

Go to page 201 of your e-text: Convert solution to Stylistic Furniture budget consisting of 9 budget schedules into a financial model using excel as discussed in class. Create a worksheet in excel file and name it "Data". Input all given data per the problem in this worksheet. Link each budget schedule to the data worksheet as | have done often in class, as per solution (1 schedule per work sheet) - see excel file in module 6 (Full Operating Budget - Stylistic Furniture). Make certain all schedules contain formulas and is linked to the given data as appropriate. Include a Gross Margin % in the Operating income Statement - schedule 9. Note the overhead schedule (schedule 5) does not need to be set up and linked to data sheet. | will review in class. Instead just input total overhead ($12 million) in Cost of Goods Sold schedule (8). Once every schedule reflects the solution and is linked with formulas, do a save as: name it "Stylistic Budget - Base Case" Next save the base case file as "Stylistic Budget - Scenario 1" and answer the following question: Perform a what if scenario with this file based on CEQ's statement: | don't care how you do it, | want the budgeted revenues to be $40 million (as opposed to $38 million per solution). What is the revised Gross Margin dollars and %. Comment on the change in the Operating Income Schedule 9. How did the change impact margins, do you agree with this scenario? Once Scenario 1 is completed, save as "Stylistic Furniture - Scenario 2" and answer the following question: What if you decide inflation may be a real factor (more than you originally anticipated), in this scenario assume 15% increase in material costs and 10% increase in labor costs. Revenue is assumed to be the original budgeted $38 million. Now what is Gross Margin dollars nd percentage. Comment on the change in the operating income statement - again do you agree. Should price remain the same, explain your rationale? X . Note, you can receive partial credit. In order to receive full 15 points, every schedule must equal the solution exactly including format with your thoughtful comments. If you set this up correctly, I believe the time spent on this assignment will not be as much as you anticipate.6.3: Developing an Operating Budget Steps in Preparing an Operating Budget Consider Stylistic Furniture, a company that makes two types of granite-top coffee tables: Casual and Deluxe. It is late 2019 and Stylistic's CEO, Rex Jordan, is very concerned about how to respond to the board of directors' mandate to increase profits by 10% in the coming year. Jordan goes through the five-step decision-making process introduced in Chapter 1. 1. IDENTIFY THE PROBLEM AND UNCERTAINTIES. The problem is to identify a strategy and to build a budget to achieve 10% profit growth. There are several uncertainties. Can Stylistic dramatically increase the sales of its more profitable Deluxe tables? What price pressures will Stylistic face? Will cost of materials increase? Can Stylistic reduce costs through efficiency improvements? 2. OBTAIN INFORMATION. Stylistic's managers gather information about sales of tables in the current year. Sales of Deluxe tables have been stronger than expected. A key competitor in Stylistic's Casual tables' line has had quality problems that are likely to persist through 2020. The prices of direct materials have increased slightly during 2019 compared to 2018. 3. MAKE PREDICTIONS ABOUT THE FUTURE. Stylistic's managers feel confident that, with a little more marketing, they will be able to grow the Deluxe tables' business in 2020 and even increase prices moderately relative to 2019. They also do not expect significant price pressures on Casual tables during the year because of the quality problems faced by aCasual tables during the year because of the quality problems faced by a key competitor. The purchasing manager anticipates that prices of direct materials in 2020 will remain unchanged from 2019. The manufacturing manager believes that manufacturing costs of tables will be the same as in 2019 with efficiency improvements offsetting price increases in other inputs. Achieving these efficiency improvements is important if Stylistic is to maintain its 12% operating margin (that is, operating income : sales = 12%) and to grow sales and operating income. 4. MAKE DECISIONS BY CHOOSING AMONG ALTERNATIVES. Jordan and his managers feel confident about their strategy to increase the sales of Deluxe tables. This decision has some risks, but is the best option available for Stylistic to increase its profits by 10%. 5. IMPLEMENT THE DECISION, EVALUATE PERFORMANCE, AND LEARN. As we will discuss in Chapters 7 and 8, managers compare actual performance to predicted performance to learn why things turned out the way they did and how to do better. Stylistic's managers would want to know: Were their predictions about the prices of Casual and Deluxe tables correct? Did prices of inputs increase more or less than anticipated? Did efficiency improvements occur? Such learning would help when building budgets in subsequent years.Stylistic's managers begin work on the 2020 budget. Exhibit 6-2 shows various parts of the master budget, comprising financial projections for Stylistic's operating and financial budgets for 2020. The light, medium, and dark green boxes in Exhibit 6-2 show the operating budget, consisting of the budgeted income statement and its supporting budget schedules. Exhibit 6-2 Overview of the Master Budget for Stylistic Furniture Revenues Budget (Schedule 1) Ending Production Inventory Budget Budget (Schedules 2 & 6) (Schedule 2) Direct Direct Manufacturing Material Manufacturing Overhead Costs Budget Labor Costs Budget Costs Budget Schedule 3) (Schedule 4) (Schedule 5) Cost of Goods Sold Budget (Schedule 7) OPERATING R&D/ Design BUDGET Costs Budget (Schedule 8)OPERATING R&D/ Design BUDGET Costs Budget (Schedule 8) Marketing Costs Budget (Schedule 8) Distribution Costs Budget Schedule 8) Budgeted Income Statement (Exhibits 6-3 & 6-7) Capital FINANCIAL Cash Budgeted Balance Budgeted Expenditures BUDGET Budget Budget Sheet Statement of Cash Flows (Exhibit 6-6) (Exhibit 6-6) Exhibit 6-8 The light green revenues budget box is the starting point of the operating budget. The supporting schedules-shown in medium green-quantify the budgets for various business functions of the value chain, from research and development to distribution costs. These schedules build up to the budgeted income statement- the key summary statement in the operating budget- shown in dark green. 200 The orange and purple boxes in the exhibit are the financial budget, which is the part of the master budget made up of the capital expenditures budget, the200 The orange and purple boxes in the exhibit are the financial budget, which is the part of the master budget made up of the capital expenditures budget, the cash budget, the budgeted balance sheet, and the budgeted statement of cash flows. A financial budget focuses on how operations and planned capital outlays affect cash- shown in orange. Management accountants use the cash budget and the budgeted income statement to prepare two other summary financial statements- the budgeted balance sheet and the budgeted statement of cash flows, which are shown in purple. 201 The steps for preparing an operating budget for Stylistic Furniture for 2020 follow, using Exhibit 6-2 as a guide. The chapter appendix presents Stylistic's cash budget and budgeted balance sheet. The following are details of Stylistic's operations: . Stylistic sells two models of granite-top coffee tables: Casual and Deluxe. Revenue unrelated to sales, such as interest income, is zero. . Work-in-process inventory is negligible and is ignored. . Direct materials inventory and finished-goods inventory are costed using the first-in, first-out (FIFO) method. The unit costs of direct materials purchased and unit costs of finished-goods sold remain unchanged throughout each budget year, but can change from year to year. . There are two types of direct materials: red oak and granite slabs. The direct material costs are variable with respect to units of output-coffee tables.. Direct manufacturing labor is hired on an hourly basis; no overtime is worked. . Manufacturing overhead costs have two cost drivers - direct manufacturing labor-hours and setup labor-hours, and two manufacturing overhead cost pools - manufacturing operations overhead and machine setup overhead. . Direct manufacturing labor-hours are the cost driver for the variable portion of manufacturing operations overhead costs. The fixed component of manufacturing operations overhead costs is tied to the manufacturing capacity of 300,000 direct manufacturing labor-hours Stylistic has planned for 2020. . Setup labor-hours are the cost driver for the variable portion of machine setup overhead costs. The fixed component of machine setup overhead costs is tied to the setup capacity of 15,000 setup labor-hours Stylistic has planned for 2020. . For computing inventoriable costs of finished goods, Stylistic allocates all (variable and fixed) manufacturing operations overhead costs using direct manufacturing labor-hours and machine setup overhead costs using setup labor-hours. . Operating (nonmanufacturing) costs consist of product design, marketing, and distribution costs. All product design costs are fixed costs for 2020. The variable component of marketing costs is the 6.5% sales commission on revenues paid to salespeople. The variable portion of distribution costs varies with cubic feet of tables sold and shipped.The following data are available for the 2020 budget: Direct materials Red oak $ 7 per board foot (b.f.) (same as in 2019) Granite $10 per square foot (sq. ft.) (same as in 2019) Direct manufacturing labor $20 per hour Content of Each Product Unit Casual Granite Table Deluxe Granite Table Red oak 12 board feet 12 board feet Granite 6 square feet 8 square feet Direct manufacturing labor 4 hours 6 hours Product Casual Granite Table Deluxe Granite Table Expected sales in units 50,000 10,000 Selling price $ 600 $ 800 Target ending inventory in units 11,000 500 Beginning inventory in units 1,000 500 Beginning inventory in dollars $384,000 $262,000 Direct Materials Red Oak Granite Beginning inventory 70,000 b.f. 60,000 sq. ft. Target ending inventory 80,000 b.f. 20,000 sq. ft. Stylistic budgets costs to support the revenues budget, taking into account efficiency improvements it expects to make in 2020. Recall from Step 3 of the decision-making process that efficiency improvements are critical to offset the anticipated increases in the cost of inputs and to maintain Stylistic's 12% operating margin. 202 The budget manual contains instructions and information for preparing budgets. Although the details differ among companies, the following basic202 The budget manual contains instructions and information for preparing budgets. Although the details differ among companies, the following basic steps are common for developing the operating budget of a manufacturing company. Beginning with the revenues budget, each of the other budgets follows step by step in logical fashion. As you go through the details for preparing a budget, think about two things: (1) the information needed to prepare each budget and (2) the actions managers plan to take to improve performance. Step 1: PREPARE THE REVENUES BUDGET. Stylistic's managers plan to sell two models of granite-top coffee tables, Casual and Deluxe, in 2020. The revenues budget describes the quantities and prices for each table. A revenues budget is the usual starting point for the operating budget. Why? Because the forecasted level of unit sales or revenues has a major impact on the production capacity and the inventory levels planned for 2020-and, therefore, manufacturing and operating (nonmanufacturing) costs. Many factors affect the sales forecast, including the sales volume in recent periods, general economic and industry conditions, market research studies, pricing policies, advertising and sales promotions, competition, and regulatory policies. The key to Stylistic achieving its goal of growing its profits by 10% is to grow its sales of Deluxe tables from 8,000 tables in 2019 to 10,000 tables in 2020. Managers use customer relationship management (CRM) or salesManagers use customer relationship management (CRM) or sales management systems to gather information. Statistical, machine learning, and data-analytic models, such as regression, trend analysis, decision-tree, and gradient-boosting, use indicators of economic activity and past sales data to forecast future sales. The models improve and learn from past experiences in predicting sales. Sales managers and sales representatives debate how best to position, price, and promote Casual and Deluxe tables relative to competitors' products. Together with top management, they consider various actions, such as adding product features, digital advertising, and changing sales incentives, to increase revenues, taking into account related costs. The sales forecast represents the output of models, collective experience, and judgment of managers. Top managers decide on the budgeted sales quantities and prices to determine the revenues budget of $38,000,000 shown in Schedule 1. These are challenging targets designed to motivate the organization to achieve higher levels of performance. Schedule 1: Revenues Budget for the Year Ending December 31, 2020 Units Selling Price Total Revenues Casual 50,000 $600 $30,000,000 Deluxe 10,000 800 8,000,000 Total $38,000,000 Revenues budgets are usually based on market conditions and expected demand because these factors drive revenues. Occasionally, other factors, such as available production capacity (being less than demand)management, they consider various actions, such as adding product features, digital advertising, and changing sales incentives, to increase revenues, taking into account related costs. The sales forecast represents the output of models, collective experience, and judgment of managers. Top managers decide on the budgeted sales quantities and prices to determine the revenues budget of $38,000,000 shown in Schedule 1. These are challenging targets designed to motivate the organization to achieve higher levels of performance. Schedule 1: Revenues Budget for the Year Ending December 31, 2020 Units Selling Price Total Revenues Casual 50,000 $600 $30,000,000 Deluxe 10,000 800 8,000,000 Total $38,000,000 Revenues budgets are usually based on market conditions and expected demand because these factors drive revenues. Occasionally, other factors, such as available production capacity (being less than demand) or a manufacturing input in short supply, limit revenues. In these cases, managers base the revenues budget on the maximum units that can be produced