1. Using Excel, prepare the following budgets for the twelve-month period from January 2018 to December 2018.  The first worksheet should contain your raw data and assumptions and all future worksheets should be linked to this data.  Use a different worksheet for each budget. Show all calculations and use Excel functions where possible.  While you should use examples in your text as a guide, the key to using spreadsheets is that they are structured so that others can use them and follow the flow of information without difficulty.

• Monthly Sales Revenue and Cash Collection Budget
• Production Budget in Units
• Direct Materials and Cash Purchases Budget
• Direct Labour Budget
• Manufacturing Overhead Budget (break into variable and fixed components)
• Monthly Operating Cost Budget
• Ending Inventory budget for Finished Goods
• Cost of Sales Budget
• Budgeted Income Statement for the year ended 31 December 2018
• Monthly Cash Budget

Your budget spreadsheet must be submitted in Excel Format (not as an appendix in your WORD document).

2. Write a report (Maximum 1,500 words) for your manager to present to the upcoming Board meeting. The report should include:

• an overview of the expected results for the 2018 year;
• an analysis of the Contribution Margin for each product and recommendations regarding the products Sun Worship Leisure Wear manufacture and sell;
• recommendations regarding the cash position of the business; and
• an analysis of current market conditions and future predictions that you believe should be considered when the 2019 budget is prepared. You should use broader industry data and trends from sources such as IBISWorld and the Australian Bureau of Statistics to support your recommendations.
• Use visual aids such as tables and graphs to enhance your report presentation.

The report should be submitted as a WORD (not PDF) document.

Introduction

Budgeting may be defined as a financial plan prepared for a future time period. It forecasts the future with the help of historical data and present market situations. With the growth of the business, the process of budgeting gains accuracy as the historical base increases with the growth of the business(Belot, 2017). It is an important tool for managing the business, which not only helps in planning the future course of action but also assists in controlling the operations. The budgets provide a target within which the managers are expected to keep their actual performance(Horngren, 2010). It also helps in cash planning as with the help of budgeting, and the organization come to know whether it has sufficient funds, excess funds or deficient funds. The plans for the arrangement of cash can be accordingly. A sound budgeting system anticipates capital expenditures, revenue expenditure and all monetary receipts(Horngren, 2010). This report provides an overview of various budgets prepared for Sun Worship Leisure Wear for 2018. This company manufactures four products which are bikini, board short, towel and swim bag. The contribution margin for each of the products has also been discussed, and the recommendations have been made for the considerations to be made for 2019 budgeting on the basis of the current market scenario.

Analysis of contribution margin of products

Contribution margin is one of the concepts of cost accounting, which helps to determine the profitability of a product on the basis of its contribution(Thomas, 2017). Contribution represents the difference between the selling price and the variable cost of the product. It does not take into consideration fixed costs as these costs are supposed to be incurred whether production is continued or not(Horngren, 2010).

(Some parts of the solution has been blurred due to privacy protection policy)