PLEASE SHOW EXCEL FORMULAS!!
PLEASE SHOW EXCEL FORMULAS!!
Mesa Ski Shop is a merchandising company that sells skis in Grand Junction. You, the manager, are tasked with doing a sensitivity analysis for 3 different scenarios in the upcoming year. Create a sales budget for the 3 scenarios. Scenario 1 Open from Jan-Apr and Oct-Dec Expected Ski Sales in Units: 2,500 Expected Ski Cost: $375 Expected Mark-Up: 50% Scenario 2 Open from Jan-Dec Expected Ski Sales in Units: 4,000 Expected Ski Cost: $375 Expected Mark-Up: 40% Scenario 3 Open from Jan-Feb and Nov-Dec Expected Ski Sales in Units: 2,100 Expected Ski Cost: $375 Expected Mark-Up: 50% 2022 Sales Budget 2022 (51) 2022 Sales Budget 2022 (S2) 2022 Sales Budget 2022 (53) Sales Budget Budgeted units to be sold Sales price per unit Total Sales Mesa Ski Shop wants to keep 200 units in inventory at the end of 2022 because they're planning on having a closeout sale at the beginning of 2023. Create a COGS budget. 2022 Cost of Goods Sold Budget 2022 (51) 2022 Cost of Goods Sold Budget 2022 (S2) 2022 Cost of Goods Sold Budget 2022 (53) $ Cost of Goods Sold Budget Beginning Inventory (Zero) Purchases Ending Inventory Total budgeted cost of goods sold Mesa Ski Shop's variable supplies expense per year is $25.00 per unit. The fixed selling and administrative expenses are included in each scenario detail. Create a selling and administrative expense budget. Scenario 1 Salaries Expense: $140,000 Advertising Fees: $20,000 Depreciation Expense: $12,500 Utilities Expense: $2,000/month open Scenario 2 Salaries Expense: $200,000 Advertising Fees: $40,000 Depreciation Expense: $12,500 Utilities Expense: $2,000/month open Scenario 3 Salaries Expense: $80,000 Advertising Fees: $15,000 Depreciation Expense: $12,500 Utilities Expense: $2,000/month open 2022 SG&A Expense Budget 2022 (51) 2022 SG&A Expense Budget 2022 (52) 2022 SG&A Expense Budget 2022 (53) Selling and Administrative Expense Budget Salaries expense Advertising/Marketing expense Depreciation expense Supplies expense Total budgeted S&A expense Budgeted Net Operating Income/(Loss) As the manager, you need to present the most profitable scenario for the company in 2022. What scenario will you recommend? Mesa Ski Shop is a merchandising company that sells skis in Grand Junction. You, the manager, are tasked with doing a sensitivity analysis for 3 different scenarios in the upcoming year. Create a sales budget for the 3 scenarios. Scenario 1 Open from Jan-Apr and Oct-Dec Expected Ski Sales in Units: 2,500 Expected Ski Cost: $375 Expected Mark-Up: 50% Scenario 2 Open from Jan-Dec Expected Ski Sales in Units: 4,000 Expected Ski Cost: $375 Expected Mark-Up: 40% Scenario 3 Open from Jan-Feb and Nov-Dec Expected Ski Sales in Units: 2,100 Expected Ski Cost: $375 Expected Mark-Up: 50% 2022 Sales Budget 2022 (51) 2022 Sales Budget 2022 (S2) 2022 Sales Budget 2022 (53) Sales Budget Budgeted units to be sold Sales price per unit Total Sales Mesa Ski Shop wants to keep 200 units in inventory at the end of 2022 because they're planning on having a closeout sale at the beginning of 2023. Create a COGS budget. 2022 Cost of Goods Sold Budget 2022 (51) 2022 Cost of Goods Sold Budget 2022 (S2) 2022 Cost of Goods Sold Budget 2022 (53) $ Cost of Goods Sold Budget Beginning Inventory (Zero) Purchases Ending Inventory Total budgeted cost of goods sold Mesa Ski Shop's variable supplies expense per year is $25.00 per unit. The fixed selling and administrative expenses are included in each scenario detail. Create a selling and administrative expense budget. Scenario 1 Salaries Expense: $140,000 Advertising Fees: $20,000 Depreciation Expense: $12,500 Utilities Expense: $2,000/month open Scenario 2 Salaries Expense: $200,000 Advertising Fees: $40,000 Depreciation Expense: $12,500 Utilities Expense: $2,000/month open Scenario 3 Salaries Expense: $80,000 Advertising Fees: $15,000 Depreciation Expense: $12,500 Utilities Expense: $2,000/month open 2022 SG&A Expense Budget 2022 (51) 2022 SG&A Expense Budget 2022 (52) 2022 SG&A Expense Budget 2022 (53) Selling and Administrative Expense Budget Salaries expense Advertising/Marketing expense Depreciation expense Supplies expense Total budgeted S&A expense Budgeted Net Operating Income/(Loss) As the manager, you need to present the most profitable scenario for the company in 2022. What scenario will you recommend
