decision analysis

2021 Spring MGT212 Decision Analysis for Entrepreneurs This is an individual assignment. Please submit one Excel document with your answers to the problems below. DUE DATE: May 6th, before 18h30 (6:30pm) Problem 1 The following table provides historical quarterly sales data for IOU Inc for the last 3 years. Year Quarter Sales in $1000 2018 1 109 2018 2 123 2018 3 121 2018 4 146 2019 1 113 2019 2 127 2019 3 128 2019 4 150 2020 1 117 2020 2 133 2020 3 132 2020 4 164 Required: 1) Forecast sales for 2021 using the decomposition method. 2) Provide a graph showing actual sales and your forecast (including what the forecast would have been for 2018-2020 and also your forecast for 2021). Make sure that the graph has a title, horizontal axis title, vertical axis title, and a legend. Make sure to use a line graph. 3) Comment on the accuracy of this model. Problem 2 Use the attached “salaries2.xls” file for this problem. Required: 1) Using the Pivot Table in Excel, create a table showing the distribution of salaries within the company while grouping the salaries by salary range. (Frequency distribution table as we did in class) Your first range should start with 25000 and the last range should end with 64999. The width should be 5000. The table should include the count of the number of salaries included within each class. Comment on the distribution of all salaries within the company. For additional insight, also provide a histogram depicting the distribution of salaries within the company. (HINT: Use a 2-d column chart, make the gaps between the bars equal to 0, label your chart and axis, make sure that the x-axis labels correspond to midpoints of your classes (ex. 17500 for the first class)). 2) Using the Pivot Table in Excel, crease a table showing average salaries for every title for females, males and totals. Comment on the differences in salaries between different titles and males and females, as a whole and between titles. (HINT: category

corresponds to seniority level; think how this can be taken into account for your comments and feel free to add additional graphs) Problem 3 Widgets Inc. is a start-up and would like to enter the luxury widgets market. The company plans to sell 2 product lines: Widget 1.0 and Widget 2.0. It is expected that unit sales of Widget 2.0 will be 2 times the sales of Widget 1.0. Selling price of Widget 1.0 will be set to $10 each. Widget 2.0 will be sold for $15 each. Cost of goods sold for both is equal to 25% of their selling price. Other expenses include a fixed rent of $1500 per month and salaries and wages, which will equal to $2000 per month of fixed salaries and $15 per hour of labor (count 10 minutes per every unit sold of both product lines). Tax rate is equal to 35%. Required: 1) Develop an Excel table to model a pro-forma income statement for the first year for Widgets Inc. using a single-step income statement format like follows:

Revenue Sales of Widget 1.0 Sales of Widget 2.0 Total revenue Expenses Cost of goods sold Rent Salaries Wages Total expenses Profit before taxes Taxes Profit after taxes

Set aside two cells at the top of your table for the estimated quantity sold for both product lines (HINT: only one of the cells should be an input cell, while the other should contain a formula based on the relationship between the sales of two products). Make sure that taxes are equal to zero if profit before taxes is negative. 2) Find the break-even point in unit sales for both product lines and in annual $ revenue using the Goal Seek function in Excel. Write down your answer in separate cells. Include a screenshot showing the filled in Goal Seek function window. 3) Based on your table from part 1), develop a table to compute Total expenses and Profit after taxes for different levels of Total revenue, starting with a total revenue of 0 and going all the way up to $250000 of revenue in increments of $25000. 4) Based on your table from 3), develop a Cost-Volume-Profit chart with Total revenue on x axis, showing 3 lines: Total revenue, Total expenses and Profit after taxes. Make sure that your chart has a title, axis are labeled and legend clearly shows which line corresponds to what.