Question

Would be helpful if you show in excel with calculations

The following information is available for year 1 for Pepper Products:

13 The following information is available for year 1 for Pepper Products: 58, 189, Bee 10 points $ 478.ee 485, Bee 935, ee 2,851, Bee Besk Sales revenue (320, Bee units) Manufacturing costs Materials Variable cash costs Fixed cash costs Depreciation (fixed) Marketing and administrative costs Marketing (variable, cash) Marketing depreciation Administrative (fixed, cash) Administrative depreciation Total costs Operating profits 1,285, Bee 428,2ee 1,456, Bee 213,Bee $7,972,eee $ 128,00 All depreciation charges are fixed and are expected to remain the same for year 2 Sales volume is expected to fall by 6 percent, but prices are expected to rise by 16 percent. Material costs per unit are expected to Increase by 9 percent. Other unit variable manufacturing costs are expected to decrease by 7 percent per unit. Fixed cash costs are expected to increase by 5 percent. Variable marketing costs will change with unit volume. Administrative cash costs are expected to increase by 2 percent. Inventories are kept at zero. Pepper Products operates on a cash basis. Required: Prepare a budgeted Income statement for year 2. (Do not round Intermediate calculations. Round your final answers to the nearest whole dollar amounts.) S 8.832 240 PEPPER PRODUCTS Budgeted Income Statement For Year 2 Sales revenue Manufacturing costs Materials Other variable costs Fored cash as Depreciation (fixed) 981.750 2,851.000 S 3,832,750 Total manufacturing costs Marketing and administrative costs: Marketing (variable, cash) Administrative (fixed, Cash) Administrative depreciation Total marketing and administrative costs Total costs Operating profil S 0 S 3.832.750

Answer 1

с D 103 104 PP 105 Budgeted Income Statement 106 For Year 2 107 Sales revenue $88,32,240 108 Manufacturing costs 109 Materials $4,89,759 110 Variable cash costs $3,54,925 111 Fixed cash costs $9,81,750 112 Depreciation (fixed) $2,81,000 113 Marketing and administrative costs 114 Marketing (variable, cash) $11,32,700 115 Marketing depreciation $4,28,000 116 Administrative (fixed, cash) $17,47,200 117 Administrative depreciation $2,13,000 118 Total costs $56,28,334 119 Operating profits $32,03,906 120

с D 103 104 PP 105 Budgeted Income Statement 106 For Year 2 107 Sales revenue =8100000*(100%-6%)*1.16 108 Manufacturing costs 109 Materials =478000*1.09*0.94 110 Variable cash costs =406000*(100%-7%)*0.94 111 Fixed cash costs =935000*1.05 112 Depreciation (fixed) 281000 113 Marketing and administrative costs 114 Marketing (variable, cash) =1205000*(100%-6%) 115 Marketing depreciation 428000 116 Administrative (fixed, cash) =1456000*1.2 117 Administrative depreciation 213000 118 Total costs =SUM(D109:D117) 119 Operating profits =D107-1118 120

If you have any doubts, please ask. Please rate my answer.