With 2022 fast approaching, BlueBlush Beauty makes its plans for the years production and purchase budgets. The
Question:
- With 2022 fast approaching, BlueBlush Beauty makes its plans for the year’s production and purchase budgets. The sales forecast of the Jojoba Soothing Body Oil (JSB) for January, February, March and April 2022 is 15,600, 18,300, 23,900, and 25,100 jars respectively. The JSB currently sells for $38 a jar. Each jar of the JSB requires 500ml of jojoba oil and 1.25 labour hours to produce. The jojoba oil is sourced from a Mexican supplier at $12.70 per litre. The expected labour rate is $8.75 per hour. Factory overhead cost is allocated based on $0.75 per direct labour dollar. As at 1 January 2022, estimated finished goods inventory is 1,800 jars, while materials inventory is 1,500 litres. Estimated materials inventory as at 31 March 2022 is 2,600 litres. The desired ending material inventory is equal to 10% of the next month’s production needs. The desired ending finished goods inventory is equal to 15% of the next month’s sales in jars.
Selling and administrative expenses are as follows:
• Fixed $18,000 per month
• Variable $0.80 per jar sold Required: (Show all workings / calculations for your answers)
Prepare the following budgets for January, February and March 2022 for the product, Jojoba Soothing Body Oil. All budgets should include a total column for the 3 months. You must use Excel to present your answers incorporating the necessary formulae to compute the numbers and cell addressing to link the different budgets where appropriate. Submit the soft copy of your budgets (.xlsx file) together with your report.
a) Provide the Excel worksheet showing the following detailed budgets:
1) Sales budget
2) Production budget
3) Materials Purchases budget
4) Cost of Goods Sold budget
5) Budgeted Income Statement
b) Assume top management is not satisfied with the initial budgeted net profit. They have requested that you make new projections with the following changes:
• Increase selling price by $1.99
• Decrease purchase price of materials by $0.20
• Reduce factory overhead cost allocated by $0.15 per direct labour dollar
• Decrease fixed selling & administrative expenses by $2,000 per month
Generate the revised budgets and provide the new Excel worksheet. This worksheet should be in another tab in the same Excel file. Explain briefly which budgets will be affected and provide the new total amounts for each of the budgets affected. Would you recommend the proposed changes?