MBA-AC721 Budgeting for Managers Project Week 3 This week we will create the actual 2018 Income Statement. 1) Add another worksheet to your Excel workbook and then copy the entire worksheet you used for Week 2 Budgeted Income Statement for BRBS and label the new sheet Wk3 BRBS Actual. 2) Then add another column for the Actual 2018 Operations using the information below. (Hint: add a Computations worksheet to do your calculations and test the different scenarios for decision making. Then pull your information into the Wk3 BRBS Actual statement 3) Next you will compare budget to actual results, compute markup percentage & gross profit percentage for BRBS. To do this add a column for common-sized (vertical) analysis and a column for comparative budget to actual analysis. 4) Then make recommendations (see questions a-d) based on your analysis. 2018 Actual Sales: Online Bike C: 95,000 bikes; Bike A: 4,800 bikes. Total online 99,800 bikes Instore Bike C: 5,100 bikes; Bike A: 200 bikes. Total instore 5,300 bikes Bicycle Returns were 2% of online sales. Sales price: Bike C = $104; Bike A = $250 Purchase price from BRBC: Bike C = $52; Bike A = $125 Inventory: Beginning Inventory: 1,000 Bike C Ending Inventory: 1,000 Bike C; 50 Bike A Staffing changes: Added 1FTE in Purchasing and receiving Added one Saturday sales clerk to work 8 hours Utilities were as budgeted. Marketing costs increased by $25,000 and other costs increased by $75,000. BRBS is in the 29% tax bracket. After creating your 2018 Actual Income Statement, Answer these questions for 2019.You may need to test your assumptions in the income statement to see how it turns out: a) Only Bike C is budgeted to be sold at BRBS in store. Should Bike A and/or Bike B also be sold? In 2018, an experiment was conducted selling Bike A. Was this a good decision? Should it be continued for 2019? Should other adjustments to sales be considered? Explain. (Note: Bike B could be purchased from BRBC sister company for $70.) b) Budgeted profit margin is 17%, but the Board expects a 25% margin, so they can continue to give back to the community 10% of instore sales. How can the store increase their profit margin to 25% in 2019? c) To increase sales the store will need to increase store sales staff. Currently sales staff are paid $15 per hour and receive a bonus of 10% of instore sales (total divided among sales staff). How many additional sales staff will be needed to meet projected sales increases and profit margin for 2019? d) BRBS is considering starting two bicycle clubs one leisure and one competitive, in conjunction with the local YMCA. These would be community activities to increase physical activity and fitness, but would also bring awareness of BRBS and potentially increase sales. How would the costs of these activities be shown in the financial statements?