Homework answers / question archive / SHIRT MANUFACTURING EXAMPLE CHANGING CELLS NUMBER OF T-SHIRTS NUMBER OF BUTTON-DOWN SHIRTS CONSTANTS TAX RATE SELLING PRICE: T-SHIRT SELLING PRICE: BUTTON-DOWN SHIRT VARIABLE COST TO MAKE: T-SHIRT VARIABLE COST TO MAKE: BUTTON-DOWN SHIRT COTTON USAGE (LBS): T-SHIRT COTTON USAGE (LBS): BUTTON-DOWN SHIRT TOTAL COTTON AVAILABLE (LBS) BUTTONS PER T-SHIRT BUTTONS PER BUTTON-DOWN SHIRT TOTAL BUTTONS AVAILABLE CALCULATIONS RATIO OF NET INCOME TO TOTAL REVENUE COTTON USED: T-SHIRTS COTTON USED: BUTTON-DOWN SHIRTS COTTON USED: TOTAL BUTTONS USED: T-SHIRTS BUTTONS USED: BUTTON-DOWN SHIRTS BUTTONS USED: TOTAL RATIO OF BUTTON-DOWNS TO T-SHIRTS INCOME STATEMENT T-SHIRT REVENUE BUTTON-DOWN SHIRT REVENUE TOTAL REVENUE VARIABLE COSTS: T-SHIRTS VARIABLE COSTS: BUTTON-DOWNS TOTAL COSTS INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME AFTER TAXES 1 1 0

SHIRT MANUFACTURING EXAMPLE CHANGING CELLS NUMBER OF T-SHIRTS NUMBER OF BUTTON-DOWN SHIRTS CONSTANTS TAX RATE SELLING PRICE: T-SHIRT SELLING PRICE: BUTTON-DOWN SHIRT VARIABLE COST TO MAKE: T-SHIRT VARIABLE COST TO MAKE: BUTTON-DOWN SHIRT COTTON USAGE (LBS): T-SHIRT COTTON USAGE (LBS): BUTTON-DOWN SHIRT TOTAL COTTON AVAILABLE (LBS) BUTTONS PER T-SHIRT BUTTONS PER BUTTON-DOWN SHIRT TOTAL BUTTONS AVAILABLE CALCULATIONS RATIO OF NET INCOME TO TOTAL REVENUE COTTON USED: T-SHIRTS COTTON USED: BUTTON-DOWN SHIRTS COTTON USED: TOTAL BUTTONS USED: T-SHIRTS BUTTONS USED: BUTTON-DOWN SHIRTS BUTTONS USED: TOTAL RATIO OF BUTTON-DOWNS TO T-SHIRTS INCOME STATEMENT T-SHIRT REVENUE BUTTON-DOWN SHIRT REVENUE TOTAL REVENUE VARIABLE COSTS: T-SHIRTS VARIABLE COSTS: BUTTON-DOWNS TOTAL COSTS INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME AFTER TAXES 1 1 0

Computer Science

Share With

