Excel File Edit View Insert A B U Home Insert Draw Page Layout MS Sans Seril 10 B T V Format Tools Data Window Help CJ7471(1).xls (Compatibility Mode] Formulas Data Review View AA 0 % , Conditional Formatting A Alignment Number Formas Table 2 Cell Styles D3 20x Cost 20 C ounded 2 31 Projected Varie Manufacturing Cost Perunt 53 Variable Seling 54 Variable Administrative SS Projected Variable Manufacturing Unit Cost 38 Projected Total Variable Cost Per Unt 20w Cost Fed Owerhead + normal capacity of 2 Fred Sling Fored Administrative Projected Total Food Costs 11 View Insert Format DO Excel File Edit AutoSaver Home Insert Draw Page Layout Formulas Tools Data Window Help CJ7471 (1).xls [Compatibility Mode) Data Review View = % Conditional Formatting Alignment Format as Table Number Cell Styles 19 MS Sans Serif 10 A A Xfx MI For the Period Ending December 31, 20x1 25,000 Tampa Sales Cost of Goods Sold Gross Profit Selling Expenses $45.00 $30.00 $1,125,000.00 750.000.00 $ 375,000.00 Variable (Commission per unit) Administrative Expenses $3.00 $23,000.00 75,000.00 $ 98,000.00 $2.00 $42.000.00 50,000.00 92.000.00 Variable Total Selling and Administrative Expenses Net Profit $ 190,000.00 185.000,00 E92NNREYNTI I See The Ught Projected Balance Sheet As of December 31, 20x1 $ 34,710.00 67,500.00 Current Assets Cash Accounts Receivable Inventory Raw Material Lamp Kits Work in Process Finished Goods Total Current Assets $16.00 8,000.00 500 @ 0 3000 $30.00 90,000.00 $ 200,210.00 Fixed Assets Equipment Accumulated Depreciation Total Fixed Assets Total Assets $ 20,000.00 6.800.00 13.200.00 $213.410.00 $ 54,000.00 $ 54,000.00 Current Liabilities Accounts Payable Total Liabilities Stockholder's Equity Common Stock Retained Earnings Total Stockholder's Equity Total Liabilities and Stockholder's Equity $ 12,000.00 147.410.00 159.410.00 $ 213.410.00 A1 2 3 5 Excel File Edit View Insert Format AutoSaver Home Insert Draw Page Layout Formulas MS Sans Serif 10 BIV 2A Tools Data Window Help CJ7471(1).xls (Compatibility Mode), Data Review View % Conditional Format Allgnment Number Format as Table Cell Styles Paste 15 The projected cost of a lamp is calculated based upon the projected increases or decreases to 16 current costs. The present costs to manufacture one lamp are: 28 36 Lamp Kit: Direct Labor Variable Overhead: Fred Overhead: $16.0000000 per lamp 2.0000000 per lamp (4 lamps/hr.) 2.0000000 per lamp 10.0000000 per lamp (based on normal capacity of 25,000 lamps) 37 39 Cost per lamp $30,0000000 per lamp 48 Expected increases for 20x2 49 When calculating projected increases round to TWO (50.00) decimal places. 84 1. Material Costs are expected to increase by 4.00% 2. Labor Costs are expected to increase by 5.50% 3. Variable Overhead is expected to increase by 3.50% 4. Fixed Overhead is expected to increase to $290.000 5. Fixed Administrative expenses are expected to increase to $58,000 6. Variable selling expenses (measured on a per lamp basis) are expected to increase by 2.00% 84 7. Fixed selling expenses are expected to be $41,000 in 20x2. 8. Variable administrative expenses (measured a per lamp basis) are expected to increase by 3.00% 97 On the following schedule develop the following figures: 98 1- 20x2 Projected Variable Manufacturing Unit Cost of a lamp. 99 100 2- 20x2 Projected Variable Unit Cost per lamp. 108 109 3. 20x2 Projected Fixed Costs. 2 A 6 3