Large Business decision model Problem 32. (4 marks for question, 6 marks for Excel file) Security code: 12GGI A small business owner wants to develop an Excel decision model to calculate how much business income tax she should pay each quarter. Her business has two revenue sources: wholesale revenue and retail revenue. The following probability distributions describe the revenue from each source. Annual wholesale revenue $100,000 $150,000 $200,000 $250,000 Probability 0.25 0.25 0.25 0.25 Annual retail revenue $800,000 $1,000,000 $1,100,000 Probability 0.5 0.3 0.2 Wholesale revenue can be reduced by up to 35-percent for wholesale business expenses. Retail revenue can be reduced by up to 55-percent for retail business expenses. The remaining revenue can be reduced by up to $110,000 for personal expenses. The business income tax rate is 25-percent on the first $300,000 of revenue after deductions and 45-percent on revenue after deductions over $300,000. a. Open a new Excel workbook and worksheet. In cell A1 type the security code at the top of this problem. In cell A2 type your first name and last name. In cell A3 type your student number (e.g. 001234567). In cell A4 type your McMaster e-mail (e.g. chenga4@mcmaster.ca). At the bottom of the worksheet name your worksheet Problem 32'. If any of these items are missing or incorrect you will receive a mark of zero for the Excel file. b. Construct an Excel business decision model. Enter the variables in the order they occur in the question. The decision variables are the business owner's estimates of wholesale revenue and retail revenue. The output variables include total revenue, total deductions, total tax, quarterly tax payment, and revenue remaining after reductions and taxes. Your model must be very neat and follow exactly the format learned in class. Show all dollar values without any decimal places; e.g. $500,000 not $500,000.25. c. Enter the following decision variable values: wholesale revenue = $150,000, retail revenue = $1,000,000. Your Excel decision model should automatically calculate and show the corresponding values for the output variables. d. Construct a two-way Data Table. Wholesale revenue is the row variable, retail revenue is the column variable, and revenue remaining after reductions and taxes is the output variable. The values of wholesale and retail revenue are those in the probability distributions above. Your data table should also calculate the expected revenue remaining after reductions and taxes when the retail revenue is $1,000,000. Colour this cell red. e (6 marks). Save your Excel file (use filename: Lastname, firstname - Excel problem 32) and place the file in the dropbox: Avenue >Assessments > Assignments > Exam 1 - Practice Dropbox If this is done incorrectly you will receive a mark of zero for the Excel file. (On the exam the dropbox will have a different name; something along the lines of Exam 1 - Excel problem'.) Large Business decision model Problem 32. (4 marks for question, 6 marks for Excel file) Security code: 12GGI A small business owner wants to develop an Excel decision model to calculate how much business income tax she should pay each quarter. Her business has two revenue sources: wholesale revenue and retail revenue. The following probability distributions describe the revenue from each source. Annual wholesale revenue $100,000 $150,000 $200,000 $250,000 Probability 0.25 0.25 0.25 0.25 Annual retail revenue $800,000 $1,000,000 $1,100,000 Probability 0.5 0.3 0.2 Wholesale revenue can be reduced by up to 35-percent for wholesale business expenses. Retail revenue can be reduced by up to 55-percent for retail business expenses. The remaining revenue can be reduced by up to $110,000 for personal expenses. The business income tax rate is 25-percent on the first $300,000 of revenue after deductions and 45-percent on revenue after deductions over $300,000. a. Open a new Excel workbook and worksheet. In cell A1 type the security code at the top of this problem. In cell A2 type your first name and last name. In cell A3 type your student number (e.g. 001234567). In cell A4 type your McMaster e-mail (e.g. chenga4@mcmaster.ca). At the bottom of the worksheet name your worksheet Problem 32'. If any of these items are missing or incorrect you will receive a mark of zero for the Excel file. b. Construct an Excel business decision model. Enter the variables in the order they occur in the question. The decision variables are the business owner's estimates of wholesale revenue and retail revenue. The output variables include total revenue, total deductions, total tax, quarterly tax payment, and revenue remaining after reductions and taxes. Your model must be very neat and follow exactly the format learned in class. Show all dollar values without any decimal places; e.g. $500,000 not $500,000.25. c. Enter the following decision variable values: wholesale revenue = $150,000, retail revenue = $1,000,000. Your Excel decision model should automatically calculate and show the corresponding values for the output variables. d. Construct a two-way Data Table. Wholesale revenue is the row variable, retail revenue is the column variable, and revenue remaining after reductions and taxes is the output variable. The values of wholesale and retail revenue are those in the probability distributions above. Your data table should also calculate the expected revenue remaining after reductions and taxes when the retail revenue is $1,000,000. Colour this cell red. e (6 marks). Save your Excel file (use filename: Lastname, firstname - Excel problem 32) and place the file in the dropbox: Avenue >Assessments > Assignments > Exam 1 - Practice Dropbox If this is done incorrectly you will receive a mark of zero for the Excel file. (On the exam the dropbox will have a different name; something along the lines of Exam 1 - Excel problem'.)