SHIRT MANUFACTURING EXAMPLE CHANGING CELLS NUMBER OF T-SHIRTS NUMBER OF BUTTON-DOWN SHIRTS CONSTANTS TAX RATE SELLING PRICE: T-SHIRT SELLING PRICE: BUTTON-DOWN SHIRT VARIABLE COST TO MAKE: T-SHIRT VARIABLE COST TO MAKE: BUTTON-DOWN SHIRT COTTON USAGE (LBS): T-SHIRT COTTON USAGE (LBS): BUTTON-DOWN SHIRT TOTAL COTTON AVAILABLE (LBS) BUTTONS PER T-SHIRT BUTTONS PER BUTTON-DOWN SHIRT TOTAL BUTTONS AVAILABLE CALCULATIONS RATIO OF NET INCOME TO TOTAL REVENUE COTTON USED: T-SHIRTS COTTON USED: BUTTON-DOWN SHIRTS COTTON USED: TOTAL BUTTONS USED: T-SHIRTS BUTTONS USED: BUTTON-DOWN SHIRTS BUTTONS USED: TOTAL RATIO OF BUTTON-DOWNS TO T-SHIRTS INCOME STATEMENT T-SHIRT REVENUE BUTTON-DOWN SHIRT REVENUE TOTAL REVENUE VARIABLE COSTS: T-SHIRTS VARIABLE COSTS: BUTTON-DOWNS TOTAL COSTS INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME AFTER TAXES 1 1 0.28 $8.00 $36.00 $2.50 $14.00 1.50 2.50 13,000,000 3.00 12.00 110,000,000 IS320 Advanced Business Applications Spring 2021 Excel Macro Assignment Reporting Template. a. Open an Excel workbook, save as follows: File Name: MDVolunteers Save As Type: Excel Macro-Enabled Workbook b. Sheet tabs should be changed to the months of the year, and one tab to say “Year Totals”. There should be 13 tabs in all. You may want to practice creating the template below before recording the macro. c. On the January sheet, click cell B3. To record a macro, click View/Macros/Record Macro Record a macro named “setup” with short-cut key “ctrl+shift+T” and create the following template. Be very careful; every keystroke will be recorded!!! You may select different colors, font styles, etc... , but the text information should be as shown. Totals are the sum of the row or column (Use the sum function). Don’t forget to “stop recording” when you are finished with the design. View/Macros/Stop Recording d. Test your work by switching to the February tab, click B3 and run your macro. (View/Macros/View Macros/Run) Then manually change the month on the spreadsheet to February. e. Edit program code, view/macros/view macros/edit, and examine VBA code. Locate line of code to edit and change month to March. Close code editor. f. On the March tab cell B3, run the macro. Check for accuracy. Don’t forget to Save excel workbook; save as type “Excel Macro Enabled Workbook". Keep for next week. Building a Decision Support System Using Microsoft Excel Solver 145 edule should s? This ferent ? B 16 CONSTANTS TAX RATE 8 9 constants Section some of your company's operations follows the figure. J/xir spreadsheet should contain the constants shown in Figure D-21. A discussion of the constant cells and SELLING PRICE: T-SHIRT 110 VARIABLE COST TO MAKE: T-SHIRT SELLING PRICE: BUTTON-DOWN SHIRT | 12 COTTON USAGE (LBS): T-SHIRT VARIABLE COST TO MAKE: BUTTON-DOWN SHIRT 13 COTTON USAGE (LBS): BUTTON-DOWN SHIRT 15 BUTTONS PER T-SHIRT TOTAL COTTON AVAILABLE (LBS) 17 TOTAL BUTTONS AVAILABLE 16 BUTTONS PER BUTTON-DOWN SHIRT FIGURE D-21 Shirt manufacturing constants 7 0.28 $8.00 $36.00 $2.50 $14.00 (11 nnot. You vers with 14 1.50 2.50 13,000,000 3.00 12.00 110,000,000 igher ib. TAX RATE—The rate is .28 on income before taxes. Note that no taxes are paid on losses. SELLING PRICE—You sell polo-style T-shirts for $8 and button-down shirts for $36. VARIABLE COST TO MAKE-It costs $2.50 to make a T-shirt and $14 to make a button-down shirt. These variable costs are for machine operator labor, cloth, buttons, and so on. COTTON USAGE—Each polo T-shirt uses 1.5 pounds of cotton fabric. Each button-down shirt uses 2.5 pounds of cotton fabric. TOTAL COTTON AVAILABLE—You have 13 million pounds of cotton on hand to make all of the T-shirts and button-down shirts. BUTTONS—Each polo T-shirt has three buttons. By contrast, each button-down shirt has one button on each collar tip, eight buttons down the front, and one button on each cuff, for a total of 12 buttons. TOTAL BUTTONS AVAILABLE—You have 110 million buttons on hand to be used in making the shirts. ess Assume Calculations Section Calculations and related business constraints are discussed next. Your spreadsheet should contain the calculations shown in Figure D-22. se the B A 19 CALCULATIONS 20 RATIO OF NET INCOME TO TOTAL REVENUE 21 COTTON USED: T-SHIRTS 22 COTTON USED: BUTTON-DOWN SHIRTS 23 COTTON USED: TOTAL no 24 BUTTONS USED: T-SHIRTS 25 BUTTONS USED: BUTTON-DOWN SHIRTS 26 BUTTONS USED: TOTAL RATIO OF BUTTON-DOWNS TO T-SHIRTS RATIO OF NET INCOME TO TOTAL REVENUE-The minimum return on sales is 0.20. This FIGURE D-22 Shirt manufacturing calculations ratio is net income after taxes divided by total revenue. foto 146 Tutorial D of each resource must be calculated and then used in constraints. COTTON USED/BUTTONS USED—You have a limited amount of cotton and buttons. The usage and at least 2 million button-down shirts. You want to be known as a balanced shirt maker, SO RATIO OF BUTTON-DOWNS TO T-SHIRTS—You think you must make at least 2 million T-shirts duced 9 million button-down shirts and 2 million T-shirts, the ratio would be too high. you think the ratio of button-downs to T-shirts should be no greater than 4:1. Thus, if you pro- Income Statement Section Your spreadsheet should have the Income Statement section shown in Figure D-23. B ? 29 INCOME STATEMENT 30 T-SHIRT REVENUE 31 BUTTON-DOWN SHIRT REVENUE 32 TOTAL REVENUE 33 VARIABLE COSTS: T-SHIRTS 34 VARIABLE COSTS: BUTTON-DOWNS 35 TOTAL COSTS 36 INCOME BEFORE TAXES 37 INCOME TAX EXPENSE 38 NET INCOME AFTER TAXES FIGURE D-23 Line items in the shirt manufacturing income statement The meaning of the line items should be clear from the sports example shown earlier in this tutorial. Solver's objective is to maximize net income after taxes. You could use the table shown in Figure D-24 to hand-write the constraints before entering them into Solver Expression Fill in the Excel Expression Net income to revenue >= Ratio of button-downs to T-shirts Minimum T-shirts >= A V V A Minimum button-downs Usage of buttons Usage of cotton